One of the things that is perhaps most key to anything involving data is security. It is fundamental in QlikView, and Section Access is one of the core components of that security model.
For the uninitiated; Section Access allows you to apply an ‘initial selection’ based on a user account that the user can then not clear or see beyond. This effectively locks your app so users can only see the data they are supposed to. Much has been written on Section Access and you can find lots of information on setting it up – but what I want to look at here is one of the pitfalls with Section Access.
The worst thing that can happen with Section Access is that you can lock yourself, and everybody else, out. Right out.
Section Access applies security at a row and then a document level. This, in a worst case scenario, could mean you don’t have the ability to get back in to your document to access your scripts and visualizations.
Lock outs can occur from a coding error when applying Section Access – or can catch you off guard by occurring due to a data load error. To explain further; if a user can not access any data they can not access the document. If the document contains no data then no one can access it. Most reload failures will cause the load to stop and the document will be left in its prior state (with the old data intact) – but if a table in the load is present but empty this may not happen.
Here are some tips to avoid lock out happening.
The first is simple: back up. Always do this before any Section Access change – but it is good to back up regularly also. Saving a copy of your document without Section Access enabled is the sure-fire way of ensuring you are not locked out. Just make very sure this is secured by your network security and is not put anywhere near your Access Point.
Have a back door login. Typically Section Access will be on domain accounts, ideally at a specified domain SID. A change to user accounts or domain could lock all users out. By adding a user name and password based account also you can gain access should this happen. Obviously this username and password should be secured as per your info-sec policy on other admin passwords (often these are locked in safes until required).
Have some Section Access accounts hard coded in your QlikView load script. Generally accounts are loaded from a database table or flat file. If data from this source is cleared down then no accounts will be loaded – in-line loaded accounts will cause this to not spell disaster.
Test well before closing your document. This simple tip could save you a lot of messing around. When amending Section Access; make your changes, reload and save. The temptation now is to close QlikView and re-open your document to test – don’t do this! After saving your document open a new instance of QlikView and test the document by opening it in the new instance. If you are unable to open the document you still have it open in another window to change the security and try again.
Hopefully these simple tips will save you from pain, expense and tricky explanations to bosses.
As I said at the start there is much written on setting up Section Access – so I don’t want to repeat that here. Just to mention though that any changes to Section Access script or data will require a reload, and setting the options on the Opening tab of the document properties tab are critical.
Also, even with Section Access set up correctly on your document you don’t want someone taking it off site. Make sure you secure all files with Active Directory permissions on the server. This is particularly important with QVD files – that have no other security around them.
Finally, at risk of stating the obvious, security and access restrictions are perhaps the most important things to consider with your QlikView implementation. Make sure you give them the consideration they deserve and always test thoroughly.
Hi Steve,
Good points here, in fact, I think QlikTech should take this and put it as page 1 in all of their manuals!
Thanks,
Chris
Thanks Steve. The article is very nicely written and covers all the points that should be taken care of while implementing section access. Must read for QV developers.
No worries. Glad you found the article useful.
Very useful article Steve !!
HI Steve,
I am new user of Qlikview, I have a small doubt in section Access i.e.,
when we are going for inline section access we generally create a inline load table with basic fields like ACCESS, USERID and PASSWORD.
My doubt is how a developer knows other users usernames , if at all he knows the usernames by some database, how can he get the passwords.
If the two are done some how after reload, the inline table is shown in table viewer and a user can know other users usernames and passwords.
PLZ kindly go through my doubt.
Thank You
Hi Kiran,
The issue is that any password used for Section Access needs to be passed in in plain text. Often these are stored in a spreadsheet (rather than hardcoded in the load script), but they are still visible to the developer. A better bet is always to use Active Directory security using the NTNAME column in Section Access. You can mix NTNAME and USERID types of security in a single table – simply place * in the columns you don’t want to restrict on.
Steve
Thank you steve for ur immediate response,
I have a small requirement in set analysis
consider a chart a showing the sum of sales based on country dimension and there are at most four countries.
Can we design a report such that based on location(locale) they are opening the chart should show the countries sum of sales with out using set analysis. one of my friend argued that we can do it in tableau from server, is there any solution in qlikview from desktop or any function or from server, cmc side.
I’m not sure how you would pick up the users locale, but if you built an intersection table between the user and the country they were interested in then set analysis would do this fine. If you really didn’t want to use Set Analysis you could use an IF statement – but I don’t see why you would do that.
Hi All.. I am very new developer in Qlikview. In my scenario, before my joining the company a colleague has left the company and left no trace of a dashboard which has section access.
Is there any way where I can open the dashboard?
I want to modify the script in edit script.
This could be a bad situation. Do you have the ability to log in as the departed colleague? You may find you can open the document and edit the script that way. Be careful if this means changing his password in AD, as this itself may break things. If the application is loading at a beat then it is probably loading users from a database or spreadsheet – if you can add yourself as an Admin to that user list you may unlock it for yourself.
If you are properly stuck it is probably worth trying to find a QlikView consultant who can try and reverse engineer their way into the document, but it can be problematic.
Another thing to look at is creating a Project folder, and extracting the source of the app that way. You may then be able to rebuild the application with new Section Access.
Good luck.
Hi,
With regards to the quote you mentioned
“Have some Section Access accounts hard coded in your QlikView load script”
What would I need to change with my current script as per below? Would I have to use the Concatenate Function to hardcode the ADMIN/Service account User?
SECTION ACCESS;
Security:
LOAD UPPER(Access) as [ACCESS],
UPPER(NtName) as NTNAME,
UPPER(Level) as LEVEL;
SQL SELECT *
FROM
SecurityArea.dbo.dimQVAccess;
SECTION APPLICATION;
STAR IS *;
Reduction:
LOAD UPPER(Level) as LEVEL,
UPPER(Db) as DB;
SQL SELECT *
FROM
SecurityArea.dbo.dimQVLevel;
Thanks
Hi John,
Yes, but you do not need the CONCATENATE, as the tables will auto concatenate if the fields are the same, simply add the admin account at the top of the section access:
SECTION ACCESS;
Security:
LOAD
‘ADMIN’ as [ACCESS],
‘ADMIN\Service’ as NTNAME,
‘*’ as LEVEL
AUTOGENERATE(1)
;
Alternatively you could use an INLINE load, which will make it easier to have more than one hard-coded account.
Hope that helps,
Steve
Not sure about this but just want to throw out this idea:
Let’s say a user is locked out of QVW file because of Section Access. The sample Section Access security was like this:
ACCESS,SECURITY,USER_AD_NAME
ADMIN,*,COMPDOMAIN\USER1
ADMIN,*,COMPDOMAIN\USER2
But you forgot to add your user name as the ADMIN, then you can either try to login as either USER1 or USER2, install QlikView and open the file and edit the script and add your username as ADMIN.
Or alternately, install a Windows Server on a stand alone machine and keep the name of this machine as COMPDOMAIN. Create a user on this machine called USER1 and login with this user. Install QlikView and the license. Then just try to open this file on this computer. This should work.
Thanks.
Hi Sohail, you are correct, if someone with domain admin rights wanted to get into a QlikView app with Section Access then they could change the password on a user they knew to have access and then use that account to open the app. This could perhaps be mitigated against by having a password attached to each user as well. The approach of setting up a new domain or machine with a matching using name is also an approach. This can be prevented by adding the Domain SID to the Section Access also – this is a unique key attached to each domain and it would be different if a new domain was set up with the same name.
Hi all I need help on Qliksense section access
Hi. Section Access is similar in Qlik Sense, but there are some important differences, particularly how users are referred to. The best place to find assistance would be the Qlik Community.