Today I would like to illustrate row-level authorization (or if you prefer, row-level security) in SAS Viya, with some examples. We'll use the fictional GELCorp organization from some of the SAS Global Enablement and Learning (GEL) team's Administration workshops, with a couple of small modifications. Our example uses two row-level grants for two different groups on the Select permission for the same CAS table. We'll see how they affect four users, variously members of one group, the other, both, and neither.
If you would rather watch and listen than read, my YouTube video, How to Apply Row-Level Security in SAS Viya, on the SAS Technical Insights & Expertise Series channel uses the same examples.
Many of our workshop examples feature two business groups, HR and Sales. We'll use those same two groups for our row-level grants.
We will therefore choose to use the fictional users Helena in HR and Sophia in Sales, but any members of HR and Sales would work equally well. For this illustration I wanted two more users: one in both HR and Sales, and one in neither.
If you know our GELCorp set-up from those workshops, you may remember that the administrators geladm and sasadm are both in both HR and Sales. That suited the workshops well, but for these examples,
If you want to 'try this at home' using our workshop image, please make similar changes.
Now we are ready for our demonstration: Please welcome... to the stage... Helena, Barney, Sophia and geladm!
Select any image to see a larger version.
Mobile users: To view the images, select the "Full" version at the bottom of the page.
As the diagram shows:
Here is a simplified representation of the HR_SUMMARY CAS table to which we are going to apply row-level authorization:
It is not a very big table in IT terms - the CSV source file is 539 KB on disk, but with 10 columns and 14,999 rows, it's more than big enough for this example. I've not shown details of the other rows or columns in the picture because they're not very important to this example. The only thing I'd add is that there isn't just one row for each combination of department and salary_level, as the table might imply: there are hundreds of rows for each combination, and thousands more rows for departments which are neither hr nor sales.
Here are the relevant permissions, or CAS access controls, set on our HR_SUMMARY table:
Authenticated users can neither see the table (ReadInfo is not authorized), nor the data in it (Select is not authorized).
HR and Sales can both see the table (ReadInfo is authorized), and each has a different row-level grant of the Select permission. We'll look at the detail of these in a moment.
The only permission which may raise an eyebrow is the grant of Select to SAS Administrators, which HR_SUMMARY inherits from its parent caslib hrdl.
In real customer projects, where data is real and can be sensitive or personal, it's a good idea to separate the responsibilities of SAS Administrators from those of users who work with data. You should consider not granting your SAS Administrators access to data. (The CAS Superuser role does not have access to see data by default). Instead, design and implement a separate data administrator role, perhaps one for the whole organization or one per line of business, to look after your data. In our workshop environment we do have a Data Administrators group (Delilah and Douglas are members). But we decided to leave SAS Administrators with full control of data in the hrdl (and salesdl) caslibs. That's something I would reconsider when we next review the GELCorp security model design.
The filter expression for HR is upcase(department) in ('SUB::SAS.IdentityGroups').
The filter expression for Sales is department = 'sales'.
When Helena accesses data in the HR_SUMMARY table, the CAS authorization system checks her group memberships. The only relevant ones are HR and Authenticated Users. (Splitting hairs a little, I would argue that Authenticated Users is a principal type, not a group, but for our purposes here it behaves much the same way as a group). CAS also looks at the access controls on the table.
It finds that the relevant effective permissions which apply to Helena are for Authenticated Users and HR:
The row-level filter expression for HR says that members of group ID='HR' should only be given access to rows where the uppercased value in the department column of the table is found in a comma-separated list of the current user's group memberships (i.e. in the string resulting from the SQL-like substitution value SUB::SAS.IdentityGroups). For Helena, the only relevant group in her list of group memberships is 'HR'; there are no rows in the table where the value in the department column matches any of the other groups Helena belongs to. So, she only sees the rows for 'department=hr' in HR_SUMMARY.
Here is an example report as seen by Helena:
Sophia's experience is similar to Helena's. When she accesses data in the HR_SUMMARY table, CAS notices her relevant group memberships, Sales and (split hairs aside) Authenticated Users. As before, it finds that the effective permissions which are relevant to Sophia are those for Authenticated Users and Sales, and again, only the ones from Sales have are important because there are not really any access controls for Authenticated Users, just an absence of them.
Sophia can see the table, and can select some rows in the table as determined by the filter expression for Sales which says that they should only have access to rows where department = 'sales'. Note that no string substitution is required in this filter expression. This is handy when the ID of the defining group for this row-level grant ('sales') is not the same as the values in the column you wish to filter on. In this case, there is a value of 'sales', so they are the same. But we could easily show members of the 'Managers' group data where department = 'management' despite the slight spelling difference, or let them see rows for several departments, by modifying the filter expression as desired.
Here is the same report, as seen by Sophia:
Here is where things start to get a bit more interesting.
Barney is in both HR and Sales, so both row-level grants of the Select permission apply to him. As explained in SAS Viya 3.4 Administration > Security > Authorization > CAS Authorization > Concepts > Multiple Fil..., with some caveats, 'If there is an identity precedence tie (the user is a member of multiple groups, each of which has a filter), the user can access any row that meets any of the filters.'. Put another way, a user to whom multiple row-level grants applies sees the union of rows granted by all of the filters.
Because Barney is in both HR and Sales, both row-level filter expressions apply to him when he tries to access data in HR_SUMMARY, and he sees rows for both 'hr' and 'sales':
Our last example user, geladm, is in neither HR nor Sales (at least, not since I took this user out of both groups while preparing these examples). Neither of the row-level grants have any effect on geladm, and the only relevant access controls are grants of ReadInfo and Select which SAS Administrators inherit on this table from the parent caslib, hrdl:
This means that geladm sees results which are based on all the rows in HR_SUMMARY:
It's good that administrators or data managers can restrict the rows in a CAS table that users can see, using row-level grants. And once a global caslib which points to a path on the filesystem is defined, the same CAS server (e.g. cas-shared-default) will not allow you to create another CAS library which uses the same filesystem path.
Because our example user Helena does not have host account access to the Viya server hosting SAS Studio and the SAS Compute Service in our collection, nor on the CAS servers, we switch to another user for this next step. Delilah is also a member of HR, and has an OS account which has HR as one of her secondary groups:
[root@intviya02 data]# id Delilah
uid=4002(Delilah) gid=2003(sasusers) groups=2003(sasusers),3007(powerusers),3001(HR)
[root@intviya02 data]#
The problem is that the source CSV file (hr_summary.csv) for the CAS table (hrdl.hr_summary) to which we have applied row-level grants in CAS, is in a filesystem directory which is accessible to members of the HR group, and the hr_summary.csv file is readable to members of the group HR:
[root@intviya02 data]# ls -al
total 1244
drwxrws---. 2 sas HR 86 Mar 22 10:23 .
drwxrws---. 7 sas HR 72 Sep 6 2017 ..
-rwxrwx---. 1 sasadm HR 589824 Sep 8 2017 hrdata.sas7bdat
-rwxrwx---. 1 sasadm HR 551791 Oct 25 2017 hr_summary.csv
-rwxrwx---. 1 sasadm HR 131072 Sep 7 2017 performance_lookup.sas7bdat
[root@intviya02 data]#
First, let's show that Delilah is subject to the same row-level security as anyone else in the HR group, when she tries to access the HR_SUMMARY table in CAS. Also, she cannot just declare a new caslib pointing to the same path in this, the only CAS server in our deployment:
/* Delilah is also a member of HR, and is subject to the same row-level grants as Helena */ /* Delilah also only sees results for HR in this crosstab */ cas mySession; proc cas; session mySession; simple.crossTab / row="department" col="salary_level" aggregator="N" table={caslib="hrdl",name="hr_summary"}; run; quit; /* A CAS server such as cas-shared-default will not allow more than one caslib pointing to the same path */ caslib nope datasource=(srctype="path") path="/gelcontent/gelcorp/hr/data/" sessref=mySession subdirs; /* However, if Delilah tried to use another CAS server, she might be successful in declaring a new caslib pointing to the same filesyste path, but without the same row-level access controls */ cas mySession terminate;
The first part of that code works, and only shows Delilah rows for HR:
And, as expected, we get errors for the second part of that code fragment, because CAS will not let Delilah define a new caslib, even a session-level caslib, pointing to the same path as the existing hrdl caslib from the same CAS server:
But this is not sufficient. We must also protect the source data using filesystem permissions.
If read access to the source data is not suitably secured on the filesystem, a user with host account access like Delilah can simply use base SAS code in e.g. SAS Studio, and can access the whole table, without the row-level security. If you allow this, it rather negates the value of the row-level grant in CAS!
Here is an example of some SAS code which demonstrates this alternative route to access the data using an INFILE statement, along with its successful results.
/* Time for an evil laugh... */ data mwahaha; infile "/gelcontent/gelcorp/hr/data/hr_summary.csv" firstobs=2 dlm=','; length satisfaction_level last_evaluation number_project average_montly_hours time_spend_company Work_accident left promotion_last_5years 8 department salary_level $16 ; input satisfaction_level last_evaluation number_project average_montly_hours time_spend_company Work_accident left promotion_last_5years department salary_level ; *list; run; /* Show that we managed to read rows for all departments from hr_summary.csv as Delilah, a member of HR */ proc summary data=mwahaha nway; class department salary_level; output out=counts; run; proc print; var department salary_level _freq_; run;
Here are the results, showing Delilah bypassed the CAS authorization system entirely, and can see data for all departments in hr_summary.csv:
Running ordinary SAS code is clearly not a 'back door' or a 'hack' of some obscure vulnerability. It is a perfectly normal 'front door' which, for the purposes of illustrating this alternative way to get to the source data, we have simply failed to lock. My point is simply that securing your data in CAS alone is not enough.
Members of the HR group who have host accounts can get to the source file, and read it from a SAS session, bypassing CAS completely. (Having read the table in a SAS session, they would be free to copy the data anywhere they have write access).
The solution is straightforward and quite obvious. When applying row-level security to a table in CAS, a SAS or data administrator (preferably a group they belong to, which is part of their host account at the operating system level) who are allowed to see all of the data in the table should load the table into memory in CAS.
The source data should be secured on the filesystem, or in the source database, in such a way that users who are not members of that SAS or data administrator group have no access to the source data.
Row level-grants should only ever be used to grant a principal (a group or a user) access to selected rows in a CAS table which that principal cannot access, in CAS or in any other way.
One final thought. Over several years of working with authorization model design, and occasionally touching on row-level access in a peripheral way to my main project work, I have seen the same two examples of identity-based substitutions over and over again - always something like these two:
empID='SUB::SAS.Userid'
and
FacilityRegion In ('SUB::SAS.IdentityGroups')
The overall filter expression syntax is SQL-like, and the documentation shows lots of other comparitors and operators you can use to build more complex expressions than these. (Just because you can, doesn't mean you should though - I'm a big fan of keeping it simple!) However, I haven't seen the 'SUB::variable' syntax [edited 25/03/19] anywhere outside of SAS 9 and CAS, where it has appeared in OLAP MDX examples, BI filters, and I think it is supported in LASR too. If you know where that form of syntax comes from, please add a comment below or email me directly.
To learn more about row-level access on CAS, see the documentation, which is well written, well organised and thorough.
See you next time!
Hi Team
Suppose if we have 10 different different department like hr finance commercial etc.
and i want to give row level security to a person who can see some kpi of each individual department.
Is it Possible to login those person to his credential and see some kpi of each department?
Hi uttam631,
Yes, that's possible. To make it easier to understand, let's suppose the 10 different departments of a fictional company are named DeptA, DeptB, DeptC ... through to DeptJ. The company has a group in their LDAP provider for each department.
Next, let's imagine we have 11 users in the system - a sasadmin user who is in SAS Administrators, and can see everything and manage permissions etc., and 10 normal users named Ahmed (who is in DeptA), Barbara (who is in DeptB), Clare (who is in DeptC), ... through to James (who is in DeptJ). (Our fictional company is very particular about who can work in each department!)
Next, let's suppose the company has a KPIs table, where each row contains a single KPI for a single department. There are three columns in our kpis table: Department, KPI and Value. Every department has one row per KPI.
We grant ReadInfo on the table to Authenticated Users (i.e. everybody). Then we apply a row-level filter on the Select permission for the table, also for Authenticated Users (we only need one row-level filter to do this), granting Select on the table's rows with filter expression of "department in ('SUB::SAS.IdentityGroups')", without the double-quotes. The effect of this rule is that each user can see each row only if they are a member of the corresponding DeptA..DeptJ group. So far, so good.
Now, there are two ways to answer your question: "Is it Possible to login those person to his credential and see some kpi of each department?"
One way to achieve this is to create a user, called Uttam, and make him a member of ALL the departments - DeptA..DeptJ. Then he will see all the KPIs.
Another way to achieve this is to create a user, called Uttam, who is in a new group called Universal, and also add a new access control to the KPIs table, which grants members of the Universal group full Select permission on the table, with no filter. Then Uttam will see all the rows in the table, irrespective of whether he is also a member of any of the other departments or not. The rows a user can see are the UNION of rows that any of the access controls on the table allow them to see.
You can of course create access controls for individual users too - it works, but it's more effort for you to maintain at scale, so I would recommend you make your life easier and only create access controls that apply to groups/custom groups.
Does that answer your question?
Kind regards,
David
@DavidStern Hi David, thanks for the article.
What happens with row-level security rules when we restart CAS server, for eg. when something bad happened and we need to restart the server.
Is there any way to restore row-level rules after server restart?
Hi @idziemianczyk , sorry for missing this message. Like all CAS access controls for caslibs or tables, row-level filters are stored in the CAS permstore directory. When you restart the CAS server, the access controls for the table, including row-level access controls, should still be there and you should not have to do anything to restore those access controls.
For more about this, search for 'permstore' in e.g.
If you are seeing the row-level filters or other access controls for a CAS table disappear, perhaps this section of the doc might be helpful to understand why: SAS Help Center: CAS Authorization: Concepts > Application and Persistence > Persistence of Access C...?
Kind regards,
David
Hi @DavidStern
Indeed, the last link is the right place to check. We tested multiple scenarios and found a lot of strange behavior.
When we save cas table into SASHDAT file, there are no UAM set on the file. But from the opposite side, it works.
Scenario 1. Create CAS table -> Assign custom UAM -> save table to SASHDAT -> restart server(or delete CAS table) -> upload the data from SASHDAT => No custom UAM in restored CAS table
Scenario 2. Create CAS table -> save to SASHDAT -> Assign custom UAM to SASHDAT -> upload the CAS table (UAM is there) -> restart server(or delete CAS table) -> upload the data from SASHDAT => Custom UAM in restored CAS table
Scenario3. When we need to copy SASHDAT from server A to server B and reuse all the UAM from server A. => No UAM in copied with SASHDAT file. No UAM after uploading from SASHDAT to CAS. Scenario 2 needs to be repeated in server B
Do you have any ideas why it happens? We have implemented a workaround to use Scenario 2 in both server A and server B, but it's not a clean solution. Are there any other ways to solve such a problem?
Kind regards,
Ivan
Hi Ivan,
Thanks for writing this article and posting the video. I found them both incredibly helpful. Is it possible to use row-level auth in combination with report distribution so that the report from your example could be run by Uttam (who can see all the data) and automatically distributed to Sophia, Helana, and Barney? Essentially Uttam is "bursting" the report out to the other stakeholders, who can only see their versions of the report and the data to which they are authorized.
Thanks,
Jeff
@jeffhunta - Report Distribution in SAS Visual Analytics will honor row level security. This only really matters if you choose to include the PDF with the report distribution, and the way it works is that when it comes time to run the scheduled distribution, it will generate the PDF as each intended recipient. If you choose not to include the PDF, then these users will just get an email with a link to the live report, which will force them to authenticate anyway to see it.
Hopefully that helps!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning and boost your career prospects.