Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 happens when 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:
- A sheet is given a specific name, e.g. SheetException_001_Goeteborg
- 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".
- 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.
- 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.
- 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.
- 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.
- 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)
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
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!
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.
