BookmarkSubscribeRSS Feed

Examples of row-level security in SAS Viya

Started ‎08-02-2019 by
Modified ‎08-02-2019 by
Views 15,442

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.

 

The GELCorp example authorization model with a couple of changes

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,

  1. I preferred to create a new user called Barney, who is in both HR and Sales, and
  2. I modified geladm to be in neither HR, nor Sales, taking it out of both groups.

If you want to 'try this at home' using our workshop image, please make similar changes.

 

Our example users

Now we are ready for our demonstration: Please welcome... to the stage... Helena, Barney, Sophia and geladm!

 

Users.png

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:

  • Helena is in HR
  • Barney is in both HR and Sales
  • Sophia is in Sales
  • geladm is in neither HR nor Sales, but is a member of the SAS Administrators group

Our example data

Here is a simplified representation of the HR_SUMMARY CAS table to which we are going to apply row-level authorization:

 

Table.png

 

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.

 

Our example permissions

Here are the relevant permissions, or CAS access controls, set on our HR_SUMMARY table:

 

Permissions.png

 

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.

 

Our row-level grant filter expressions

The filter expression for HR is   upcase(department) in ('SUB::SAS.IdentityGroups').

 

The filter expression for Sales is   department = 'sales'.

 

Filter-expressions-in-row-level-grants.png

 

How row-level grants work

Helena

 

How-row-level-grants-work-Helena.png

 

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 ReadInfo and Select permissions on this table are neither granted nor denied to Authenticated Users, either here, or on the parent caslib hrdl. In the absence of any relevant access controls, and any relevant grant or deny, a default authentication decision of 'not authorized' is reached for Authenticated Users in general...
  • ...but Helena is also in HR, which inherits a grant of ReadInfo from the parent caslib hrdl. HR also has the directly-applied row-level grant of Select on this table. These two access controls are the only ones which are relevant to Helena for this table, so of course they have precedence and are effective. Helena can see the table, and some rows within it, as determined by the row-level filter expression for 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:

 

Report-for-Helena.png

 

Sophia

How-row-level-grants-work-Sophia.png

 

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:

 

Report-for-Sophia.png

 

Barney

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.

 

How-row-level-grants-work-Barney.png

 

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':

 

Report-for-Barney.png

 

geladm

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:

 

How-row-level-grants-work-geladm.png

 

This means that geladm sees results which are based on all the rows in HR_SUMMARY:

 

Report-for-geladm.png

 

Secure the source data, too

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:

 

CAS-Results-for-Delilah-in-SAS-Studio.png

 

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:

 

CAS-duplicate-path-error.png

 

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:

 

Base-SAS-results-for-Delilah.png

 

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.

 

Identity-based Substitutions

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!

Comments

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!

Version history
Last update:
‎08-02-2019 09:48 AM
Updated by:
Contributors

SAS Innovate 2025: Call for Content

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!

Submit your idea!

Free course: Data Literacy Essentials

Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning  and boost your career prospects.

Get Started

Article Tags