We’re smarter together. Learn from this collection of community knowledge and add your expertise.

Enforce Row Level Access

by Super User on ‎05-25-2015 03:45 PM - edited on ‎10-05-2015 02:52 PM by Community Manager (1,808 Views)

By Using Metadata-Bound Libraries, and a Comparison with Other Techniques

 

Background

The need for applying row level access to data is old, and increasingly elevating due to that fact we want more people to use the data and reports to good things. But when offering more detailed data, it usually comes with the side effect that we need to consider privacy and other legal issues. We need ways to ensure that information only is available to whom it is authorized.

Historically in SAS, this usually meant that we have to build the access control within our applications. With time, this has been easier with the help of meta data, OLAP cubes and Information Maps. But what about users who should access data directly and in more flexible ways? If this is the case, we can’t hide behind applications, or techniques such as Information Maps.

 

Row Level Access Methods at or Near the Data

So, if we can’t rely on authorization near the user, we need to put authorization closer to the data. Here’s a list of few of the methods that are somewhat easy to use for SAS applications. They are (but not limited to):

  • External data base
  • SAS Scalable Performance Data Server
  • SAS Federation Server
  • SAS Metadata-bound libraries
  • SAS Metadata variable _Metadata_Authenticated_Userid_

Independent of solution, we wish to look for methods that supports filtering based on groups memberships (and in some occasions the user id).

 

External Data Base

In this scenario, we will rely on methods present in the data base. Some having better support for this than others, so it’s hard give a common receipt how this can/will work. As an example SQL Server have no out of the box concept for this. Restricting access involves securing tables/columns, and use of views and Stored Procedures.

 

SAS Scalable Performance Data Server

The Scalable Performance Data Server (SPDS) have had from the start a quite extensive security model, including management of users and groups, Access Control Lists and a security model that can be applied on tables and columns. And for quite some time, even row level filtering. It is enforced by using user/group symbol resolution, which is used in server side defined views.

There are some automatically defined and assigned symbols:

  • @SPDSUSR: the SPD Server user id that is executing the query
  • @SPDSGRP: the group memberships for the SPD Server user that is executing the query

Let me exemplify:

 

proc sql;

    connect to sasspds (dbq='Test' host='zen' serv='5400' user='Lisa' password='abcd1234');

    select *

         from connection to sasspds

               (select *

                                    from class

                                    where sex = "@SPDSGRP");

    disconnect from sasspds;

quit;

 

Lisa is a registered user in SPD Server, and belongs to a group “F”. After automatic symbolic substitution the where clause is transformed to:

 

where sex = "F"

 

This logic can be applied to a view. And of course, you need to secure the underlying table for direct access by the users. Another even “tighter” way is to use it as a where constraint on the table:

 

proc spdo lib=test;

    set acluser;

    constraint add class;

        Where (sex = “@SPDSGRP”);

    constraint describe class;

quit;

 

Follow the link under “References” for details.

This techniques is secure, and quite flexible. I.e. you can combine a separate organization table in a view (think star schema).

If you don’t already have SPD Server licensed, it will of course be an additional cost involved in fees and time learning the product. Also, there is no easy way to batch load/synchronize users and groups. So if you have vast amount of users with individual access patterns, the maintenance can be substantial. For some use cases, you could perhaps use “group accounts” in SPD Server, and hold individual user information within the Metadata Server alone.

 

SAS Federation Server

The SAS Federation Server originated from the Data Flux product line. Federation means that it combines access to multiple data sources in a central point of administration. It has many features, and one is row level access restrictions for specific users and groups.

Another security mechanism allows you to set up rules for masking sensitive data, such as SSN.

Like the SPD Server case, if you don’t have the license yet, you need to do some initial investments. But if are looking for a data virtualization tool anyway, it’s definitely worth evaluating.

 

Metadata-Bound Libraries

As the title implies, using a metadata-bound library will create an unbreakable connection between a physical table and its metadata registration. This means that any authorization defined in metadata will be enforced, even if you aren’t initially logged on to the metadata server. Moving the table by brute force doesn’t help – without its mother metadata server, the table is inaccessible. When binding a library to metadata, each table will get a corresponding “secured library object” created in the metadata under the folder /System/Secured Libraries/

Then, you are ready to apply row level access patterns, or “Providing Fine-Grained Access Using Condition Permissions” as SAS calls it in the documentation. This feature is quite similar to the symbolic substitution technique used in SPD Server. It’s called identity driven properties, and is maintained by the metadata server. When you define them as part of a condition that you set on the secured library object, the properties are evaluated at query time.

The properties are called in the form SUB::SAS.Propertyname

Example of property names are Userid, ExternalIdentity and IdentityGroups. For a complete list, follow the link under “References” bellow. The steps to make this work are:

 

1. Bind the library

Open Management Console, navigate to the /System/Secured Libraries folder.

Right click, and chose “New” - “Secured Library”

Give a name, the same name as the library you wish to bind is not a bad choice.

 

NewSecLib.jpg

For this, you need to define a data set password (PW=), and an encryption key. If you need strong encryption, AES is available. Be sure to keep those in a safe place for future maintenance. These hasn’t to be supplied by the end users at query time.

Also, be aware of that the user that creates the metadata-bound library must have both write metadata rights, and the necessary right to rewrite all physical data in the secured library.

When choosing Finish, all data sets in the specified location will be rewritten using the specified data set password(s) and encrypted if specified. Also, all data sets in the location will also get an individual metadata object under the Secured Library folder.

 

2. Define metadata groups for sub setting

This step is necessary unless you plan to do filtering on a user id level.

Define groups which names have the same value as the column values in your data set you wish to use for sub setting. Be aware that values returned from identity properties when used in Visual Analytics (see below) is returned in upper case, so it would be a best practice to define them as such, for clarity.

Assign user to respective group according to their authorization.

If you have a lot of users and groups, this step can be automated by using User Import Macros, see references at the end.

 

3. Define the condition permission

Find the secured library object for the table you wish to control. Open its properties, chose Authorization. Now, we want to define this condition for every user that can access the table. It could be SASUSERS, or some other group that holds all users authorized to view the data. Select that group, and grant the permission “Select”.

SecTabAuth.jpg

Now, the button “Conditions” is clickable. By doing so, a small dialog box is opened were you can define your subset criteria. Enter without the keyword “where”:

 

Sex IN(“SAS::IdentityGroups”)

SecTabCondition.jpg

The syntax can be a bit confusing. This because if you are a member of multiple groups, SAS will automatically create your IN list for you and enclose each group name within quotes:

 

Sex IN(“SASUSERS”,“F”,“STUDENTS”)

 

4. Access the data

At this point, when a user access this table, the above defined filter will be applied automatically each time the data is accessed. The metadata-binding will prevent any user from bypassing this filter.

Footnote: Identity properties can also be utilized in Visual Analytics and in Information Maps. In Visual Analytics they are defined within the Visual Analytics Administrator application, or in batch mode. When used in Information Maps call them by the corresponding macro variables instead (e.g. &SAS.userid).

 

Metadata variable _Metadata_Authenticated_Userid_

Yet another way to limit access is to use the metadata user id to limit access to data via a view. The metadata user id is available in SAS sessions as the pseudo-variable _Metadata_Authenticated_UserId_. It resolves in the same way as the identity property SAS.Userid described earlier. By using this in a where-clause in the view, you can limit access to data – in multiple tables if your view has join in it.

You need of course secure the underlying data for direct access. So I think this concept is a bit more complicated than and not as flexible as the metadata-bound/identity property one. But on the other hand, it offers possibility to limit access to columns, which is not possible with metadata-bound libraries in the current release.

 

Conclusion

There are a variety of ways to secure data on row level in SAS. If you wish to secure data created for direct access, metadata-bounds libraries is probably the most straight forward solution. Given that a substantial part of SAS sites license metadata server.

A limitation is that this is supported only for the Base SAS engine. Users that have the data in question stored in a SPDE library need to consider to move to a Base library. If you are using a star schema this doesn’t need to cause performance issues, since you can chose only to move the dimension tables that you are using for conditional permission.

For users with their data elsewhere should probably first evaluate techniques available to them in that environment, i.e. the RDBMS of choice.

 

References

SAS Scalable Performance Data Server Table WHERE Constraints

SAS 9.4 Guide to Metadata-Bound Libraries, Second Edition

Fine-Grained Controls for Data

Providing Fine-Grained Access Using Views

SAS(R) 9.4 Intelligence Platform: Security Administration Guide, User Import Macros

Paper SAS1779-2015: Row-Level Security and SAS® Visual Analytics

 

Acknowledgements

My colleague Fredrik Englund for proofreading.

SAS R&D which provided valuable feedback, and for the mediation.

Comments
by Frequent Contributor
on ‎05-27-2015 11:50 AM

Thanks a lot for sharing, I find the article very instructive.

One can also define rowfile-level access at server level using the LOCKDOWN Mode's (whitelist) assigned by Restricted options based on Usernames (Windows + Unix/Linux) or User Primary Group (Unix/Linux) :

https://support.sas.com/documentation/cdl/en/biasag/63854/HTML/default/viewer.htm#n23000intelplatfor...

see page 12 : https://support.sas.com/documentation/installcenter/en/ikfdtnwx6cg/66385/PDF/default/config.pdf

see page 9 : https://support.sas.com/documentation/installcenter/en/ikfdtnunxcg/66380/PDF/default/config.pdf

The where clause  based on User Group you are showing in the particular case of the SPDS server is already available at Foundation level within the Proc SQL (it's called USER Literal) :

https://support.sas.com/documentation/cdl/en/sqlproc/65065/HTML/default/p020urejdmvi7vn1t9avbvazqapu...

[edited 1 time]

by Super User
on ‎05-28-2015 03:14 AM

Didn't know about the SQL user literal. Even if it's not as flexible/secure as metadata-bound libraries and SPD Server symbol resolution, it's a great tip!

Wonder why they call it literal, syntactically it doesn't remind me of d, t, dt and n literals.

by Frequent Contributor
on ‎05-28-2015 06:44 AM

yes, I also found it quite confusing & elusive. As far as I know, 'Literal' in SAS usually defines a Literal expression (quoted expression) however in this context, USER is rather used as a special language  keyword.:smileyconfused:

by Super User
on ‎07-02-2015 09:35 AM

Hi all,

just want to add some real life experience.

By using a Permission Condition, you are in fact enforcing a where-clause, even if you won't see in your programs/logs.

But your other SAS programs will encounter this. So be aware if you for instance have a data step modify, with key=.

Since you can't combine the key= option with where, you will experience an error:

A work-around for this (there may be others) is to us a user that itself, or the groups that it belongs to explicitly don't have a Permission Condition.

What you do is

  1. add this user (or group) to the Authorization for the Secured table.
  2. Mark the Select Effective Permission
  3. Click on Add Condition button
  4. Make sure that this dialog box is empty

Then you will be able update the table (again)

Your turn
Sign In!

Want to write an article? Sign in with your profile.