Sheet Restrictions With Security Rules

TL;DR – Security rule to demonstrate how to manage “Sheet” level access in Qlik Sense.

Qlik Sense is very flexible in how it helps businesses address many of their analytical needs as well as their data security concerns. When working with data, security plays a big role. Businesses want to make sure that their data security is being handled robustly and, at times, they need to ensure only the right person sees the fraction of data that is relevant to them. To address those scenarios, Qlik Sense provides many different approaches, catering for different situations. For example, we have platform level security which are managed via various user and platform attributes (known as Attribute-based access control, ABAC) and then we have data level (row based) security that is being managed per application via Section Access. There are several layers of security in Qlik Sense, but that is a topic for another day.

Today, I want to address one of the most common scenarios we see, where business would like to restrict a set of “Analyser” users to a particular “Sheet” or set of “Sheets” within a Qlik Sense application. This is not something we can achieve in Qlik Sense using out of the box security rules. This requires some security rule changes, and I aim to demonstrate a solution in a step by step guide here.

Note: This is only applicable to Qlik Sense Enterprise for Windows.

Qlik Sense application: Business has developed a Qlik Sense application which consists of many dashboards showing details on many metrics, from marketing to sales. One dashboard (Sheet) within the application is for the CEO, one for the sales team and one for the marketing team. Application is to be published in its own stream (named “ournewstream”).

Users: All the users consuming this application are “Analyst” license holders and can only consume, but not create any new content (apart from stories!). Users job roles span across all hierarchies of the business, from the CEO to the sales execs.

Problem: Business would like the CEO to see only the “Executive summary” sheet, sales execs to see the “Sales overview” sheet, marketing execs to see the “Marketing overview” sheet and CFO, CMO to see the entire application.

Change the default “Stream” rule from –

(resource.resourcetype = "App" and resource.stream.HasPrivilege("read")) 
or 
((resource.resourcetype = "App.Object" 
    and resource.published ="true" 
    and resource.objectType != "app_appscript" 
    and resource.objectType != "loadmodel") 
    and resource.app.stream.HasPrivilege("read"))

To the following –

((resource.stream.name!="ournewstream") 
and 
    (resource.resourcetype = "App" and resource.stream.HasPrivilege("read")) 
    or 
    ((resource.resourcetype = "App.Object" 
        and resource.published ="true" 
        and resource.objectType != "app_appscript" 
        and resource.objectType != "loadmodel") 
        and resource.app.stream.HasPrivilege("read"))))

Create a new rule as shown below.

((resource.stream.name!="ournewstream") and (
    (resource.resourcetype = "App" 
    and 
    resource.stream.HasPrivilege("read")) 
    or 
    (((resource.resourcetype = "App.Object" and (resource.published ="true" and (RESOURCE.NAME != "Executive summary" or RESOURCE.NAME != "Marketing overview"))) and
    (user.group= "sales group") and resource.app.stream.HasPrivilege("read")))
    or 
    (((resource.resourcetype = "App.Object" and (resource.published ="true" and (RESOURCE.NAME != "Sales overview" or RESOURCE.NAME != "Marketing overview"))) and
    (user.group= "CEO group")and resource.app.stream.HasPrivilege("read")))    
    or 
    (((resource.resourcetype = "App.Object" and (resource.published ="true" and (RESOURCE.NAME != "Executive summary" or RESOURCE.NAME != "Sales overview"))) and
    (user.group= "market group") and resource.app.stream.HasPrivilege("read")))
    or 
    (((resource.resourcetype = "App.Object" and (resource.published ="true")) and
    (user.group= "CFO-CMO") and resource.app.stream.HasPrivilege("read")))
    )
)

Well, the change in “Step 1” only adds the new stream as an exception to the default stream access rule, whereby the default rule will still be applicable to rest of the streams apart from the new stream.

The new rule then only targets the new stream where the application will be published and grant access to the objects (sheet in our case) based on users’ group attribute. The users’ group attribute comes from the user directory. You may wish to use custom properties if the correct user group configurations are not available.

Please, make sure to always validate and preview the rules before applying it. Testing is the key here. Make sure to keep the “disabled” box ticked until you are ready to deploy the rule. This will save you a lot of headaches in long run. And, like always, make sure you have backup(s) before you start making any changes to the security rules.

As stated before, Qlik Sense is very flexible when it comes to security. This is just one possible way to achieve the goal, but certainly not the only one. Be mindful of using complex object level security like this one across the entire platform, as this do not scale very well with a larger user base and suffers from delays, resulting in bad user experience.

Few principles to remember when working with security rules in Qlik Sense –

Hope this gives some ideas on how to manage sheet level security in Qlik Sense. Combining this technique with section access can provide really robust security for most use case.

Thanks for reading!