article Sheets Security with Section Access in Member Articles

Sheets Security with Section Access

Carlos_Reyes — Fri, 07 Oct 2022 13:24:47 GMT

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:

Download the enclosed file
In the script, go to the Section Access tab and uncomment the disabled code.
In the same tab, change the NTNAME and USER field values in order to reflect your local/domain users.
Go to Document Properties/ Opening and enable Initial Data Reduction Based on Section Access. Do not enable "Strict Exclusion"
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.
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.
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.

Re: Sheets Security with Section Access

bullish35 — Thu, 31 Oct 2013 01:42:07 GMT

I have a valid license but can not open the attached .qvw. Can you re-post? Thank you.

Re: Sheets Security with Section Access

Carlos_Reyes — Thu, 31 Oct 2013 01:54:25 GMT

Hi Ellen,

I just realized that I had uploaded a file with Section Access enabled... sorry!

But now you should be able to open the file.

Hope it's useful.

Re: Sheets Security with Section Access

bullish35 — Thu, 31 Oct 2013 02:57:10 GMT

Thank you Carlos!

Re: Sheets Security with Section Access

bullish35 — Thu, 31 Oct 2013 07:28:52 GMT

Hi Carlos,

I have section access working locally. That is, when I open my qvw and enter a particular userid/password, the correct, reduced data is present in the document tables. However, all sheets are always visible. I'm using the following expression for conditional display of the tabs: vShowTab6='Y' and sum(SHEET6)>=1

The first part of the expression, vShowTab6='Y', has been working for quite a while for other purposes. Since adding the piece for section access, and sum(SHEET6)>=1, neither is working. Is there anything else I should be looking at? Do you have any idea what I may be doing wrong?

Thank you!

Re: Sheets Security with Section Access

Carlos_Reyes — Thu, 31 Oct 2013 15:36:41 GMT

Hi Ellen,

Mmmm, its difficult to say just by the description, haha, it sounds like anything could be happenning, but I suggest you review the following things:

Are you loading the sheets access table? Sounds obvious but it's very common to forget to do that.
Is your Section Access table linking to your Sheets Access table? I know it's a dumb question but make sure you're using the same field both in the SA table and the Sheets Access table.
In Section Access all values are automatically converted to capitals... perhaps the values in your Sheet Access table are not in that format.
Are you using the correct Sheet Access profile in the Section Access table for the user you're testing? Again... sounds obvious but It happens to me all the time that I'm using the incorrect profile and therefore I get the incorrect sheets enabled.
If you're testing several users with the Desktop client, remember that you must logout of the Windows session and log in in the correct user session in order to test ever user results. That... if you're using NTNAME.
Did you enable the "Data Reduction Based on Section Access" in the Document Properties\Opening tab?
Did you reload, save and close the QlikView instance before opening the application in order to see the results?

Other than that, I think it'll be easier if you share a sample of your code or even a sample app.

Regards.

Re: Sheets Security with Section Access

bullish35 — Sat, 02 Nov 2013 21:59:24 GMT

Thank you for your response, Carlos. Actually, I found a #8 item to forget - disable the Settings, Documents, Security, Show all sheets and objects setting! I had been using the CTRL-SHFT-S command during development for testing some conditional show functionality and hadn't disabled the show all feature.

Re: Sheets Security with Section Access

Carlos_Reyes — Mon, 04 Nov 2013 15:30:21 GMT

Wow... I didn't know about that setting. I've never used it before but it's definitely something to watch out for. So, have you solved your problems? I hope my template was useful.

Re: Sheets Security with Section Access

bullish35 — Wed, 06 Nov 2013 23:40:26 GMT

Yes, and yes. Thank you!

Re: Sheets Security with Section Access

juampe — Wed, 23 May 2018 14:03:43 GMT

Hi carlos.reyes.qv‌

Thanks for sharing us this info.

I have the following problem,
Once the QlikServer reload, it creates a qvw file to distribuite the information to all the users, so in that qvw file the Sheet level Acccess doesnt work because all user are "ADMIN". I cant understand why the distribuite document has this configuration because the original document still has the good one and if I try to open it, works

In this first pciture there are all the projects and inside mine (47. Nivel de Servicio,,,,) there is the qvw file that if I try to open locallly (no throuh website) works perfectly the Sheet level Access

In the second Picture we see the qvw file that the Qlik Server creates to distribute via Website and in that qvw file doesnt work the sheet level Access

Any thoughts?? I am totally lost ;(

Re: Sheets Security with Section Access

Carlos_Reyes — Wed, 23 May 2018 17:26:54 GMT

Mmm, it doesn't make sense that the users profiles are "changed" due to a distribution task on the server... I've never seen that kind of behavior. As far as I've seen, the distribution task on the server will respect the user profiles as they are or exist in the Section Access table. The only thing you should concern with is to add the user that runs the Qlik Server services as an additional (ADMIN) user profile in the Section Access table, so the Server can open the file and perform the reload task... and... adding all the users you want to give access to in the distribution task user's list.

Re: Sheets Security with Section Access

brennanbd — Wed, 06 Jun 2018 20:00:53 GMT

This worked flawlessly! Thank you!

Re: Sheets Security with Section Access

Carlos_Reyes — Sun, 02 Dec 2018 02:18:26 GMT

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.

Re: Sheets Security with Section Access

juampe — Thu, 20 Dec 2018 10:19:48 GMT

Thanks @Carlos_Reyes I would try it !

Re: Sheets Security with Section Access

jakobjosef — Tue, 08 Oct 2024 14:01:33 GMT

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