Securing Cubes

OLAP member-level permissions enable you to limit access to SAS OLAP data using filters. Each filter consists of an MDX expression that subsets the data in a dimension as appropriate for a particular user or group. The filters are stored in the metadata as permission conditions. This feature relies on the SAS OLAP Server for enforcement. At query time, the server performs the filtering to determine which dimension members should be returned to the user that submitted the query. This ensures that the filters are applied every time the data is accessed.

When you use OLAP member-level permissions, it is essential to understand these points:

Follow these steps to assign a permission condition to one or more members in a given dimension, for a selected user or group.

See also the example Setting Member–Level Permissions.

Use the following identity-driven properties in your MDX filters to subset the users that receive member-level permissions.

See also the example Setting Identity-Driven Security.

After you have set the necessary permission conditions for dimension members, you must indicate whether the OLAP server includes these permission conditions when processing cube queries. In order for a cube to control the roll-up values for designated members, the SECURITY_SUBSET PROC OLAP option must be set to YES when the cube is built. The SECURITY_SUBSET option is used in conjunction with permission conditions to specify whether the roll-up values include only the members in the permission condition or all of the members in the cube. If you set SECURITY_SUBSET=YES, then the cell values are recalculated at query time based on the security subset defined by the active permission conditions for the given user. If you set SECURITY_SUBSET=NO, then the OLAP server does not recalculate the cell values. The default value (NO) includes all members within a total.

This example demonstrates how you can use member-level permissions. These are the security goals in this example:

The following figure depicts the relevant parts of a company's organization structure and of the OLAP cube against which the company runs executive reports. Notice that the levels in the cube's Geography dimension closely correspond to the depicted organizational structure.

Here are the implementation steps for this example:

For example, the following table contains MDX expressions that you could use to subset the data based on each executive's area of responsibility.

Different users of cube data might have different security and access restrictions that must be complied with and applied when querying the underlying data for a cube. When selecting a data table for drill-through, you might need to define user restrictions for certain data in the drill-through table. The SAS Metadata LIBNAME engine is used to assign the drill-through table library on a per-session basis. This allows client credentials to be used when determining which columns the user can see. Columns which have ReadMetadata permissions denied on the drill-through table will not be visible to the user. If access is denied for a column on the drill-through table, that level must also be denied Read access in the cube. Conversely, if a level is denied Read access in the cube, that column in the drill-through table must have ReadMetadata access denied. For more information about the SAS Metadata LIBNAME engine see the topic “Pre-assigning Libraries” in the SAS Intelligence Platform: System Administration Guide.

Because of this enforcement, the following changes could affect your site:

When applying permissions to a cube, you might need to address permissions for different combinations of users, groups, SAS OLAP Servers, schemas, and cubes, as well as different elements of the cube, including the dimensions, hierarchies, levels, and measures. For example, you might need to grant ReadMetadata and Read access to the group that contains specific cube users. Or you might need to restrict Read access for different components of a cube (dimension, hierarchy, level, or measure) using MDX conditions for each cube component, per user, consumer, or group. These various combined permission settings can be easily created and managed with batch security that is applied through permission tables.

Starting in the third maintenance release for SAS 9.2, you can specify batch security in SAS OLAP Cube Studio and SAS Data Integration Studio with the Manage Permission Tables function. The Manage Permission Tables function enables you to create a special SAS data set known as a permission table that contains cube access controls for submitting in bulk. A permission table is a table of access control information that can later be applied to a cube with batch SAS code. The Manage Permission Tables dialog box enables you to create and modify permission tables as well as import access controls (permissions) from a cube or an OLAP schema. You can also execute the code interactively or export the code to a file for use in a stored process or deployed job flow.

When a cube is created, security for that cube is determined by the permission settings that are found in the cube metadata. In SAS OLAP Cube Studio, permission tables will appear in the metadata tree as a table. You must have WriteMetadata access to create and modify permission tables. To access the Manage Permission Tables function in SAS OLAP Cube Studio, select ToolsManage Permission Tables. The Manage Permission Tables dialog box appears.

Permissions tables have the following columns.

The following display shows the denial of read access to the levels Date and Customer_Age. Note the blank-delimited entry in the Items column.

The following display shows the denial of read access for the measures Quantity and Sum.