Qlik Community

Qlik Sense Documents

Documents about Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER

Dynamic Sheet Exception With Stream and App Level Security

Specialist III
Specialist III

Dynamic Sheet Exception With Stream and App Level Security

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:

  1. 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.

StreamUserCustomProperty.PNG

    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 Stream and App.png

    Apply custom property to jarvis user too

AppluCustomPropertyTojarvis.png

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 (

  1. 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.

ASIA Sheet Settings.png

     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

SheetExecptionCustomPropety.PNG

    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.

Output File.png


    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:SheetVisibleToQlik_Sense.png

Sheets which are visible to Jarvis:

Sheetswhich is visble to jarvis.png

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.

Attachments
Comments
Partner
Partner

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

Specialist III
Specialist III

Hi Erik,

I created this new document with two objectives, first, to make it more simple, like many users don't know how to use power shell, execute script in power-shell, Qlik CLI and especially stream and apps security too. What I noticed later if you have groups in AD then this will be completely dynamic but clients often don't have groups in AD and if you ask them to make groups then they deny it and say do whatever is possible in Qlik because there network team don't wana change anything in their AD, then we write security rules in Qlik which we say static one or mine solution.

If we don't have users in AD then every time we add user in your app, execute it, generate the csv file and use it for security or manually update the csv sheet for new user.

I have started recommending your solution to Qlik users and help them to implement it though I ask them to first read mine once, then you will understand its short fall or problem in write security rules or updating security rules and afterwards understand the value of Erik's solution. Its like mine one is first step and your one is next.

I hope, I would help you if any. Please reach to me anytime.

Thanks,

Rohit 

0 Likes
Creator II
Creator II

Thank you Rohit,

Is Qlik_CLI is apporved by Qlik or is it 3rd party extension ?

Br,

Clark

0 Likes
Partner
Partner

It is not an official extension, it is 3rd party. Although the developer itself works at Qlik...

https://github.com/ahaydon/Qlik-Cli

Contributor
Contributor

Thanks for the sharing. After this implementation, rootAdmin cannot see the Apps in Monitoring App Stream. May I know how to resolve it?

0 Likes
Contributor II
Contributor II

If you came to that post, please consider promoting the Sheet Show/Hide feature idea for Qlik R&D to know about your interest into the feature. 

https://community.qlik.com/t5/Ideas/Sheet-Show-Hide/idc-p/1694119#M1786

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2018-07-26 01:24 PM
Updated by: