Row-level access in Qlik Sense using custom properties in QMC

Unlike its predecessor QlikView, QlikSense has a very robust and flexible rule-based security engine that allows setting up access restrictions in a centralized fashion using QMC (Qlik Sense Management Console). However, one important thing is still missing, and it's row-level data access. You're supposed to use Section Access, a QlikView relic which has only one good feature -- it works.

Luckily, it is possible to set up row-level access right from QMC, leveraging all the power of centralized security management. It's not completely official, since it requires accessing QSR (Qlik Sense Repository) directly, although it is possible to encapsulate the logic in a reusable script thus making future maintenance easier.

Here is how it works:

In QMC we create a custom property (e.g. MyCustomProperty) with some possible values (e.g. "MARKETING" and "FINANCE"). Now, the QMC will have a new property on a user page - MyCustomProperty, which can be defined as MARKETING, or FINANCE, or their combination (or nothing at all). These values are stored in QSR (the repository).

Since QSR is a PostgreSQL database we can connect to it in a Qlik Sense application and create Section Access that will use the custom property values for dynamic data reduction.

To make it work, first, connect to your Qlik Sense repository.

The repository data model contains 100+ tables however we need only three of them:

• Users
• CustomPropertyDefinitions
• CustomPropertyValues

Subset of QSR data model

Here is the SQL query, needed to extract and join all the three tables to form Section Access.

SQL SELECT
    CASE WHEN
        //RootAdmins can always see everything
        usr."RolesString" LIKE '%RootAdmin%' THEN 'ADMIN'
        ELSE 'USER'
    END as "ACCESS",
 
    upper(val."Value") as "REDUCTION",
 
    upper(usr."UserDirectory") || '\' || upper(usr."UserId") as "USERID"

FROM "QSR"."public"."CustomPropertyDefinitions" def 

    LEFT JOIN "QSR"."public"."CustomPropertyValues" val
        ON def."ID" = val."Definition_ID"
       
    LEFT JOIN "QSR"."public"."Users" usr
        ON usr."ID" = val."User_ID"
       
    WHERE def."Name" = 'MyCustomProperty' and val."Deleted" is false
    ;

The query creates Section Access with three columns:

• ACCESS
• USERID
• REDUCTION

For every combination of user and its custom property value it creates a new row in Section Access, where REDUCTION corresponds to the values. The query also assigns ADMIN access to all RootAdmins in QMC (just in case). Everyone else gets USER access.

Now, include field REDUCTION (make sure its values are upper-cased) into your application data model, and Section Access will leave only rows where REDUCTION in the data model is the same as REDUCTION in Section Access for the current user, thus enforcing row-level data access.

A few gotchas:

1. To change permissions for a user -- change his/her MyCustomProperty in QMC, and reload the application (Section Access will keep old permissions until reloaded).

2. If you need to have users that have to access all data, but they are not RootAdmins, you will need to explicitly add all possible values of the custom property for the user, because in Qlik Sense its Section Access works in strict mode. You can do it manually, in QMC, but that's not convenient. Much easier is to create a new value (e.g. EVERYTHING), and then for users with this value assigned automatically create rows with all possible values (excluding EVERYTHING). QSR stores all possible values of a custom property in field "ChoiceValuesString" of table

"CustomPropertyDefinitions". All properties are stored together in one text field, separated as below:

FINANCE:,:MARKETING:,:EVERYTHING

In order to create a list from this line of text the following query can be used:

LOAD
    Subfield( [ChoiceValuesString], ':,:') as REDUCTION
;
SQL SELECT
    "ChoiceValuesString"
FROM "QSR"."public"."CustomPropertyDefinitions" def

    WHERE def."Name" = 'MyCustomProperty';

Once you get the list of values you can append it to the Section Access created previously.

3. To make Section Access work in scheduled reload tasks you must add the scheduler's account to Section Access:

CONCATENATE (YourSectionAccessTable) LOAD * INLINE [
ACCESS, USERID,REDUCTION
ADMIN,INTERNAL\SA_SCHEDULER,*
];

 4. You can wrap entire Section Access script into a common reusable script and insert it into applications using something like:

$(Must_Include=lib://SectionAccess/section_access.qs)

In this case even if Qlik will change QSR's data model you can only adjust your reusable script to make it work for all applications that use it.

UPDATE 12/14/2015

5. Before reloading an application to apply changes in Section Access make sure you close all browser tabs opened with the application (Load Editor, Data Model, etc.). Otherwise, you might get unpredictable behavior. Also make sure you reload applications with Section Access every time you changed custom properties for a user because Section Access keeps a subset of old repository data.