Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW

Dynamic Sheet Exception With Stream and App Level Security

No ratings
cancel
Showing results for 
Search instead for 
Did you mean: 
rohitk1609
Master
Master

Dynamic Sheet Exception With Stream and App Level Security

Attachments

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.

Comments
ergustafsson
Partner - Specialist
Partner - Specialist

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

rohitk1609
Master
Master

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
millan123
Creator II
Creator II

Thank you Rohit,

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

Br,

Clark

0 Likes
ergustafsson
Partner - Specialist
Partner - Specialist

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

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

ADJHKCOM
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
frigoar1
Contributor III
Contributor III

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

ilyas393
Creator
Creator

Hello Rohit

This is a very helpful article, Thank you for putting it together.

I have an excel file that gets updated from a ServiceNow form that includes User ID's and App Name.

And I have these App Names as the Custom Properties as well.

How can I update the Users in QMC with the Custom Property that gets added in the excel file? What would be the CLI/PowerShell code/syntax?

A user can have more than one custom property.

Thank you for your help.

 

 

 

0 Likes
Contributors
Version history
Last update:
‎2022-02-11 03:49 AM
Updated by:
Anonymous