Row Level Security Filter

[harrison_david]

I am trying to create a row-level security filter on a table that will look at who is logged in and find them in one of three columns (i.e. Director, Principal, Teacher). I can get a filter to work when looking at just one column but can't get it to work when looking across multiple columns.

Here's what works: upcase(Teacher)=upcase('SUB::SAS.Userid'). This will return only rows for the teacher who is logged in. 

Here's what doesn't work:

1. upcase('Director', 'Principal', 'Teacher') IN upcase('SUB::SAS.Userid')

2. upcase(Director, Principal, Teacher) IN upcase('SUB::SAS.Userid')

3. upcase(Director)=upcase('SUB::SAS.Userid') OR upcase(Principal)=upcase('SUB::SAS.Userid') OR upcase(Teacher)=upcase('SUB::SAS.Userid')

I need the filter to look at three columns for the SAS Userid of the person logged in. BTW, the SAS Userid will only be in one of the three columns. The same userid will not be found in multiple columns. 

I'm sure my issue has to do with my syntax. Any help would be greatly appreciated.

I am working in SAS Viya V.03.04 and in Environment Manager 3.3. 

[BrunoMueller · Accepted solution]

The condition "upcase(director) = upcase('SUB::SAS.Userid') or upcase(principal) = upcase('SUB::SAS.Userid') or upcase(teacher) = upcase('SUB::SAS.Userid')" should work.

You can use the following code to see the actual values substituted  for SUB::SAS.Userid

/*
 * using accessControl.isAuthorizedTables
 * it will show the actual values substituted for
 * 'SUB::SAS.Userid'
 * 'SUB::SAS.IdentityGroups'
 */
proc cas;
  action accessControl.isAuthorizedTables /
    caslib="&caslib"
    permission="Select"
    tables={"&casTable."}
  ;
run;
quit;