Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

A Primer on Section Access

 

Section Access is a QlikView feature that is used to control the security of an application. It is basically a part of the load script where you can define an authorization table, i.e. a table where you define who gets to see what. QlikView uses this information to reduce data to the appropriate scope when the user opens the application.

This function is sometimes referred to as Dynamic Data Reduction, as opposed to the loop-and-reduce of the Publisher, which is referred to as Static Data Reduction.

Data Model.png

 

For example, above you have the authorization table in Section Access to the left, linking to the field COUNTRY. (In a real application, the authorization table is not visible in the data model.) This means that when a user opens the application, QlikView uses the user name (NTNAME) to establish which countries this user is allowed to see, and then makes the corresponding selection in the Customers table.

The selection propagates to all the other tables in the standard QlikView manner, so that the appropriate records in all tables are excluded, whereupon QlikView reduces the scope for this user to only the possible records. This way, the user will only see data pertaining to the countries to which he is associated.

Selection.png

 

A good way to debug your Section Access is to temporarily remove the Section Access statement and run the script. The authorization table will then be visible in the data model and you can make selections in NTNAME.

Within Section Access you should define at least three fields: ACCESS, NTNAME and a third reducing field that links the authorization table with the real data. You may have additional fields also, like user roles or departments.

Some points around Section Access:

  • All fields in Section Access must be upper case. Hence, the reducing field must be in upper case also in the data. Use the Upper() function and name the fields in upper case.
  • Don’t use the fields USERID and PASSWORD, unless it is for testing or debugging. Proper authentication is achieved through NTNAME.
  • NTNAME is the field used to match an authenticated user – also if you set up ticketing using other authentication mechanisms than Windows integrated security.
  • NTNAME may contain names of groups as well as individual users.
  • Make sure "Initial Data Reduction..." and "Strict Exclusion" are checked (Document properties - Opening). If the field value of the reducing field in Section Access doesn't exist in the real data, the will be no data reduction unless Strict Exclusion is used.
  • If your users work off-line, i.e. download the physical qvw file, the security offered by Section Access has limited value: It does keep honest people honest, but it will not prevent a malicious user from seeing data which he shouldn't have access to, since the file is not encrypted. So for off-line usage I instead recommend the static data reduction offered by the Publisher, so that no files contain data the user isn't allowed to see.
  • In most of our examples, an inline Load is used in Section Access. This is of course not a good place to keep an authorization table. Store it in a database and load it using a SELECT statement instead!

And finally

  • Always save a backup copy when making changes to Section Access. It is easy to lock yourself out...

Section Access is a good, manageable and flexible way of allowing different access scopes within one document. And when used on a server, it is a secure authorization method.

HIC

 

Further reading related to this topic:

Data Reduction – Yes, but How?

Data Reduction Using Multiple Fields

Tips and tricks for section access in Qlik Sense (2.0+)

41 Comments
MVP & Luminary
MVP & Luminary

Hi Henric,

Thanks for this - nice to see a very simple introduction to what Section Access is and can do.  Glad to see you mentioned the perils of locking yourself out - I dedicated a whole blog post to this subject a while back:

http://www.quickintelligence.co.uk/help-ive-locked-myself-out/

Steve

6,787 Views
kirankkk
Contributor II

Thanks Henric for simple and help full article.

Could you please elaborate at offline mode how security breach can be there ?

What I  understand if section access information is coming from database or from inline table which mostly we can put in hidden script  so how can it can break?

Thanks & Regards

Kiran Kokade.

0 Likes
6,787 Views

The file isn't encrypted, so of course it is possible to get to the hidden data - one way or another. That's why I think you should use static data reduction when the files go off-line, so that there is no hidden data. Or keep the files on a server, so that no unauthorized data leaves the server.

HIC

0 Likes
6,787 Views
kirankkk
Contributor II

Is there any alternative for publisher for static reduction?

Thanks & Regards

Kiran Kokade.

0 Likes
6,787 Views
sudeepkm
Valued Contributor III

Hi Henric,

Thanks a lot for a nice article. I've a quick question related to the below given statement.

"If the field value of the reducing field in Section Access doesn't exist in the real data, the will be no data reduction unless Strict Exclusion is used."

Assume that the field for data reduction is "REGION" and there is a value as "CA" present in the Section Access table however the value "CA" does not exist in the real data table.

1. When the Strict Exclusion is enable

    Will the end user be able to open the QVW itself ?

2. The Strict Exclusion is disable

     Will the end user be able to open the QVW itself ? if yes then will he be able to see all the values of field Region?

Thanks and Regards,

Sudeep

0 Likes
6,787 Views
MVP & Luminary
MVP & Luminary

Hi Kiran,

I have used a driver file with the ID's of the allowed records in, which is then loaded first and used as a WHERE EXISTS for the rest of the data.  The same empty QVW is then copied into a number of folders, each with a different file of ID's in it, and then each QVW is reloaded - picking up the correct data.  This is then all tied together with a batch file to do the copy and the reloads.  Windows Scheduler can then be used to fire the .bat file.

This works okay, but Publisher is the far better way of achieving this - from a logging and resilience point of view.

Steve

6,787 Views
kirankkk
Contributor II

Thanks Steve for solution.

I would more appreciate if you load small demo example.

Thanks & Regards,

Kiran Kokade

0 Likes
6,787 Views

@ Kiran Kokade

In addition to Steve's solution: You could also create a VB macro that does this. But just as Steve says: The Publisher is a far better solution.

@ Sudeep Mahapatra

Q1: Strict Exclusion On: If the user is allowed to see 'CA' only, and this does not exist in the data, then QlikView will not allow access to the file.

Q2: Strict Exclusion Off: If the user is allowed to see 'CA' only, QlikView will try to make this selection, but since the selection fails, there will be no reduction of the data when QlikView allows access to the file. The user will see everything.

HIC

0 Likes
6,787 Views
sudeepkm
Valued Contributor III

Thanks a lot Henric.

0 Likes
6,787 Views
MVP & Luminary
MVP & Luminary

Hi Kiran - please get in touch directly (via the website at http://www.quickintelligence.co.uk/ or on Community) and I can send you something.  I may do a blog post on this technique in the near future...

0 Likes
6,787 Views
kirankkk
Contributor II

Hi Henric,

My concern is Publisher will cost me extra licenses.

I would prefer solution you have mentioned. 

If you can share any demo example would really appriciate

Thanks & Regards

Kiran Kokade

0 Likes
6,787 Views
kirankkk
Contributor II

Thanks Steve.

0 Likes
6,787 Views
Partner
Partner

Thanks HIC, can you confirm for me that all the data of the document is loaded into memory and then some hidden? ie. the amount of memory needed to load the document is still the same as if there was no data reduction?

0 Likes
6,787 Views

On the server, all data is loaded into memory and some data is hidden. So you need memory for the complete document.

On the desktop, data is removed from the memory when you open the document. But you will still need memory enough to load the entire document, since QlikView needs to load the file before the reduction is made.

HIC

6,787 Views
Partner
Partner

Hi Henric,

What you expained about Strict Exclusion On is only true for "User" ACCESS type. For "Admin" ACCESS type, the user will see the unreducted data set if there are no matches.

Regards

0 Likes
6,787 Views

@ Sébastien Fatoux

That is correct. There are many factors in the logic for data reduction... You need to have

   - ACCESS = USER

   - Strict exclusion = ON

   - A match in the reducing field

to get the proper reduction.

On the other hand - all users are USER and not ADMIN when they open the document on the server, which is the most common way.

HIC

0 Likes
6,787 Views
Partner
Partner

Hello Henric,

Thanks for everything.

You talk about the risks that exist with users working off-line (downloading the physical qvw file to their desktop machine).

How a malicious user could see data which he shouldn't have access?

How could we prevent that, still using Section Access -instead of Publisher reduction?

Thanks again,

Ariel

0 Likes
6,787 Views

The file isn't encrypted, and that opens for a number of possibilities to circumvent Section Access. The most obvious one is to write your own exe that opens the file and exports the tables. There are other, easier ways to "crack" a qvw file, but I am not going to explain that on the official Qlik web page...

The bottom line is anyway that a qvw isn't really safe if you have access to the physical file. There is no way around it. So, you should instead distribute files that contain exactly what the user is allowed to see, and not more. I.e., you should use the Publisher loop-and-reduce.

HIC

0 Likes
6,787 Views
Not applicable

Very useful post, thanks a lot !

0 Likes
6,787 Views
Not applicable

Hi Henric,

Very useful post. I have a question on section access and loop and reduce.

I have qvw where I have applied section access and loop and reduce together. when the file got reduced the script go away. Does the section access code still works in datamodel?

In other way - can i reduce file on departments and still do dynamic reduction further based on employees?

Do i need to set up something else in task properties apart from loop and reduce?

Thanks in advance,

Anosh

0 Likes
6,787 Views

The fact that the script is erased does not matter. The only thing that matters is the data that was loaded in the last script run.

HIC

0 Likes
6,787 Views
Not applicable

Thanks Henric for the quick response. I have one more question. As Qlikview server respects windows directory authentication. Can I use groups as NTNAME in Qlikview section access.

1. Will Accesspoint provide access to only those users who are part of the assigned group?

2. Does application reload required if a new user is added to the windows group?

0 Likes
6,787 Views

Groups in NTNAME should work. And, no, I don't think a reload is necessary: The file contains the group name (not the group members) and the lookup in the AD (does this user belong to the group?) is done when the user tries to open the file.

HIC

0 Likes
6,787 Views
Not applicable

We use AD groups in the NTNAME field extensively.  Works well for us.

0 Likes
6,787 Views
Partner
Partner

Groups in NTNAME fields does work, you can also mix users and groups in NTNAME.

Only users part of any group listed in NTNAME or listad directly with user id in NTNAME will have access.

Reload is not necessary when users are added/removed from group that is listed in NTNAME.

Lookup against AD is live when user tries to access the document, it's not done during load.

But if you add or remove a group in the section access table a reload is needed.

0 Likes
6,787 Views
Not applicable

Thanks you everyone for your reply however for me the AD group name is not working perfectly. If a new user is added to the group, Accesspoint shows the dashboard but when opened it asks for username and password and doesn't go further after that. Even if putting the correct credentials.

Any solution for this problem?

Thanks in advance,

Anosh Nathaniel

0 Likes
6,787 Views
Not applicable

Have the AD changes had time to replicate across all DCs?  Sometimes users need to log off to pick up group membership changes.  You can sometimes force a simulation of this by doing a Run As Different User on the iexplore.exe process.

0 Likes
6,787 Views
Partner
Partner

Have you used the correct fields in section access?

There are several different fields you can use depending on the kind of access you want.

Sounds like you have used USERID and PASSWORD but should use NTNAME.

0 Likes
6,787 Views
Not applicable

Hi Graeme and Anders, Thanks for your valuable suggestion. My bad, it is working fine now. The AD groups were not in upper case in section access.

0 Likes
6,787 Views
rsdhavle
Contributor II

I have one doubt. After implementing section access we can give different names dynamically within the dashboard sheets, But can we manage two application names when someone lokks at access point? I dont think its feasible, but if there is any way pls guide me

0 Likes
6,787 Views