Previous Page | Next Page

BI Row-Level Permissions

How to Implement BI Row-Level Permissions

The steps for implementing row-level permissions are Preliminary Tasks. Data Modeling. Information Map Tasks, and Verification.

Preliminary Tasks

  1. If you need comprehensive security, set up the high-security configuration of SAS Web Report Studio. This prevents regular users from circumventing row-level filters by accessing the target tables directly (without going through the information map that enforces the filters). In the high-security configuration, SAS Web Report Studio uses a privileged account to fetch the data. Regular users have only mediated access to the target tables.

    In the following environments, you might not need the high-security configuration:

    • prototype environments

    • environments that do not have strict security requirements

    • environments in which a firewall separates untrusted users

    • environments in which untrusted users do not have the tools, knowledge, or operating system privileges to access files and metadata on the server tier

    For instructions for setting up the high-security environment, see Configure a Client-side Pooling Workspace Server to Enforce Row-Level Security in the SAS Intelligence Platform: Application Server Administration Guide.

  2. Make sure that appropriate coarse-grained controls are in place for users.

    Metadata Layer Coarse-Grained Controls
    Access Target Table Information Map
    All rows Grant Read, ReadMetadata Grant Read, ReadMetadata
    No rows Deny Read, ReadMetadata Grant1 Read, ReadMetadata
    Some rows Grant Read, ReadMetadata Grant Read2, ReadMetadata
    1 Set these grants if the "No rows" users access other tables through this information map.

    2 For authorization-based prefilters, this must be an explicit grant.

    Note:   In the high-security configuration, only the restricted puddle account and IT staff who test queries in SAS Information Map Studio have physical access to the target table. If you use server-side pooling, only the server launch credential should have physical access to the target table.  [cautionend]

  3. If the target data is in a third-party database, enable authentication to that server.

    • Make sure that IT staff who test queries against the data from SAS Information Map Studio can authenticate to the DBMS server. For example, you can store individual or group DBMS credentials for those users, or require those users to interactively provide a DBMS user ID and password. See How to Store Passwords for a Third-Party Server.

    • Make sure that regular users can access the DBMS server. For example, you can store individual or shared DBMS credentials for users as you did for the IT staff.

      Note:   In the high-security configuration, you use a different approach so that physical access for regular users is mediated by a privileged service account.  [cautionend]

  4. Plan how you will create the data subsets that will narrow grants of the Read permission for each user. Your choice will be affected by the number and type of access distinctions that you are making, the information that your data already contains, and your plans for enhancing your existing data to support row-level filtering. See Filtering Techniques for BI Row-Level Permissions.

    Note:   In a situation in which multiple filters (multiple distinct permission conditions) apply to a particular user as a result of the user's group memberships, the subset of data that is available to that user is determined by identity precedence. See Precedence for Permission Conditions.  [cautionend]

Data Modeling


Overview and Examples

It is usually necessary to enhance existing data to include information that works with the filters that you want to use. For example, consider a four-person company with a flat organizational structure and a business requirement that each employee sees only his or her own order information. The order information is stored in this table:

Orders Example: Target Table

[Orders Example: Target Table]

Assume that you didn't import users (so you don't have SAS.ExternalIdentity values in the metadata that correspond the EmpID values in the ORDERS table). To avoid setting up a different filter for each user, you decide to use the SAS.PersonName identity-driven property. Create a table that maps each user's PersonName (from the Name field on the General tab of the user's definition) to the user's employee ID. The following figure depicts how that table is used to prescreen the data for each user.

Orders Example: Data Model

[Orders Example: Data Model]

Another simple example is to subset employee performance information based on each manager's external identity value, as depicted in the following figure.

Performance Example: Data Model

[Performance Example: Data Model]

Another example is to subset sales information by each salesperson's geographic responsibilities. Assume that there is a metadata group for each country and that some employees have responsibilities in multiple continents. The following figure depicts continent-level subsetting based on each salesperson's metadata group memberships.

Sales Example: Data Model

[Sales Example: Data Model]

This approach provides aggregated retrieval and flattens the group structure. Each user gets all rows that are permitted for any groups that the user belongs to. To enable everyone to see the global totals, the security associations table includes a row that pairs the PUBLIC group with global totals.

Content of a Security Associations Table

A security associations table is a type of table that documents the relationships between a user and some criterion on which you are making access distinctions. When access distinctions are based on each user's place within an organizational hierarchy, the security associations table must contain a representation of the reporting relationships within the organization. If access distinctions are based on some other criterion (such as each user's project assignments), then the security associations table should reflect that criterion.


Format of a Security Associations Table

BI row-level permissions do not require that the security associations table have a particular format. However, the format of a security associations table can affect filter performance. This topic describes a format that supports efficient hierarchy-based filtering. This format is useful for many common scenarios, because security policies are often hierarchical. For example, a typical business requirement is that a manager can see data for all of the employees that he or she manages either directly or indirectly.

The following figure depicts two ways to structure a security associations table that documents each user's place in a simple organizational hierarchy. The sparse version of the table includes only direct reporting relationships; information about indirect relationships must be derived. The fully articulated (or robust) version explicitly includes indirect reporting relationships along with direct reporting relationships; this is advantageous for query performance.

Representations of an Organizational Hierarchy

[Representations of an Organizational Hierarchy]

The table that uses the fully articulated format explicitly includes not only the hierarchy's immediate parent-child relationships, but also every other ancestor-descendant association (such as grandparent-child and great grandparent-child). This facilitates simpler queries by eliminating the need to traverse the hierarchy to find all of the descendants of any particular node.


Creation and Maintenance of a Security Associations Table

This topic contains a general discussion about creating and managing a security association table for use with dimensional target data. BI row-level security does not require that target data conform to a particular structure. The description in this topic is for dimensional data, because that is a frequently used structure for query and reporting.

A security associations table is usually created as a new object by traversing an existing sparse table and filling in the indirect relationships to create a fully articulated (or robust) version of the table. If you do not have an existing sparse table, then you must create that object first.

Note:   If you want to enhance an existing sparse table rather than creating a new table, you should first review current uses of the sparse table to determine whether the additional rows will negatively affect those uses.  [cautionend]

In most cases it is helpful to have an index on the column in the security associations table that is used for filtering. In some cases, factors such as the size of the security associations table or query optimization features in a particular data source might negate the need for this index.

The security associations table must be maintained as security relationships change. This maintenance should be on a schedule that is appropriate for your environment. Typically, this maintenance is accomplished by a batch process (such as a nightly ETL process against the existing tables). In some cases, updates might be entered directly by an administrator.

Information Map Tasks


Overview

The following figure depicts the row-level permission aspects of information map design.

Information Map Design for Row-Level Permissions

[Information Map Design for Row-Level Permissions]

The following topics provide generic instructions for each of these four tasks.


How to Add a Security Associations Table to an Information Map

In order to make the security relationship information that you added to the data model available for filtering, you must incorporate that information in an information map. To enhance an existing information map to include a new security associations table:

  1. In SAS Management Console, register the new security associations table in the metadata.

  2. In SAS Information Map Studio:

    1. Add the table to your information map as a data source.

    2. On the Relationships tab, define an inner join that connects an identifier column in the security associations table with a corresponding column in the target table (or in an intermediate dimension).

    3. Make the security associations table a required table by performing these steps:

      1. Select Edit [arrow] Properties [arrow] Information Map, and then select the Required Tables tab in the Information Map Properties dialog box.

      2. In the Available tables list, select the table that you are using as a security associations table. Use the arrow button to move the table to the Required tables list. Click OK.

Note:   We recommend that you do not create data items from columns in the security associations table. Excluding these column references from the information map prevents their values from surfacing when reports are created in SAS Web Report Studio.  [cautionend]


How to Create an Identity-Driven Filter

To create a filter that is based on an identity-driven property, perform these steps in SAS Information Map Studio:

  1. Open the information map, save the information map, and select the Design tab. Select Insert [arrow] New Filter to open the New Filter dialog box.

  2. Enter a name for the filter and select a character data item (or click Edit Data Item and use the expression builder to define a character item).

  3. In the New Filter dialog box, from the Enter value(s) drop-down list, select Derive identity values (for row-level permissions).

    The examples column shows what your values are for each property.

    • The SAS.PersonName value corresponds to the Name field on the General tab of your user definition. This is not always the same as the value of the Display Name field.

    • The SAS.IdentityGroupName value is usually blank and isn't often useful.

    • Although the SAS.IdentityGroups property displays only one value, this property actually returns a list of the groups and roles that you belong to.

    • The SAS.ExternalIdentity value is populated only in certain circumstances. See External Identities.

  4. Select the row for the identity-driven property that you want to use in the filter. See Identity-Driven Properties.

    Note:   Not all conditions support all identity-driven properties. Make sure that the selection in the Condition drop-down list is appropriate.   [cautionend]

    Note:    If you use the IdentityGroups property, set the condition to Is equal to or Is not equal to. For this property, these conditions are converted to an IN (or, NOT IN) statement when the query executes.  [cautionend]

  5. Select the Hide from user check box at the bottom of the Definition tab. This prevents the filter from being surfaced (and potentially removed) when reports are created in SAS Web Report Studio.

  6. Click OK. The new filter is now available for use in the current information map.


How to Assign a Filter for Row-Level Permissions

To use a filter for security purposes, assign the filter as an authorization-based prefilter or a general prefilter.

To assign a filter as an authorization-based prefilter:

  1. Open the information map, save the information map, and select Tools [arrow] Authorization to open the Authorization dialog box.

  2. In the Users and Groups list, select (or add) the identity that should be subject to the filter.

  3. Make sure that the right user or group is selected. Add an explicit [white check box] grant of the Read permission.

  4. Click Add Condition to open the Row-Level Permission Condition dialog box.

  5. In the Selected filters list (the right-hand panel), select the table that you are using as a security associations table.

  6. In the Available filters list, select the filter and then use the arrow button to move the filter to the Selected filters list. Click OK.

  7. In the Authorization dialog box, click Close.

  8. To make your changes take effect, save the information map.

To assign a filter as a general prefilter:

  1. Open the information map, save the information map, and select Edit [arrow] Properties [arrow] Information Map.

  2. In the Information Map Properties dialog box, select the General Prefilters tab.

  3. In the Selected filters list (the right-hand panel), select your security associations table.

  4. In the Available filters list, select the filter and then use the arrow button to move the filter to the Selected filters list. Click OK.

Verification

In a high security configuration, final verification must be performed from within SAS Web Report Studio. This testing requires that you log on to that application using different accounts.

If you are using server-side pooling, or if you have physical access to the data, you can do some preliminary testing to check your filter logic from within SAS Information Map Studio. Before you test an information map from within SAS Information Map Studio, you should save the information map to ensure that all settings are applied. To test a filter that is based on an identity-driven property, use different accounts to log on to SAS Information Map Studio. To test other filters, temporarily assign the filters to your identity.

Note:   If you are using server-side pooling, your results in SAS Information Map Studio should be the same as your results in SAS Web Report Studio. See Choices in Workspace Server Pooling.  [cautionend]

Previous Page | Next Page | Top of Page

Copyright © 2011 by SAS Institute Inc., Cary, NC, USA. All rights reserved.