row level security, how to?

[#1]

How to implement a "row level security"?

I'm testing Qlik Sense for the first time and the first critical point is the security.

so how to implement some sort of row level security to display only the authorized data to the users.

Like sharing a store dashboard where each user can only see his store data.

I'm using the direct discovery feature, but I'm not able to find how to send the current user name in my queries.

in my database I have a table listing which store a user can access.

so how to do this?

the help site has very limited description about the data security.

thanks.

[Miguel_Angel_Baeyens]

Hi Jerome,

Qlik Sense can use Section Access too, besides security rules, to make sure that you have row level security in place.

There is more info here: https://help.qlik.com/sense/en-us/online/#../Subsystems/Hub/Content/Scripting/Security/ManagingSecur...

Miguel

[JonnyPoole · Accepted solution]

Jerome

There are a number of things to keep in mind when setting up Section Access for row level security.  The following provides additional tips not found in help.qlik.com.  Its meant to go over row level security in more detail. it doesn't talk about column level security . hope it helps.

1. The section statements should go in the load editor before the data load segments. In the sample below, I load the Section Access table before the rest of the data model.

2. The Section Access table requires an ACCESS field and a USERID or GROUP field.  Under USERID, you will use the userid that the Qlik Sense hub uses to track all users . For default security, its likely to be in a DOMAIN\USERID format.  I suggest entering the USERID or GROUP reference in all caps as you see below

3. To do row level security, add 1 more other fields to the Section access table. The field names should be capitalized as should the data values in those field(s). You don't need to alter your source data, just alias the field name in the section access table to provide a capitalized field name and use the upper() function to put the values in all caps as well

ex:

section access;

LOAD

      'USER' as ACCESS,

      USERID,

      upper(Department) as DEPARTMENT,

     upper([Cost center id]) as [COST CENTER ID]

FROM ...;

4.  In the rest of your data model you must also have the same all caps field names and data values (see below).  The way it works is that when a userid logs in, it will  act as if the whole app was filtered based on the records with the field values (or combination of field values if you have multiple fields)  referenced in the section access table for that userID. The associative indexing is intact for this action so that all linked tables will likely be filtered based on those same field values. 

LOAD

      upper(Department) as DEPARTMENT,

      upper([Cost center id]) as [COST CENTER ID],

Department,

[Cost center id],

Sales

FROM ...

5. You CAN put an asterix '*' for a field value entry in the section access table if you want that user to see all values.

6.  Section Access only works on Qlik Sense Server.  An app with a valid section access table in the load editor must be on the server AND reloaded on the server for the security to take effect. To test different users access, you must publish the app to a stream so that you can test logging in as different users to see the effect. Without publishing the app is stuck in the 'my work' of one user and not accessible by other users.

7. ALWAYS create a backup or duplicate of the app prior to adding any section access statements to the load editor.

[#4]

thanks for the information appear to solve most of my challenge

what's the impact if I want to give access to aggregated information for the district level (sum of all stores in the district) of my stores, but only the authorized store(s) when only the stores are requested?

does this security model will works too?

[JonnyPoole]

I think you could do that but i'm not sure it could be done with Section Access, but perhaps just with modeling techniques and UI expressions.  The only issue is that by deviating from section access, the security is no longer pervasive and you need to give thought to whether the users would ever interact with the data model (self service) or just pre-created dashboard widgets and pre-created expressions. My gut tells me the latter would be necessary.

Basically you add a table to the data model that ties users to stores.

Then in the UI expressions tie that table into the expression in some fashion. 

Store level calc:  sum (  if( osuser()=User,Sales) )      ...where user is the field that stores the userID in the table

District level calc:   sum (Sales)

[Anonymous]

5. You CAN put an asterix '*' for a field value entry in the section access table if you want that user to see all values.

Is it possible to set the user to see nothing in that column but not filter out other value?

[Miguel_Angel_Baeyens]

Louis,

If you mean that user should not see any value in that column, I would suggest to take a look at OMIT keyword for Section Access, however, this could also affect the results in the charts if that column is used in them (for example: total sales, amount, etc.)

https://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/Scripting/Security/dynamic-data-...

The link is from help for QlikView 12, but it also applies to QlikView 11

Miguel