Prerequisites

To add a row security object, you first need to create a dataset of user/group-to-attribute mappings in the data warehouse. All user IDs that run queries against the model must be included in this dataset. You can add or remove users/groups at any time by inserting or deleting rows. For more information, see Mapping table example.

Additionally, if you plan on using the row security object to secure a dimension, verify whether the dimension contains multiple hierarchies:

• If it has multiple hierarchies, they should share the same leaf level (the most granular level of the hierarchy). This ensures a relationship is automatically established for every hierarchy after you create the row security object. To check if the hierarchies share a level, open the leaf level (indicated by a leaf icon) of each hierarchy in the dimension and check if the Query Name field is the same for each.
• If the hierarchies don't share a level, find one hierarchy's leaf level (indicated by a leaf icon) and duplicate it to the other hierarchies in the dimension. Then delete the old leaf levels from the other hierarchies.

Create the row security object

1. In Design Center, open the Data Sources panel and locate the security dataset.

2. Click the dataset's context menu and select Create row security. The Edit Row Security panel opens.

3. Complete the following fields:

   • Display Name: The name of the row security object, as it appears in AtScale.

   • Unique Name: The unique name of the row security object. This value must be unique across all repositories and subrepositories.

   • Description: A description of the row security object.

   • Dataset: The dataset that contains the user/group-to-attribute mappings.

   • Attribute Filter Keys: The column in the security dataset that defines the rows each user/group has access to.

   • Lookup Rules: The method AtScale uses to apply security:

     • None: The system enforces security by joining with the row security table.
     • Use Filter Key: The system enforces security by first looking up the Filter Key Column values using the user/group IDs, then uses those values as a constraint in a second query against the fact or dimension dataset. Some data warehouses perform better with this option.

   • IDS: The column in the security dataset that contains the user/group IDs.

   • ID Type: Determines whether the IDs are user or group IDs.

   • Scope: Determines which queries security is applied to: Related, Fact, or All. For descriptions of these values, see Setting the Scope.

   • Secure Totals: Enables/disables the secure totals functionality. For more information, see below.

4. Click Apply.

The row security object appears in the security/ folder in the Repo Browser.

Add the row security object to a dataset in a model

1. Open a model and switch to the Canvas tab.
2. In the Repo Browser, locate the row security object, click its context menu, and select Add to Model. The row security object appears on the Canvas.
3. Click and drag the dataset columns you want to secure to the row security object. For each, the Edit Relationship panel opens.
4. Edit the relationships as needed, then click Apply.

Add the row security object to a dimension in a model

1. Open a model and switch to the Canvas tab.
2. Click the context menu for the dimension you want to secure and select Edit.
3. Switch to the Canvas tab for the dimension.
4. In the Repo Browser, locate the row security object, click its context menu, and select Add to Dimension. The row security object appears on the dimension Canvas.
5. Click and drag the columns you want to secure from the dimension dataset to the row security object. For each, the Edit Relationship panel opens.
6. Edit the relationships as needed, then click Apply.

Note: You cannot connect a security dimension to a degenerate dimension. If you wish to secure a degenerate dimension, connect the security dimension directly to the degenerate dimension's source dataset instead.

Secure Totals

When enabled, the security restriction applies to the following:

• Subtotal measures of the secured hierarchy level or reachable attributes of higher levels.
• Queries that select secured fact tables (a scope of all or fact), but do not select the secured dimension.
• The grouping of the secured level.
• The secured level's secondary attributes.
• Attributes and nested dimensions that are reachable from hierarchy levels lower than the secured level.

When secured totals is disabled, the security restriction only applies to the following:

• The grouping of the secured level.
• The secured level's secondary attributes.
• Attributes and nested dimensions that are reachable from hierarchy levels lower than the secured level.