BI Row-Level Permissions

Example: Using BI Row-Level Permissions

This example demonstrates how a company could use row-level permissions to manage access to employee data. The example includes these assumptions:

The target tables are registered in the metadata repository.
Except where otherwise noted, users have Read permission for the information maps that they are using.
The data model is a star schema that contains employee and customer data. The security associations table includes both direct and indirect reporting relationships.
The company has set up a high-security configuration of SAS Web Report Studio as specified in Preliminary Tasks.

In this example, the business requirement is to enable managers to see salary information for their employees. One way to meet this requirement is to use the SAS.PersonName property. The following figure depicts this process for a requesting user who is a high-level manager in the organization.

Salary Example: Data Model

[Salary Example: Data Model]

Each requesting user's PersonName is used to filter the security associations table. This yields a subset that includes only those rows with employees who report (directly or indirectly) to the requesting user. That subset is inner joined to the target table to limit retrieval of salary information.

Implementation and Testing

To implement the filtering for this example:

Create an information map that includes the necessary data and relationships.
1. In SAS Information Map Studio, open a new information map.
2. Insert the target table and the security associations table as data sources. In this example, the target table (ORGANIZATION_DIM) contains salary data, and the security associations table (SECURITY_ASSOC) contains a representation of the company's reporting relationships.
3. On the Design tab, add the data items that you need from the ORGANIZATION_DIM table (insert the SALARY, EMPLOYEE_ID, and EMPLOYEE_NAME columns).
  
  Note: It is a good practice to not create any data items from the SECURITY_ASSOC table.
4. On the Relationships tab, join the two tables on EMPLOYEE_ID.
5. Save the new information map to an appropriate folder.
6. Make SECURITY_ASSOC a required table:
  1. Select Edit Properties Information Map.
  2. In the Information Map Properties dialog box, select the Required Tables tab.
  3. In the Available tables list, select the SECURITY_ASSOC table.
  4. Use the arrow button to move the table to the Required tables list, and then click OK.
Create a filter that subsets data by comparing each user's SAS.PersonName value to the PARENT_EMPLOYEE_NAME values in the security associations table.
1. Select Insert New Filter to open the New Filter dialog box.
2. Enter a name such as byPersonName for the filter, and then click Edit Data Item.
3. In the Edit Expression dialog box, select Character from the Type drop-down list. On the Data Sources tab, navigate to Physical Data SECURITY_ASSOC PARENT_EMPLOYEE_NAME, and then click Add to Expression.
4. Click Validate Expression, and then click OK twice.
5. In the New Filter dialog box, from the Enter value(s) drop-down list, select Derive identity values (for row-level permissions). A table of identity-driven properties becomes available. See Identity-Driven Properties.
  
  Note: Make sure that the value in the Condition drop-down list is Is equal to.
6. In the table of properties, select the SAS.PersonName row.
7. Click OK. The byPersonName filter is now available for use in the information map.
Assign the filter as a general prefilter:
1. Select Edit Properties Information Map.
2. In the Information Map Properties dialog box, select the General Prefilters tab.
3. In the Selected filters box, select the SECURITY_ASSOCIATIONS table.
4. In the Available filters box, select the byPersonName filter.
5. Click the right arrow button to assign the byPersonName filter to the SECURITY_ASSOC table, and then click OK.
Save the information map.

Any administrators who have physical access to the data can test by logging on to SAS Information Map Studio and running test queries. To verify that the filter is working as expected, log on using different accounts. For example:

For a user who is not included in the security associations table, no salaries should be retrieved.
For the president of the company, all salaries should be retrieved. Note that by default only 100 rows of data are returned when you test an information map.
For a mid-level manager, a subset of salaries should be retrieved.

To run a test query from within SAS Information Map Studio:

Select Tools Run a Test Query from the main menu.
In the Test the Information Map dialog box, use the arrow button to add the Salary and Employee Name items to the Selected items box.
Click Run Test and then examine the data in the Results dialog box.
To test using another account, close the information map, and then select File Connection Profile from the main menu.

Note: Because this is a high-security configuration, final verification must be performed from within SAS Web Report Studio. In a different configuration in which you use server-side pooling, your results in SAS Information Map Studio are the same as your results in SAS Web Report Studio. See Choices in Workspace Server Pooling. [cautionend]

Variation 1: Use External Identity Values for Filtering

This variation describes one way to work with target data that contains employee IDs (instead of employee names). Modify the main implementation steps as follows:

Verify that users have the external identity values that you expect. If necessary, you can manually add external identity values to the metadata. See External Identities.
Use the PARENT_EMPLOYEE_ID column instead of the PARENT_EMPLOYEE_NAME column.
Use the SAS.ExternalIdentity property instead of the SAS.PersonName property.

Variation 2: Apply Different Filtering Logic to Different Groups

This variation addresses these additional business requirements:

Four people who work in a Human Resources department must be able to view salary information for all employees. You have created a user-defined group in the metadata repository for these users (the HR group).
Users who do not have individual metadata identities must not be able to see any of the data. These users have the access that has been defined for the PUBLIC group.

This table summarizes the strategy:

Information Map Controls
Access Class (User Group)	Information Map
All rows (Human Resources)	Grant Read, ReadMetadata
No rows (PUBLIC)	Deny¹ Read, ReadMetadata
Some rows (SASUSERS)	Grant Read², ReadMetadata
1 The information map in this example exists only for the purpose of obtaining salary information, so the "No rows" users do not need to be able to see or use this information map. 2 For each member of SASUSERS, this explicit grant is narrowed by the byPersonName filter that you created in the main example. Here, the filter is used as an authorization-based prefilter.

To set these permissions:

Prepare the information map by using either of these methods:
- Create a new information map for this variation by completing steps 1 and 2 in the main example.
- Reuse the information map from the main example by saving that map with a different name and deassigning the filter that was assigned on the General Prefilters tab.
Open the information map and select Tools Authorization to open the Authorization dialog box.
In the Users and Groups list, select PUBLIC. In the Effective Permissions list, add explicit denials for the Read and ReadMetadata permissions.
Click Add. In the Add Users and Groups dialog box, select the HR and SASUSERS groups and then click OK.
In the Authorization dialog box, give SASUSERS explicit grants of the Read and ReadMetadata permissions.
To limit the SASUSERS grant of the Read permission, assign the byPersonName filter to that group.
1. Click Add Condition to open the Row-Level Permission Condition dialog box.
  
  Note: The Add Condition button became available when you added the explicit grant of Read permission.
2. In the Selected filters list, select the SECURITY_ASSOC table.
3. In the Available filters list, select the byPersonName filter and then use the arrow button to move that filter to the Selected filters list.
  
  Note: Unlike a filter that you assign on the General Prefilters tab, this filter applies only to members of the SASUSERS group as evaluated according to the identity hierarchy and access control precedence rules.
4. Click OK to close the Row-Level Permission Condition dialog box.
In the Authorization dialog box, give the HR group explicit grants of the Read and ReadMetadata permissions. Because you want this group to be able to view all salaries, do not constrain Read access by adding a permission condition.
In the Authorization dialog box, click Close. To make your changes take effect, save the information map.

With these access controls in place, retrieval is as follows:

Users who don't have their own SAS identity (PUBLIC-only users) can't see or use the information map.
Users who have their own SAS identity but aren't listed in the security associations table can see the information map, but retrieve no rows.
Users who have their own SAS identity, are listed in the security associations table, and are not members of the HR group get only those rows that contain data for their own direct and indirect reports.
Users who are members of the HR group get all rows.

Top of Page