Qlik Community

Qlik Sense Enterprise Documents & Videos

Documents & videos about Qlik Sense.

Dynamic Sheet Exception

Partner
Partner

Dynamic Sheet Exception

One missed feature from QlikView to Qlik Sense is the ability on a user/group basis hide/show sheets - dynamically. In QlikView this type of authorization is ensured by a conditional show and can be defined by a variety of expressions.

In Qlik Sense there has been a few solutions to set this statically, see e.g. Sheet or App Object Level Security Qlik Sense for more information. What if a user changes department? Or a new user comes in?

Such solutions are not allowing us to change access based on a dynamic group membership. It also forces us to mostly use a single group or user.

What if you have a hierarchy of groups, some users can see full countries, some should only see a single ProfitCenter? Or you have a complex rule system that defines that sheet A should be seen by Group A, but sheet B should be seen by Group C, D and E?


Starting with February 2018 release we can have expressions for sheets. The expressions itself cannot be immediately read by the QMC, but the name or description can be. This means that the sheet-names can look good, despite the proper name having a very technical name (for a solution like ours).


By using the QRS API and adding a custom property on a user-basis, we've managed to work around the limitation of static access to sheets, we can apply a dynamic sheet level security. The definition can be set in the script, according to how you want it to play out, just like Section Access.  We call this "SheetException".



Here's how it works:

  1. A sheet is given a specific name, e.g. SheetException_001_Goeteborg
  2. The sheet name is viewed to the user as a reading-friendly name, e.g. "Dashboard". The end-user will not see the name "SheetException_001_Goeteborg".editmode.png
  3. Within the script we read users/groups that defines which users should see the sheets with the corresponding name (SheetException_001_Goeteborg), which could include both GROUP_Sweden and GROUP_PROFITCENTER_GBG. Or even the user "King_of_Sweden". It doesn't even need to be set in script. The important step is to find a set of usernames by a definition which can be picked up, however it is neat to keep it where the code is - Qlik Sense app script.
  4. The script outputs a small csv file for all users and their corresponding custom property. For each customproperty we output a row. So if we have several countries, we add several rows, one for each country, user "filip" for SheetException_001_Goeteborg and user "johan" for SheetException_001_Malmoe. A rootadmin which should see all countries dashboards, gets one row per country. We can also in the script define SheetException_001_NotGoeteborg, meaning both "fredrik" and "johan gets the same value - "NotGoeteborg". This is entirely up to the developer.
  5. All the csv files are put in a directory which is read by a PowerShell script, which is run once a day (usually enough). The script is executed from Windows Task Scheduler. The script picks up all rows, clears the current SheetException properties and adds all the ones it can find in the script.
  6. A rule in the QMC defines a 'resource.name like "SheetException*"' implies specific access, and that if 'user.@SheetException=resource.name', the user is allowed to see the sheets.
  7. Voila!

Note: this does not restrict the data, that is what Section Access does. However you can of course use the same groups.

How to do it:

1. First you need to change some rules. We need our rules framework within the QMC to apply the correct permissions. You need to be an administrator to do this.

2. The standard rule "Stream" needs to be modified. Best practice would be to create a new one, and disable the old one instead of modifying the original rule - in case something would go wrong.

3. In the new rule, called "Stream for SheetException", you need to add write the following conditions:

(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.objectType = "sheet"

and !(resource.name like "SheetException_*") ) )

and resource.app.stream.HasPrivilege("read") )

4. And then create another new rule, for which SheetException goes to work. Let's call this one "SheetException" - where we are applying "Read" actions on "App.Object_*":

((resource.objectType="sheet" and resource.published ="true" and resource.name like "SheetException_*" and (user.@SheetException=resource.name or user.roles="RootAdmin" )) or (

resource.resourcetype = "App.Object"

and resource.published = "true"

and resource.objectType != "app_appscript"

and resource.objectType="sheet"

and !((resource.name like "SheetException_*") )

) or (resource.published ="true" and resource.objectType!="sheet")) and resource.app.stream.HasPrivilege("read")

5. We also need a rule for general stream/app access, which can be done for instance with "Stream Everyone" or the attached "Group Access Rule". Usually in a standard environment the "Stream Everyone" suits well.

(complete set of rules are attached)


6. Custom properties needs to be added. Custom Properties are metadata that can be associated to resource types within the QMC. Each custom property contains a list of possible values. They are primarily used to configure security rules using metadata rather than explicit values. Create the following custom property


Name: SheetException

Description: Used for assigning dynamic values that allows certain users to access specific sheets. Added automatically through a PowerShell script.

Resource Types: Users

Values: (none - they are automatically added)

rootadmin_public.png


Attached in the zip file is a picture for further reference.

5. A sheet-title needs to contain "SheetException_*". Usually it tells a specific number and a definition, e.g. SheetException_001_Goeteborg. Feel free to pick any number as long as it doesn't already exist already, the number is just to distinguish where it is defined. The sheet expression contains a user-friendly title (e.g. "Summary"), and the description usually holds the same user-friendly title and a description to make it easier to search for (e.g. "Summary - only for Goeteborg"). Sheet-title-expressions are not searchable in the QRS API and thus it helps with system administration to be able to search for it.


6. The actual definition for who gets to see which sheets is set in a couple of CSV files that gets picked up by a PowerShell script. In our example they are generated by one or several Qlik Sense app script so that they can be fully dynamic, and could be practically anything, just as a Section Access script. This is to be decided by yourself; if it is a city, a profitcenter, a group, a hierarchy for a whole country, a user or whatever. Usually when we build some logic into the script, we can add admins, which gets to see all sheets as well, or just some. Rootadmins see all sheets anyway, according to our script above. Attached is an example of a QS script which includes "view-all-sheets-admins" called SheetAdmin.

7. Each Qlik Sense app that applies SheetException stores .csv files from the script at a specific folder, e.g. C:\localdata\SheetException\*.csv. This is then the location which is read by a the PowerShell script UpdateSheetException.ps1. If we reload our apps once a day, thus outputting new versions of CSV files once a day, we would like to run this just afterwards, to apply the proper authorization on our sheets. Thus we schedule the PowerShell script once a day as well, which should be enough, triggered by Windows Task Scheduler. If the script is not executed properly, it will not work. If it is not executed regularly - it is not dynamic and a static solution can be used, see e.g. Sheet or App Object Level Security Qlik Sense. The PowerShell script adds the corresponding custom property values for each user, found in the CSV files, through the QRS API. For this we need to install Qlik-Cli on our server, which can be found at https://github.com/ahaydon/Qlik-Cli , which simplifies API communication with our server. If you haven't installed it already, do so by the following command, as mentioned on github, in your PowerShell console:

Get-PackageProvider -Name NuGet -ForceBootstrap

Install-Module Qlik-Cli

7. Getting Qlik-Cli to work can sometimes be a bit tricky. For us it's enough to run it locally, as an administrator at the PowerShell console, with the service account. The following code allows us to verify communication:

connect-qlik SERVERNAME.DOMAIN.LOCAL -Username "$($env:userdomain)\$($env:username)"

Some instances require modification of the script, e.g.:

connect-qlik -Computername https://MYSERVERNAME:4242 -TrustAllCerts -Username DOMAIN\username


Thus it is important to verify access with the script before we continue. When running the above command, the reply should be something like:


buildVersion      : 22.0.4.0

buildDate         : 9/20/2013 10:09:00 AM

databaseProvider  : Devart.Data.PostgreSql

nodeType          : 1

sharedPersistence : True

requiresBootstrap : False

singleNodeOnly    : False

schemaPath        : About

goeteborg_user.png

7. 4 fields needs to be included in the csv file: Username, SE_Value, Origin, SE_Expression - in that order - otherwise our PowerShell script doesn't understand it. See the sample app for script on how to apply them.

a. Username - defines each user, meaning we need a list or a connection to our user directory.

b. SE_Value - the value we are basing the permissions on, e.g. Goeteborg (as in SheetException_001_Goeteborg).

c. Origin - optional but useful, so we can trace each .csv file when we browse the C:\localdata\SheetException\*.csv directory, not really used by any other services.

d. SE_Expression - optional but useful, a copy of the definition for trace-ability. How does your IT support know who should be allowed to see which sheet... exactly!

goeteborg_user_cp.png


8. The values applied for a user matching a sheet-title allows access, so as long as you output a CSV file to this directory, the script picks it up and applies it. Put the file UpdateSheetException.ps1 in an appropriate folder, e.g. C:\admin\SheetException\ and schedule it with the attached Windows Task Scheduler task, to run e.g. once a day. Attached is a sample xml to import, which you need to modify to the correct paths. It needs to be executed with administrative rights in order for it to work, and remember to schedule it to re-run - so that new values can be propagated dynamically. A command you can use within Windows Task Scheduler is for instance:


Action: Start a program

Program/script: powershell

Add arguments: -file ".\UpdateSheetException.ps1"

Start in: C:\admin\SheetException

Remember to test the authorization of the sheets as a non-admin user.

Now you should have a dynamic access to sheets!



Changelog:

2018-08-16, 1.1:

Some types and elaborated on a few points.


2018-07-13, 1.0:

Initial release.

Attachments
Comments
zozo1992
New Contributor II

Excellent idea Erik.

However took me a while to customise for it to work. Would be great to update with the below to make it easier for other users:

  • It should be highlighted that there must be a stream security rule with read rights for the users in addition to the app and app.object access mentioned above. Thus create 3 security rules and not only 2.
  • It should be highlighted that we must create an empty custom property named 'SheetException' with users ticked, and leave the values empty for the PowerShell script to auto populate
  • Regarding the PowerShell script there are few errors:
    • $serveraddress must be the full url. This can be found by opening the desktop icon 'Qlik Management Console' that was created when installing Qlik Sense Server. Starts with https://yourservername
    • The #connects to our qlik database does not work and gives error thus I need to customize at as follows:
      • Connect-qlik -Computername https://MYSERVERNAME:4242 -TrustAllCerts -Username DOMAIN\username
        • must add ports 4242
        • replace MYSERVERNAME and DOMAIN\username
        • leave -Computername and -Username because these are commands
  • It should be noted that the PowerShell script must be executed for the loops to work. Because I was debugging the script by simply copy pasting it into a PowerShell session. The script extension ends in myscript.ps1
  • It should be noted that the QlikSense Script to generate the CSV file is not required. It can be manually created if the user wants to (myself for instance).
  • Regarding admins to have access to all/several sheets. A better solution than making them 'Root Admin' is to add an admin to all the custom properties thus accessing all the sheets. For instance, if you have three groups with four sheet exceptions then the admin will have all four sheet exception properties to their name, thus giving them access to all the sheets. The reason this a better solution is because 'Root Admin' has access to everything (Read,publish,script,etc).

Thank you again Erik,

0 Likes
Partner
Partner

Thanks for the feedback, I'll look into it!

0 Likes
Partner
Partner

Published a new version now!

Version history
Revision #:
1 of 1
Last update:
‎2018-07-04 05:05 AM
Updated by: