As new features are introducing in Qlik sense rapidly, this document will help you to do dynamic sheet level security with stream, application level security too which has supported by Qlik Feb 2018 or afterwards version of Qlik.
Last year I wrote following document for security in Qlik sense Sheet or App Object Level Security Qlik Sense which helped users to implement security at different levels of Qlik Sense. Here, level means streams=> applications => sheets.
Recently erikadvectas create a solution document of Dynamic Sheet Exception which is a fabulous document would guide you to implement sheet security with default security settings, default means when you create a stream it automatically creates a security rule of stream template and gives user access to all published app objects.
Qlik has its own default security settings where if any user has access to any stream then he or she can view all the published applications and corresponding app objects into it but what if you want to go in a different way than default and you want to customize things as per your need i.e. what if one stream has 5 application and you have two set of users. Your requirement is first set of user will see first two application and another set will see rest three but not first two.
To satisfy these kind of use cases we can follow Sheet or App Object Level Security Qlik Sense
Moving forward, let’s consider one sample case, if I have two users, one is JARVIS and other is Qlik_Sense and today Jarvis belongs to ASIA group and Qlik_Sense belongs to NON-ASIA group. With the help of above document you would configure your Qlik site as Jarvis can see only that sheet in a particular application which is designed for ASIA user and Qlik_Sense can see the sheet for NON-ASIA USER. Now problem starts when tomorrow Jarvis and Qlik_Sense switch their groups by each other or new user will come to these groups and they are moving here and there and we need to update our security rules or updating custom properties settings every time for concern users in QMC ?
To handle above problem Erik has written a very eminent document Dynamic Sheet Exception. One Important thing, if there is no requirement of application or stream level security then for Dynamic Sheet security Erik’s document is best fit to it.
Please go through to Dynamic Sheet Exception to understand Dynamic Sheet Exception in detail then try to follow below steps to implement security for each and every level of Qlik Sense.
But only thing is different in this document that it covers stream and application level security too with sheet.
To understand below points in detail from 1 to 6 please read points from 1 to 9 from Sheet or App Object Level Security Qlik Sense
Below are steps you need to follow:
- Go to security tab and disable default STREAM rule, This stream says The user should see the resource if he/she has read access to the stream it is published to means if any user or group of user has READ access to the stream then user will see all the Application and AppObjects(All resources) so if we try to restrict any user to any particular application or App Object ,it will always let user to see everything which comes in the stream. So by disabling it we are taking all the access from user to see anything in stream by default.
Note: Disabling STREAM default security rule make all default stream rules ineffective which were created automatically on the time of creation of stream likewise Security rule for access to "name of stream".
2.Go to custom property and create a new custom property StreamUser with stream and users as resource and add values to it. For this exercise I am adding Non-Dev. You can add more value at your end to write different rule focusing on ACTIONS for different set of users.
3.Custom property AppsUser with Apps and Users resources and add values to it, I am adding Non-Dev for this exercise.
4. Create a new security rule with stream template as with READ action
((user.@StreamUser="Non-Dev" or resource.@StreamUser="Non-Dev"))
5.Create another security rule of app template with READ action
((user.@Apps="Non-Dev" and resource.@Apps="Non-Dev"))
Note: I am not considering actions here especially while create security rule because my objective is provide stream and app access to user not how any user will consume the apps.
. 6.Apply custom property to stream, user and application : My sample stream name is Analysis
Apply custom property to jarvis user too
If both user can view the application named Publish App (sample name taken for this exercise) then we can proceed further.
7.create a new security rule of App Object access as
((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")
Note: above security rule is exact copy of point 4 under Dynamic Sheet Exception. You can read this document in detail to understand its purpose. This rule has same purpose in point 10 Sheet or App Object Level Security Qlik Sense. Focus on the line highlighted in red, it is saying a user can view all the app objects except "SheetException_* which will add in sheet title to hide the sheet from those users who do not have such string at the place of * in above highlighted in red .
8.Open the sample app and edit its sheet title as:
SheetException_6ba4bbbf-e833-4a07-98ab-d21cba574585_ASIA. Text after 3rd “_” will be matched under above highlighted code in red.
Same manner add SheetException_6ba4bbbf-e833-4a07-98ab-d21cba574585_NON-ASIA into Sheet Title of other sheet so we can do QA for each sheet for different user. 6ba4bbbf-e833-4a07-98ab-d21cba574585 is nothing but app ID. You can use anything. I am taking it so I can know in which app we are trying to hide the sheets.
9.Create a custom property with name SheetException and no need to add any value to it because we will assign value to this custom property by POWERSHELL
10.Open the app An app with SheetException – SAMPLE (same is attached in document)and update the code in Userdata demo tab as add qlik_sense to ASIA group and Jarvis to NON-ASIA.
11.SheetException tab in same app. I am attaching app with updated value for my exercise when you will use this app then you have to update it.
I am using app Id for variable vL_SheetExceptionId so the output file have same app id as name.
12. Load the app and check it is saved in store path properly.
13.Download https://github.com/ahaydon/Qlik-Cli Qlik ClI and save it to any folder. Open POWERSHELL DESKTOP App with admin rights and use CD command to navigate to the same folder where you saved Qlik CLI as CD “Path of folder”.
Note: Try not to have any space in path or folder name if there is write to wrap it by “” between double quotes.
14.Execute below commands in powershell for Qlik_CLI installation
Run following command Get-PackageProvider -Name NuGet -ForceBootstrap
Install-Module Qlik-Cli
16.Open the .ps1 file UpdateSheetException attached in document with text editor and update three things in to it:
#address we use with Qlik-Cli
$serveraddress = "HostNameofQlikSite"
#defines our full path
$csvpath = "FolderPathwhereTheOutputFileFromAn app with SheetException - SAMPLEIsSaved\*.csv"
17. Execute the powershell file as .\ UpdateSheetException
18. Go QMC and check user Jarvis and qlik_sense assigned which value in custom property
19. Check which custom property value of DYNAMIC SHEET has been assigned to both users.
20.Open the file and your will see qlik_sense can see:
Sheets which are visible to Jarvis:
21. Please use following link to schedule a window task for Poweshell Configuring a PowerShell Script to Run as a Scheduled Task
Reach to me if there is need of any clarification or need assistance with kumar.rohit1609@gmail.com
Follow me on Qlik Community as https://community.qlik.com/reputation.jspa?username=rohitk1609 for more Qlik or BI related important documents. Following my profile here or LinkedIn https://in.linkedin.com/pub/rohit-kumar/2b/a15/67b,
If there is need of chat to discuss any problem follow me here which enable me to initiate the chat option where I can understand your use case better and provide right solution.
Please add your Ratings, Suggestions, Compliments and questions which make us know how my document is helping you and by that we can improve the quality of the document.
Nice one! Appreciate the credit. We usually also implement stream and app level security based on AD groups (e.g. BI_STREAM_IT and BI_APP_EXPENSES) which is added within custom properties when they are named. Corresponding values between the AD group names and the property equals access, just as you mention. Section Access is though dynamic in a hierarchy form.
My article for dynamic sheets was the "missing" part, everything else was covered already 
