Sometime ago I was required to implement column level security across SAS datasets. This was to be implemented as masking of specific columns based on metadata security. The results would be presented via Information Maps and ultimately SAS Web Reports and SAS BI Dashboards. This blog will explain how I managed to implement this requirement.
- Record the Metadata groups that will control the access.
- Secure the source dataset using either encryption or password access and register the dataset in Metadata.
- Create a DATA step view which is stored in a permanent library. This view should include:
- code to check Metadata group membership – I used the macro below.
- the credentials to access the source dataset.
- logic to mask or hide the values in the secured columns.
- Register the new view into Metadata.
- Define a new Information Map based on the new data view and save.
- Setup Metadata folder security to secure the source dataset, view and Information Map.
Here’s an example of my view:
DATA VIEWLIB.vw_VIEWNAME / VIEW=VIEWLIB.vw_VIEWNAME;
/* check users membership in each security group */
/* group one = Unresticted, group two = inconfidence*/
%secureColumnByGroup(group one|group two);
/* read source table */
/* if user is restricted (maskdatalvl = 0) then mask all restricted columns */
/* if the user is unrestricted (maskdatalvl = 1) then show all data */
/* if user is in confidence (maskdatalvl = 2) then mask all restricted columns where the record is Sensitive */
IF (maskdatalvl eq 0) or (maskdatalvl eq 2 and Sensitive eq "Y") THEN DO;
/* Reassign secure columns to mask value */
columntomask1 = "*****************************";
columntomask2 = 9999999999999;
The example outlines the us of more than one security group. In this case Unrestricted users should see all values, InConfidence users would have only values in restricted columns masked if the data row was marked as sensitive, and users in neither group (considered Restricted) would have all values in the restricted columns masked.
And here is the macro that controls the masking:
%macro secureColumnByGroup(groupList /* pipe "|" seperated list of security groups from most to least secure */);
drop maskdatalvl username memberuri memberName n i numMembers rc groupCnt groupName;
retain maskdatalvl 0 username; /* initially set all users as restricted */
/* determine group membership - only execute once at the start of the data step */
if _n_=1 then do;
/* retrieve user ID using the view */
/* If non-pooled workspace server sysuserid will return the id of the user using the view. Otherwise, for pooled servers, it will return SASSRV.*/
if username eq 'SASSRV' then username=scan(getoption('metauser'),1,'@');
/* For pooled servers, parse the value of the system option metauser to retrieve the user ID. */
length memberuri $1000 memberName groupName $200;
/* Count how many groups to process */
groupCnt = countc("&groupList.", '|') + 1;
/* For each group in the list check users membership */
do i=1 to groupCnt;
/* get the ith group name from the list */
groupName = scan("&groupList.",i,'|');
/* all return strings must be initialized */
/* Use OMI functions to retrieve metadata about users and groups */
/* get the number of user IDs which are members of the user group */
numMembers=metadata_getnasn("omsobj:IdentityGroup?@Name='" !! groupName !! "'","MemberIdentities",1,memberuri);
/* Retrieve each member of groupName and see if it matches the user ID using the view.
If it matches then set the flag maskdata to i and jump out of this loop. */
do n=1 to numMembers;
numMembers=metadata_getnasn("omsobj:IdentityGroup?@Name='" !! groupName !! "'","MemberIdentities",n,memberuri);
if strip(upcase(username)) eq memberName then do;
/* flag them as a member of the group */
/* jump out of do n loop */
/* jump out of do i loop if membership found */
if maskdatalvl ne 0 then leave;
The secureColumnByGroup macro takes one parameter which can be a single group name or a list of group names separated by the pipe “|” character. The list should be in order from most secure to the least secure and will return the relative position of the group that the user was first found to be a member of. Your view can then use this relative value to decide what to secure.
Some recommendations and considerations:
- Store the original dataset in the same location as the views. The datasets and views only need to be accessible to the sassrv user if you are using Information Maps.
- Consider encrypting the original datasets, SAS encoding the encryption password and embedding the encoded password in the view.
- If you ever modify either secureColumnByGroup macros you’ll need to re-generate all the views that reference those macros. Consider storing all view definitions in one place for ease of re-creation.
- The secureColumnByGroup macro assumes that the groups in the list parameter are provided in order of relevance. So if a user is a member of group 2 and group 5 in a list of groups then the return value for maskdatalvl will be 2 rather than 5.