Implementing section access based on Qlik Sense user table.

By Vincent Hayward

Welcome to my third Blog post, which will be a basic tutorial of how to setup section access for your Qlik Sense application, based on the Qlik Sense user table. This approach, created together with my colleague Martijn Olivier, is straight forward to implement in various occasions. My aim is not to over complicate it with all the extra possibilities that are possible. Also bear in mind that it should not substitute application level access based on security rules. This method describes how to implement section access within an application, based on a custom property assigned to a group of users.

This tutorial is divided into 3 parts, in part 1 we will setup a custom property at the Qlik Sense Management Console (QMC), in part 2 we will set up the master table with all the users, in part 3 we set up the section access within the application itself.

Why use section access based on the user table?

The main reason for using the section access based on the users from the Qlik Sense user table is for having one central point of access management within the Qlik Sense environment itself and not having a second administration area built in Excel that needs to be in sync all the time.

Part 1 – Add Custom Property at the Qlik Sense Management Console (QMC)

First we start by adding a custom property, for example AppLevelAccess, at the Qlik Sense Management Console (QMC) and check ‘Users’ at Resource Types with some values, for example Application Another, Application Bitmetric and Application Charm.

Now with the custom property in place we can start assigning a custom property to an user by selecting for example “Application Bitmetric” to the custom property AppLevelAccess at the “Edit user”-screen for the user you want to apply access to “Application Bitmetric” on.

Now that we have finished the first part of the tutorial, where we have created an Application Level custom property that can be assigned to users and we have assigned one, named “Application Bitmetric, to an user.

Part 2 – Setup and load the master user table

For the next part we need to setup and load a master user table, that we can re-use for all applications that needs section access. We create this master table by connecting to the by default available data-connection named “monitor_apps_REST_user” and get the user specific data from the QRS.

Below I show you an example of how to get the user data from the Qlik Sense QRS and store it temporary to four specific tables, named User_definition, User_customProperties, User_roles and User_root. So we can drop the RestConnectorMasterTable and work from there. The script to do so, is shown in the image below.

Now that we have finished the extraction of the data, we can transform the data to one single table.

Before we go continue, I must point out that it could feel like an over developed approach with extraction and transformation as separate stages, but the reason I have created this multi stage approach is for two reasons; I really like to develop in a structured way with a good separation of concerns in order to facilitate easy of re-use possibilities. But also that it’s transparent for you as my reader to follow the tutorial step by step, so you are able to cherry pick and adjust it to suit your own needs.

With the tables User_definition, User_customProperties, User_roles and User_root available we can create one master table with all users and their specific custom properties, where we get the user id, the user role(s), it’s custom properties and the resource type. The script to do so, is shown in the image below.

The content of this table can be used to setup the section access within an application. But can also be re-used to have a clear overview of your active users, with all their assigned custom properties. An example of two records of this table is shown in the image below.

Now that we have finished the second part of the tutorial, where we have created a master table of users with custom properties, that can be used for many things, including populating a section access table.

Part 3 – Use the master user table as section access table

For the last part we can use the master user table to populate the section access table within an application. First we create the section access table by adding the Service Account SA_SCHEDULER as a first step. Then we add all admins from the UserCustomProperties.qvd as ADMIN. At last we add the regular users based on the AppLevelAccess custom property that we want to use, for example “Application Bitmetric”. The script to do so, is shown in the image below.

The section access as shown above is just a highly customizable example. You can change the level of admins to your own specifications, the same goes for the user level access on different levels and even adding data reduction is a possibility if you add for example a custom property “DataLevelAccess” with different values.

Now you are all set!

More information about managing security with section access can be found here: https://help.qlik.com/en-US/sense/June2020/Subsystems/Hub/Content/Sense_Hub/Scripting/Security/manage-security-with-section-access.htm

About The Author

Vincent Hayward

Vincent knows from an early age what he wants to do; something with computers. Thinking of Business Intelligence solutions is his passion and especially with QlikView and Qlik Sense. In addition to being an enthusiastic Business Intelligence Consultant; Vincent is father of 2 children and is married. In his spare time, Vincent is a indoor soccer player, videogamer and politician in the municipality of Westland.

3 Comments

  • 1
    Marc Algera
    June 20, 2020 - 21:38 | Permalink

    Thanks Vincent,

    Really a great article and easy to implement.

    Marc

  • 2
    July 18, 2020 - 14:56 | Permalink

    Thanks Vincent,

    It’s going to be “standard operating procedure” for my installs.

    Thank you

    Arjen

  • 3
    Varsha
    September 24, 2020 - 15:05 | Permalink

    That’s a great article and very well explained.. Thank you Vincent

  • Leave a Reply

    Your email address will not be published. Required fields are marked *

    Read previous post:
    Auto AutoNumber your key fields

    A quick note on a cool feature that I recently learned from Oleg Troyansky: AutoNumber. I hear you thinking: "What?...

    Close