Sheets Security with Section Access

[Carlos_Reyes]

Aside from filtering data records with Section Access, I'm usually requested to filter the objects and sheets to which users have access.  To be honest, I don't like to filter individual objects since this implies a lot of mainteinance and a more complex Section Access table. So I usually describe the scenario to the IT people and Key users and they always ( so far ) decide to go for the Sheets-Security option. Which in my opinion is the best alternative because it's more common to hide a complete block of information/analysis from a user than just a single KPI.

I've seen some examples like this before in the Community, however everything I've found its exemplified based on the USERID field. So, when I tried to use those examples I ran into some common errors when dealing with Section Access, and now that I've solved them I want to share a small example about how to go when using NTNAME.

Also, I want to note that there are some easier/faster alternatives to this approach. For instance you may use the MATCH() method described in this thread: Secured sheets

However if you're already using Section Access to filter data, you have too many users and/or sheets to use the MATCH() method, or if you just need to handle those privileges outside of the .qvw ( e.g. a user will maintain an excel file), you can use/reuse Section Access to accomplish this requirement.

In my example I use INLINE loads in order to allow other people to reload the app, however you could load all tables, including the SECTION ACCESS table, from xls or qvd files.

So, in order to test this example you have to do the following:

1. Download the enclosed file
2. In the script, go to the Section Access tab and uncomment the disabled code.
3. In the same tab, change the NTNAME and USER field values in order to reflect your local/domain users.
4. Go to Document Properties/ Opening and enable Initial Data Reduction Based on Section Access. Do not enable "Strict Exclusion"
5. Save and reload the app.
   • Note. If you have and use Publisher you'll need to add the account that runs QlikView services as an admin profile in the Section Access table before you reload and distribute the app.
6. If you only use the Desktop, save and exit any QlikView instance and reopen the application. If you want to check all profiles you'll need to sign out of Windows and sign in as the profile's user. You'll probably have to copy the file into a usb in order to open it in all the Windows accounts.
7. Depending on the user you used to sign in you should get the following results:
   • If you signed in as the admin user (first record in Section Access table), you should be able to see both sheets (A and B) and all Countries and Cities.
   • If you signed in as the first user (second record in Section Access table), you should only see sheet A but all Countries and Cities.
   • If you signed in as the last user (third record in Section Access table), you should only see sheet B and cities from MEXICO and USA.

Go to the following link to get the example file:

Sheets Security with Section Access File

I hope this example is useful and don't hesitate to ask if you have any doubt regarding it.

[brennanbd]

This worked flawlessly!  Thank you!

[Carlos_Reyes]

Hi @juampe

I know it was a long time ago when you needed a solution but I think I've just run into the same difficult scenario in which you were describing how when after running a publisher task, all users would get admin access level. The solution for that is using the star value (*), in the section access table, for the admin records that need to get access to all possible values. In my example, it would be:

ACCESS, NTNAME, USER, COUNTRY
ADMIN, EVOLCON-CR\CARLOSREYES, *,*
USER, EVOLCON-CR\PRUEBA1, PRUEBA1, *
USER, EVOLCON-CR\PRUEBA2, PRUEBA2, MEXICO
USER, EVOLCON-CR\PRUEBA2, PRUEBA2, USA

It seems that on the server side, the user account that runs the Qlik Server services needs to have a profile, in the section access table, that contains all possible values, or have the star (*) value, which means the same. Otherwise, the task is run and distributed an all users get the same 'profile' as the user that runs the Qlik Server services.

One note, we can only use the star (*) value, when the option "Strict Exclusion" is not marked in the Document Properties. If you mark the "Strict Exclusion" option, each user must have a record per distinct value in the filter(s) fields.

You've probably found this solution a long time ago, but now that I've dealt with it too, I thought it would be useful to add it to the post and for you in the remote case you still needed it.

Regards.

[juampe]

Thanks @Carlos_Reyes I would try it !

[jakobjosef]

wow this was exactly what I was looking for, thank you very much!