Implementing Column Level Security Today
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:
- 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 |
- 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
- 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’);
- 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.