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.

[bullish35]

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

[Carlos_Reyes]

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.

[bullish35]

Thank you Carlos!

[bullish35]

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!

[Carlos_Reyes]

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:

1. Are you loading the sheets access table? Sounds obvious but it's very common to forget to do that.
2. 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.
3. In Section Access all values are automatically converted to capitals... perhaps the values in your Sheet Access table are not in that format.
4. 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.
5. 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.
6. Did you enable the "Data Reduction Based on Section Access" in the Document Properties\Opening tab?
7. 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.

[bullish35]

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.

[Carlos_Reyes]

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.

[bullish35]

Yes, and yes. Thank you!

[juampe]

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 ;(

[Carlos_Reyes]

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.