How to hide specific columns for certain users in SAS Enterprise Guide?

[Mahis]

Hello everyone,

I would like to know if there is a way to hide a specific column from a dataset for certain users while keeping the same dataset accessible in SAS Enterprise Guide.

For example, can we prevent a user from viewing a sensitive column (like a salary or ID number) even though they have access to the dataset?

[Ksharp]

You could create a VIEW for that special user to hide the special columns.

For example, if you need to hide the NAME column:

proc sql;
create view class as
select age,weight,height
 from sashelp.class;
quit;

Or data step(could use DROP or KEEP) could also create a view:

libname x 'c:\temp';

data x.class/view=x.class;
 set sashelp.class;
 drop name;
run;

[Kurt_Bremser]

This will not really work. For a view to successfully execute, the user running it has to have read access to the underlying tables, which means they also can directly read those.

I don't know if it is possible to restrict access to specific variables via metadata when libraries are metadata-bound.

[Quentin]

This thread shows column-level permissions being set in SAS 9 metadata with SAS Management Console:

https://communities.sas.com/t5/Administration-and-Deployment/How-do-I-implement-Column-Level-Securit...

[SASKiwi]

@Quentin - Thanks for pointing that one out! It would work for different sets of users too. The only problem is you can bypass metadata permissions via your own LIBNAME statement unless you implement metadata-bound libraries.

[Patrick]

I believe for VA reports row and column level security is implemented but in the moment where a user can access the actual physical data via SAS the only way I know of to implement column level security is via metadata secured libraries - and I'm not a big fan of these. 

Ideally store your data in a database. Most DB's provide much better options for such security implementations via views where the user can execute the view without the need of direct access to the underlying table. 

Potentially (haven't tried, just an idea) such column level security could also get implemented via a data step view where the secured columns are stored in a separate table with this table secured on OS level.

[Kurt_Bremser]

Since we needed to secure our "data at rest" per company policy, we switched to metadata-bound and encrypted libraries, which worked quite well. We did uncover a bug in the SAS metadata software that led to a race condition, it has been fixed in the meantime.

[Patrick]

@Kurt_Bremser 

That makes sense and is certainly a valid use case. However.... afaik there is no equivalent for metadata-bound libraries under SAS Viya and for this reason alone I wouldn't use it anymore for any new implementation.

[yabwon]

Just for fun, though it may be interesting, using encrypt= option.

Prepare encrypted data (both data and users info)

/* PREPARE DATA and USER ID */
libname x "R:\test";

data x.class(encrypt=AES encryptkey="oneUglyLong123!!paswword"); /* set password */
  set sashelp.class;
run;

data x.users_keys(encrypt=AES encryptkey="SOMEOTHERUgly123!!paswword"); /* mask user ID */
  user = 'bart'; hiden = md5(strip(USER));
run;

libname x clear;

LOG:

1    /* PREPARE DATA and USER ID */
2    libname x "R:\test";
NOTE: Libref X was successfully assigned as follows:
      Engine:        V9
      Physical Name: R:\test
3
4    data x.class(encrypt=AES encryptkey=XXXXXXXXXXXXXXXXXXXXXXXXXX); /* set password */

5      set sashelp.class;
6    run;

NOTE: If you lose or forget the ENCRYPTKEY, there will be no way to open the file and recover the data.
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set X.CLASS has 19 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds


7
8    data x.users_keys(encrypt=AES encryptkey=XXXXXXXXXXXXXXXXXXXXXXXXXX); /* mask user ID */

9      user = 'bart'; hiden = md5(strip(USER));
10   run;

NOTE: If you lose or forget the ENCRYPTKEY, there will be no way to open the file and recover the data.
NOTE: The data set X.USERS_KEYS has 1 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds

Create view and see credentials are properly obscured:

/* Create a view */
proc sql;
create view work.x as
(
select case when 
              md5(strip(USER)) in (select u.hiden from test.users_keys(encryptkey="SOMEOTHERUgly123!!paswword") as u) /* use masked ID */
            then name 
            else "***" end as name
      ,age
      ,sex
      ,height
      ,weight
from test.class(encryptkey="oneUglyLong123!!paswword") /* use passworrd */
)
using libname test 'R:\test' /* use libname */
;
quit;

/* check how credentials are obscured */
proc sql;
  describe view work.x;
quit;

LOG:

1    /* Create a view */
2    proc sql;
3    create view work.x as
4    (
5    select case when
6                  md5(strip(USER)) in (select u.hiden from
6  ! test.users_keys(encryptkey=XXXXXXXXXXXXXXXXXXXXXXXXXX) as u) /* use masked ID */

7                then name
8                else "***" end as name
9          ,age
10         ,sex
11         ,height
12         ,weight
13   from test.class(encryptkey=XXXXXXXXXXXXXXXXXXXXXXXXXX) /* use passworrd */

14   )
15   using libname test 'R:\test' /* use libname */
16   ;
NOTE: SQL view WORK.X has been defined.
17   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.03 seconds
      cpu time            0.04 seconds


18
19   /* check how credentials are obscured */
20   proc sql;
21     describe view work.x;
NOTE: SQL view WORK.X is defined as:

        select case
                 when MD5(STRIP(USER)) in
               ( select u.hiden
                   from TEST.users_keys(encryptkey=XXXXXXXXXXXXXXXXXXXXXXXXXX) u
               ) then name
                 else '***'
                 end as name, age, sex, height, weight
          from TEST.class(encryptkey=XXXXXXXXXXXXXXXXXXXXXXXXXX)
         using libname test 'R:\test';

22   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.03 seconds
      cpu time            0.03 seconds

Use the view:

/* test */
proc print data=work.x; 
run;

Bart

[Mahis]

Thank you all for the detailed suggestions. I will try the different approaches to see which one works best for our use case.