Skip to main content

One aspect of multitenancy is the ability to share data across tenants, but still maintain some data silos. A multitenant application may need to share a file across tenants, but maintain columnar restrictions based upon the user’s rights and permissions.

In this essay, we show how to use LDAP to control access to some columns for a group or user, functionally providing each tenant a customized vertical slice of the data. This requires File Scope security be enabled in the ESP configuration.

The Problem:

Let’s assume we originally have this data file:

IDNumber

FirstName

LastName

Disease

Height

Weight

Cost

SpecialistCoPay

Deductable

MaxOutOfPocket

Now, what if this data file needed to be used by two groups, each with different column level permissions?

The first three columns (Common) need to be visible to both groups. The next three (Medical only) should only be seen by medical personnel. The last four should only be seen by insurance carriers. 

Common

Medical only

Insurance only

IDNumber

FirstName

LastName

Disease

Height

Weight

Cost

SpecialistCoPay

Deductable

MaxOutOfPocket

How can we restrict non-medical personnel from seeing medical information and how can we allow only insurance carriers see insurance information? 

One solution:

  1. We can split the data into three files and create a 1-to-1 relationship between those files by adding IDNumber to each of the new files.

File A

Common

IDNumber

FirstName

LastName

File B

Medical only

IDNumber

Disease

Height

Weight

File C

Insurance only

IDNumber

Cost

SpecialistCoPay

Deductable

MaxOutOfPocket

  1. Now in LDAP:
  • the Doctors group has permissions to see files A and B
  • the Insurance group has permissions to see files A and C

In the code sample:

EmilyK has FULL rights to tutorial::insurance scope and has been denied access to the tutorial::medical file scope

EmilyD has FULL rights to tutorial::medical scope and has been denied access to the tutorial::insurance file scope

EmilyA has FULL rights to both the tutorial::insurance and the tutorial::medical file scopes

EmilyNone does not have rights to either the tutorial::insurance or the tutorial::medical file scopes

  1. Use a SOAPCALL to ECL Watch’s WsAccess/FilePermission method to check the current user’s permissions. 

//Check Permissions
  IMPORT STD;
  STRING ECLWatchIP :=
              'http://AdminUser:Password@127.0.0.1:8010/Ws_Access/'; 
                                                            // not actual credentials
  OutRec := RECORD
    STRING UserPermission {xpath('UserPermission')}; //portion of result
  END;

  InRecMed := RECORD
   STRING FileName{xpath('FileName')} := 'tutorial::medical::tutorialperson'; 
   STRING UserName{xpath('UserName')} := STD.System.Job.User(); 
   STRING GroupName{xpath('GroupName')} :=''; 
  END;
  InRecIns := RECORD
   STRING FileName{xpath('FileName')} := 'tutorial::insurance::tutorialperson'; 
   STRING UserName{xpath('UserName')} := STD.System.Job.User();
   STRING GroupName{xpath('GroupName')} :=''; 
  END; 
Result1 := SOAPCALL( ECLWatchIP, 
                      'FilePermission', 
                      InRecMed, 
                    OutRec,
                    XPATH('FilePermissionResponse'));
Result2 := SOAPCALL( ECLWatchIP, 
                      'FilePermission', 
                      InRecIns, 
                    OutRec,
                    XPATH('FilePermissionResponse'));

//Possible values for UserPermission:
// Full Access Permission, Write Access Permission, Read Access Permission,
// Access Permission, None Access Permission, or Permission Unknown 
// For this example, we are only allowing access if user has FULL permission

  Rights := MAP(
              STD.STR.CONTAINS(result1.UserPermission,'Full',true)
              AND 
              STD.STR.CONTAINS(result2.UserPermission,'Full',true) => 'A',
              STD.STR.CONTAINS(result1.UserPermission,'Full',true) => 'D',
              STD.STR.CONTAINS(result2.UserPermission,'Full',true) => 'I',
              'X'); 

  1. Then based upon the permission found, we would dynamically select the output as shown below :

     SHARED Common := DATASET([{1,'Fred'},{2,'Sam'},{3,'Joe'}],Layout_Common);

     SHARED Layout_Doctor := RECORD(Layout_UID)
           STRING10  Disease;
     END;

     SHARED Doctor := 
                 DATASET([{1,'Mumps'},{2,'Measles'},{3,'ChickenPox'}],Layout_Doctor);

     SHARED Layout_Insurance := RECORD
           UNSIGNED2 UID;
           STRING10  Cost;
     END;
     SHARED Insurance := DATASET([{1,'1000'},{2,'2000'},{3,'3000'}],Layout_Insurance);

     Layout_Out := RECORD(Layout_UID)
           Layout_Common;
           Layout_Doctor;
           Layout_Insurance;
     END;

  DoctorOut := JOIN(Common,Doctor,
                       LEFT.UID=RIGHT.UID,
                       TRANSFORM(Layout_Out,SELF := LEFT, SELF := RIGHT, SELF := []));
  InsuranceOut := JOIN(Common,Insurance,
                          LEFT.UID=RIGHT.UID,
                          TRANSFORM(Layout_Out,SELF := LEFT, SELF := RIGHT, SELF := []));
  CommonOut := PROJECT(Common,TRANSFORM(Layout_Out,SELF := LEFT, SELF := []));
                                  
  EXPORT File := CASE(Rights,'D' => DoctorOut,'I' => InsuranceOut,'A' => AllOut,CommonOut);
END;

OUTPUT(File_Medical(Rights).File);

Results:

Running the codes sample three times using Users with different permissions:

EmilyK has FULL rights to tutorial::insurance scope and has been denied access to the tutorial::medical file scope

EmilyD has FULL rights to tutorial::medical scope and has been denied access to the tutorial::insurance file scope

EmilyA has FULL rights to both the tutorial::insurance and the tutorial::medical file scopes

EmilyNone does not have rights to either the tutorial::insurance or the tutorial::medical file scopes

Conclusion

This method uses existing functionality to achieve column level security, but it is not perfect. For example, this method doesn’t address the typical paradigm that a user without access should not even know of its existence. There is also a possibility that, depending on context, the extra cost of JOINs could be significant.  

Also, to implement in Roxie requires an initial permission check and caching of the user's privileges to ensure rapid throughput.

This is a simple quick-win solution using pure ECL. The technique can be extrapolated even to the point of having one file scope for each column, if needed. 

Acknowledgements
Thanks to Richard Taylor, Rodrigo Pastrana, Russ Whitehead, and Anthony Fishbeck for their help in crafting the code.