Row-level Security

Overview

Row-level security (RLS) permits user access to some rows in a table/view while denying access to the other rows. “User access” in this case could mean viewing data, loading data using custom pipelines, editing data, form-based data entry and/or consuming the OData API.

RLS was designed to allow multiple countries to store data in the same table, while not being able to see or change data of other countries. But it is generic in nature and can be used for any similar scenario (security by major office, security by org unit or team, etc.).

RLS works by matching values assigned to individual user accounts to values in a column (the RLS column) of a table or view. The user is granted access only to the matching rows.

There are 2 main steps to setting up RLS. A walkthrough of each step is provided below.

1. User accounts are assigned values. This is done on the Admin/Users page.
2. RLS is applied to 1 or more roles. This is done on the Admin/Roles page. The role determines which users and permissions are affected by RLS and links user account values to columns in tables/views.

Setting up RLS requires MART_MANAGE permission.

Step 1 - Assign values to user accounts

Create a custom user property

Navigate to the Admin/Users page and click Custom Properties.

Click Add Property and enter a name for the new property and click Create.

The property is created and appears in the list.

Assign values to users

Now that the custom user property has been created, we can assign each user a value for the property.

Assignment is currently done by exporting the list of users and making edits in an external spreadsheet application and importing the changes. One day a user interface for these edits may be provided but one advantage to editing in an external application like Excel is that it makes it easier to assign values for long lists of users, ie 100-200 user accounts.

On the Admin/Users page, click the Export button

Open the exported Excel file - the newly created property should appear as a column in the spreadsheet. Enter values for users in the new column. A user can be assigned multiple values by separating multiple values with a comma.

Save and upload the modified users file using the Import button. A normal batch will be created (like when loading data) and the changes to user properties will be summarized in the batch preview. Commmit the batch.

Return back to the Admin/Users page and select one of the users. The assigned properties of the user are visible.

Step 2 - Role setup

Create a role in the normal way. Only the users and permissions in this role will be affected by RLS.

Permissions affected by row-level security:

• Viewing data (DATA_VIEW permission)
• Loading data using custom pipelines (LOAD_ORIGIN permission)
• Editing data (DATA_EDIT permission)
• Form-based data entry (FORM_DATA_ENTRY permission)
• Consuming the OData API (DATA_VIEW permission)

If a user is in multiple roles with RLS on the same table/view, the access is ORed across the roles. If a user has access to country “ABC” via one role and country “DEF” via the second role, the end-user has access to both ABC and DEF records. See the Use Case Scenarios section in the Troubleshooting section below for more details about behavior when a user is in multiple roles.

To activate RLS on a role, navigate to the RLS tab of the role and select the custom user property created above.

Then select a table/view plus a column (the “RLS column”) in that table/view to associate the user property with. Selecting “Allow null values” means that the end-user also has access to rows where the values are null in the RLS column.

In the image above row-level security has been applied on the DIM_ISO3 column in the DATA table and also on the DIM_ISO3 column in the custom SQL view V_DATA. Rows with NULL values are not accessible in the table but they are in the view.

RLS via fields in FK table (double underscore fields)

If a data table to be secured by RLS has a country column but not a region column, it is still possible to define RLS by region if 1) the country column is a foreign-key field and 2) the foreign-key table (ie a country reference table) contains a region field and 3) the region field is a business primary key field in the foreign-key table.

For example, consider setting up RLS on table DATA which has foreign key field DIM_ISO3 which points to REF_COUNTRY

REF_COUNTRY has 2 BPK fields, REGION and ISO3. Normally ISO3 would be sufficent as a unique business primary key, but in order to use the REGION field as an RLS column, REGION also has to be added as a business primary key.

It is now possible to choose REGION in the RLS column dropdown box. It appears as “DIM_ISO3__REGION”: the 2 underscore characters indicate that this field comes from the foreign key table linked by the DIM_ISO3 column.

Then a custom user property named “REGION” should be created and users should be assigned region codes rather than country codes.

Generically speaking: if the RLS column is a foreign key (FK) field, it is possible to select one of the business primary key (BPK) fields from the FK table. These fields appear as having double underscores in them. This allows setting row-level security based on a column in a related reference table that is not directly stored in the main table.

Troubleshooting

Row-level security requires extra troubleshooting because each user experience can be different from the other.

Simulate with Active Roles

As a mart admin, to repoduce the behavior of an end-user reporting an access issue where RLS is involved:

1. Put your account into the same role(s) as the end-user
2. Assign yourself the same user property(ies).
3. Choose the same roles as the end-user in the Active Roles dropdown.

You will now experience the system as the end-user.

Use Case Scenarios

This section describes various scenarios to better understand system behavior in the context of RLS.

BIG TABLE OF SCENARIOS