Set up Model-Level and Row-Level Security in Deliver Instances

Note: This article is intended for database administrators. Ensure the following tasks are completed by someone with the appropriate database administration privileges.

Security allows developers to implement role-based access to a Deliver instance endpoint, granting access either to the entire dataset or to a filtered subset based on specific field values.

Model-level security grants full access to an endpoint. It is configured by creating a role with no associated Row-Level Security Setup and mapping that role to an endpoint. All members of that role will have unrestricted access to the endpoint data.

Row-level security (RLS) filters the data available through an endpoint using field values configured in a Row-Level Security Setup, which is then associated with roles or individual user logon credentials. Different users can therefore see different subsets of data when accessing the same endpoint.

Row-Level Security is implemented by following these general steps:

1. Create Roles consisting of users or groups of users.
2. Create Row-Level Security Setups that grant access to one or more specified field values.
3. Associate Row-Level Security Setups with specific Roles.
4. Associate Roles with specific Endpoints.

A Row-Level Security Setup is created under a specific field and defines which field values a role or user will have access to. Any data not related to those field values is inaccessible. For example, a "TerritoryWest" Security Setup created on the "Territory" field grants access only to data associated with the "West" territory value.

Model-level security

Model-level security grants full access to an endpoint for all members of an associated role. Once one or more roles are associated with an endpoint, users who are not members of any of those roles will receive an access error when attempting to open the endpoint.

Add a role to a Deliver instance

1. In the Deliver instance tree, right-click Roles and select Add Role.

   

2. The Add Role window opens.

   

3. In the Name box, enter a name for the role.
4. Click Add Users to add users from the local computer's user directory. The standard Select Users and Groups dialog opens.
5. Click Add External Users to add external users such as those in Azure Active Directory. The Add External Users window opens.

   

6. Enter the user's email address and click Add. Azure Active Directory Groups can also be added by entering the group name in the following format:

   <obj:groupid123@tenantid123>

7. Click OK to return to the Edit Role window, which lists all added users in the Instance pane.

   

8. Click OK to save the role.

Map a role to an endpoint

Mapping a role to an endpoint grants model-level access to that endpoint for all members of the role, provided the role has no associated Row-Level Security Setup. In the Deliver instance tree, expand the Roles folder, then use either of the following methods:

• Right-click a role, select Endpoints, and then select the specific endpoint.

  

• Drag the role onto the endpoint.

  

A role associated with an endpoint but not with any Row-Level Security Setup provides model-level (full) access to the endpoint. Once a role is associated with a Row-Level Security Setup, it provides row-level access instead.

Row-level security

Row-level security is implemented by associating a Row-Level Security Setup with an existing role. The process therefore begins with the same steps as model-level security (creating a role and mapping it to an endpoint), and then adds a Row-Level Security Setup to the role.

There are two types of Row-Level Security Setup:

• Manual — Values and members are mapped directly in the security setup dialog.
• Dynamic — Mappings are read from a table in a Prepare instance.

Add a Row-Level Security Setup (manual)

1. Right-click a field in the Deliver instance and select Add Row-level Security Setup.

   

2. In the Name box, enter a name for the setup.
3. In the Type list, keep the default selection of Manual.
4. Add one or more security mappings:
   1. From the Values list, select one or more available field values.
   2. From the Members list, check (Role Members) to apply the setup to members of any role it will be associated with.
   3. (Optional) Use the Add Member box to add specific users individually. Enter a username and click Add Member. Usernames may need to be fully qualified in the format <DomainName\UserName>.
5. Click the Add > button to map the selected values to the selected members.
6. Click OK to save the setup.

Add a Row-Level Security Setup (dynamic)

A Dynamic Row-Level Security Setup reads its value-to-member mappings from a table in a Prepare instance, eliminating the need to update the security configuration when users join or leave the organisation.

1. Right-click the appropriate Deliver instance field and select Add Row-level Security Setup.
2. In the Name box, enter a name for the setup.
3. In the Type list, select Dynamic.

   

4. In the Table list, select the Prepare instance table or view that contains the mapping data.
5. In the 'Values' field list, select the column whose values correspond to the Deliver instance field values used for filtering.
6. In the 'All values' value box, enter a value that represents all field values — for example, All.
7. In the 'Members' field list, select the column that contains the values corresponding to role members — for example, a column containing domain usernames.
8. In the 'Role members' value box, enter a value that indicates any member of an associated role — for example, Role Members.
9. Click OK to save the setup.

Map a Row-Level Security Setup to a role

After creating a Row-Level Security Setup, map it to the appropriate role using either of the following methods:

• Right-click the Row-Level Security Setup, select Roles, and select the specific role.

  

• Drag the Row-Level Security Setup onto the role.

Example: Manual RLS with multiple roles and security setups

When using (Role Members) in a Manual Security Setup, each role should have its own dedicated security setup. This approach works well with Active Directory groups — when group membership changes, neither the roles nor the security setups need to be updated.

Example configuration with five territory-based roles (West, East, Midwest, South, All):

• Five roles, each with a different AD group as its members.
• Five Security Setups, each mapping a different territory field value to (Role Members).
• Each Security Setup is mapped to its corresponding role.
• All five roles are associated with the same endpoint.

Example: Manual RLS with additional members

As an alternative to using (Role Members) with multiple setups, individual users can be mapped directly to field values using the Add Member button. This allows different users to be assigned different values within a single Security Setup.

Example: Dynamic RLS — domain logon mapped to name

This example uses a Dynamic Row-Level Security Setup to filter data based on the domain username of the person viewing the endpoint in a presentation application such as Power BI.

It is recommended to open the Deliver instance endpoint in Power BI before creating the Security Setup, and to create a test table to verify that the configuration is working correctly. On the Power BI Desktop icon, Shift+right-click to open a context menu where the application can be run as a different user, allowing testing of the endpoint under different credentials.

The first step is to create a role whose members are the AD group containing all relevant users — for example, all Sales Reps.

A Dynamic Row-Level Security Setup reads its mapping from a Prepare instance table. For this example, the table contains two columns:

• FullName — the name of the Sales Rep as it appears in the Sales fact table.
• Members — the domain username or logon of the Sales Rep.

Once the role and the Prepare instance table are ready, configure the Dynamic Security Setup as described in the Add a Row-Level Security Setup (dynamic) section above, selecting:

• 'Values' field: FullName (corresponding to the Deliver instance Sales table field)
• 'Members' field: Members (containing domain usernames)

After mapping the Setup to the role, each Sales Rep sees only their own data when opening the endpoint, matched by their domain username. Because the mapping is Dynamic, new Sales Reps are picked up automatically when they are added to the Prepare instance mapping table and the tasks are executed — no changes to the security configuration in Jet Analytics Data Integration are required.

Example: Dynamic RLS — domain logon mapped to territory values

The manual multiple-roles approach can be replaced with a single role and a single Dynamic Security Setup, using a Prepare instance table that maps each user to their permitted territory value.

Once the mapping table is available, configure the Dynamic Security Setup to use it, then associate the setup with a single role that contains all relevant users, and associate that role with the endpoint.

Example: Dynamic RLS — product security using role members

This example restricts what product data a group of users can see, while another group retains full access.

Create an AD group containing all users who should see the restricted product set. Then create a Prepare instance table with one column for the permitted product values and a second Members column containing Role Members for every row.

Create a Dynamic Security Setup under the Product_Name field using this table. Users in the restricted group will see only the products listed in the mapping table.

Users who are not members of any role associated with the endpoint will see an access error when attempting to open it.

To give other users full access without creating a separate Row-Level Security Setup, create a role with those users as members and associate it with the endpoint — with no associated Security Setup. This is model-level security as described at the start of this article.

Alternatively, create a Row-Level Security Setup that maps those users or role members to the All field value and associate it with its own role on the endpoint. Both approaches grant equivalent access.

This same configuration can also be achieved using a Manual Security Setup:

The advantage of a Dynamic Security Setup is that changes to the product list do not require updating the security configuration — only the Prepare instance mapping table needs to be updated. Execute the Ingest data sources, Prepare instance tables, and Deliver instance endpoint to pick up the changes automatically.

Security approaches summary

Multiple roles can be associated with the same Row-Level Security Setup and vice versa, providing flexibility in access control. The two fundamental approaches are:

• One role and one setup:
  • Add one role. For SSAS Tabular, all users and groups that require access must be members of the role. For Qlik, the role can be empty as it serves only as a link between the setup and the endpoint.
  • Add one Row-Level Security Setup mapping the relevant values to the appropriate members or users.
  • Using Active Directory groups as role members simplifies ongoing maintenance.
• Multiple roles and multiple setups:
  • Add one role per user or group that should access a specific data subset.
  • Add one Security Setup per role, mapping the relevant values to (Role Members).
  • Map each Security Setup to its role, and associate all roles with the endpoint.
  • This approach uses roles as user groups within the Deliver instance.

Analysis Services and Qlik handle security differently:

• On Analysis Services, access is granted to a role, and Jet Analytics Data Integration uses DAX scripting to enforce row-level access for role members.
• On Qlik, there are no roles — access is granted at the user and group level. Roles in the Deliver instance are simply an ad hoc grouping of users and groups with access to the same data.