Row level security in VA report - how can I accomplish this without joining data?

[rgreen33]

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

[AndySmith · Accepted solution]

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.