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

Consider the following datasets:

 

Row-level security (conditional grant) has been applied as follows: 'NETID'n = 'SUB::SAS.PersonName'

Table: XWALK

NETID

COLL

SUB_COLL

DEPT

JDOE23

AGRI

AGRI

AREC

KSMITH48

ARTSCI

HUMANITIES

ENGL

JJACK23

AGRI

AGRI

AREC

JJACK23

ARTSCI

HUMANITIES

ENGL

 

Table: ENROLL

ACADEMIC_PERIOD

STUDENT_ID

CLASS_CODE

MAJOR_CODE

COLL

SUB_COLL

DEPT

201640

00000001

102

AD

ARTSCI

HUMANITIES

ENGL

201640

00000001

134

AD

ARTSCI

HUMANITIES

ENGL

201640

00000002

205

DF

AGRI

AGRI

AREC

201640

00000003

309

EG

AGRI

AGRI

AREC

201640

00000003

354

EG

AGRI

AGRI

AREC

201640

00000004

286

HY

ARTSCI

HUMANITIES

ENGL

201640

00000005

408

RE

AGRI

AGRI

AREC

 

Now, we want to design a report that will use the XWALK dataset for the report prompts and use the ENROLL dataset in the body of the report (let’s say in a List Table).

 

Thus, our report looks similar to the following:

 

In the above report, we would set the following:

  • Report Prompts
    • Dropdown1 --> XWALK.COLL (not required)
    • Dropdown2 --> XWALK.SUB_COLL (not required)
    • Dropdown3 --> XWALK.DEPT (not required)
  • Report body
    • ListTable1 --> ENROLL – All Columns

 

The user that is running the report is KSMITH48. This user should be limited to see ONLY the following rows in ENROLL:

ACADEMIC_PERIOD

STUDENT_ID

CLASS_CODE

MAJOR_CODE

COLL

SUB_COLL

DEPT

201640

00000001

102

AD

ARTSCI

HUMANITIES

ENGL

201640

00000001

134

AD

ARTSCI

HUMANITIES

ENGL

201640

00000004

286

HY

ARTSCI

HUMANITIES

ENGL

 

If the user selects a value for each of the report prompts, the correct data is shown. However, if the user clears the values in the report prompts, the user will see ALL of the rows in ENROLL.

 

Obviously, this could be prevented by setting the report prompts to be “required”. However, we do not want to have these set to “required”, as some users will need to see ALL of the data at the same time – for example JJACK23 (this user should be allowed to see ALL data at the same time or limit the data as needed via the report prompts).

 

Another obvious way around this would be to simply do a LEFT JOIN on ENROLL with XWALK, with a conditional grant applied on this table (taking the XWALK table out as a needed dataset for the report). However, this is undesirable, as this would cause the size of our dataset to be increased exponentially. Also, this would be undesirable due to the fact that if a report is needed to show overall summary data, a new dataset would need to be loaded…essentially, the same data minus the join and minus the conditional grant.

 

Essentially, I would like to use my two datasets and have the report body to be secured, showing only the items that are allowed via the row-level security. The catch is that I want to allow the user to clear the dropdowns…and when they do, limit the “body” data to be ONLY the values that are “available” in the dropdown list. What are we missing? How can this be accomplished using the XWALK and ENROLL tables as originally described?

 

Thanks,

Ricky

1 ACCEPTED SOLUTION

Accepted Solutions
AndySmith
SAS Employee

Ricky -

 

The following link has a great synopsis of applying conditional grants using groups assigned to the LASR tables with conditional grant read permissions.  You could also apply at user level if you wish, but groups are often better.  

 

http://support.sas.com/resources/papers/proceedings15/SAS1779-2015.pdf

 

To apply to large numbers of users or groups, see the section about the use of set-metadata-access batch tool that you can incorporate into a data transformation job that ingests your authorization information to generate a set of batch script commands.

 

If you apply the grants with these approaches, the ability to read rows is independent of any actions performed on the underlying data, including report parameters.  We have implemented this solution with great success with other SAS education customers.

Andy Smith
SAS Education Practice

View solution in original post

1 REPLY 1
AndySmith
SAS Employee

Ricky -

 

The following link has a great synopsis of applying conditional grants using groups assigned to the LASR tables with conditional grant read permissions.  You could also apply at user level if you wish, but groups are often better.  

 

http://support.sas.com/resources/papers/proceedings15/SAS1779-2015.pdf

 

To apply to large numbers of users or groups, see the section about the use of set-metadata-access batch tool that you can incorporate into a data transformation job that ingests your authorization information to generate a set of batch script commands.

 

If you apply the grants with these approaches, the ability to read rows is independent of any actions performed on the underlying data, including report parameters.  We have implemented this solution with great success with other SAS education customers.

Andy Smith
SAS Education Practice

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 1 reply
  • 1820 views
  • 0 likes
  • 2 in conversation