BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
harrison_david
Calcite | Level 5

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
BrunoMueller
SAS Super FREQ

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;

View solution in original post

1 REPLY 1
BrunoMueller
SAS Super FREQ

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;