Security Implementation in QlikView

A detailed look at how CALs, AD groups, QMC, and Section access work in QlikView

Published in

Globant

10 min readDec 5, 2022

Securing the application is just as crucial in the area of BI as developing & releasing it. The application has to be protected from unauthorized or unlawful access. For instance, Business users from the Health Insurance industry shouldn't be able to view or access the Qlik dashboard designed exclusively for the Motor Insurance industry. Also, if necessary, we can display only pertinent information so that users only see their data. We have certain security techniques that we will discuss in this article to address all the difficulties above.

This article will cover:

what QlikView is, and its architecture
how QlikView deals with authentication & authorization
Client Access Licenses (CAL) and their types
how to secure the QlikView application using AD groups
how to use theOSUSER() function in the QlikView sheets
data level section access at Application Level, Row Level, and Sheet Level

To proceed with the article, you should know about the QlikView BI tool and have a beginner's knowledge of QMC (Qlik Management Console) and Active Directory.

An Introduction to QlikView

The following image will help you understand the basic architecture of QlikView.

It has three layers:

Data Access — Connecting to multiple data sources through the QlikView desktop.
Application — Qlik Developer will create an application and publish/distribute the application through QMC.
Presentation — Business users can access the application through the QlikView Access point. To access the application, users need to have CALs assigned to them. Also, they should be part of the Active Directory and Section Access tables.

The purpose of explaining the 3-layer architecture is to give an end-to-end idea of implementing a secure QlikView application with the help of CALs, AD groups, and Section access features.

QlikView Authentication and Authorization

Authentication and authorization are two critical aspects of securing the application. Authentication is verifying who someone is, whereas authorization is verifying what specific applications, files, and data a user has access to.

Authentication and Authorization process

The web layer handles the first two steps of getting user credentials and verifying which covers authentication (i.e., QVWS or IIS).
The third step of transferring the user's identity is achieved by the web layer transferring the identity to the QlikView Server using the QVP protocol.
The fourth step is authorization, which is handled by the QlikView Server using groups resolved by the Directory Service Connector.

Introduction to Client Access License and its types

Client Access Licenses provide access to QlikView documents available on the QlikView Server. If CALs are not assigned to an individual or the document, you can't access the applications on the QlikView Access point. You must have a publisher license to use CALs.

Different types of CALs:

Named CAL: Named CAL access is based on the individual User; ID is valid for all the documents. If Named CAL is assigned to an individual user, they can access all the documents available on the QlikView Server. We must adhere to the instructions provided on the link to assign Named CAL to the users.
Document CAL: Document CAL access is also based on the individual User ID, but it's specific to only one document. If the same user connects to two documents using this licensing method, he will consume two Document CALs. We must adhere to the instructions provided on the link to assign Named CAL to the users.
Session CAL: Session CALs are automatically distributed to users if the user did not have a Named CAL or Document CAL assigned. They can be assigned to authenticated and anonymous users. A Session CAL has a minimum session duration of 15 minutes. Session CAL does not require explicit assignment; it will be a dynamic assignment if there is no possibility of getting other CALs (User CAL and Doc CAL) for the user or if no authentication happens. But remember to click on Allow dynamic CAL assignment and follow the same steps as Document CAL.
d. Usage CAL: A Usage CAL allows any user, identified or anonymous/unidentified, to access one QlikView document residing on the server to which the Usage CAL is assigned for one client for a period of up to 60 minutes per session.

Securing QlikView Applications using AD Groups

First, we must create AD groups and assign them to the QlikView applications. After assigning AD groups to individual applications or a bunch of Qlik Applications, only authorized users who are part of the AD group can see those applications; otherwise, they can't see anything on Qlik Applications. These AD groups will restrict unauthorized users from accessing Qlik Applications.

Below is the step-by-step procedure to secure the application through the active directory.

First, we must decide whether to create an AD group Line of Business-wise or Application-wise. If it's LOB-wise, we have to raise a ServiceNow request to create AD groups and assign them to a bunch of Qlik Applications that fall under the same LOB. If it's an Application wise, then we have to raise individual AD group requests and assign these AD groups to individual Qlik Applications.
After creating AD groups, Go to the Documents and click on the Source Documents tab.
Click and Expand QlikView Application and create a task
Go to the Distribute tab from the task and select the Manually tab. Click on + sign and add required details like Server Name, Mounted folder, User Types ( All users, All authenticated users, Named Users ), and Recipients like Single User or AD group from the dropdown.

The OSUSER() function in QlikView Sheets

OSUSER() is a part of system functions. System functions provide functions for accessing the system, device, and Qlik Sense app properties.

This function returns a string containing the name of the user that is currently connected. It can be used in both the data load script and a chart or QlikView sheet expression.

We can hide QlikView sheets from unauthorized users by using the OSUSER() function.

We can write expressions like if(OSUSER()=' Domain Name\Sachin.H',1,0) in the below QlikView conditional sheet tab.

It will evaluate the expression if it matches with ST\Sachin.H, then only that particular sheet will be visible; otherwise, you can't see it.

Data level section access

The section access works on the file level in which, once you have been granted access to a QlikView file, it decides what data sections within that data file the user will have access to. The section access security in QlikView can do up to row and field levels within a data table using the 'Hidden Script' feature available in QlikView.

Below are a few important points regarding section access.

Section access is reducing the data dynamically within the Qlik application, showing/hiding the Qlik sheets, and restricting illegal access to the Qlik applications.
It's used to control the security of the application. For example, User IDs who are a part of Section Access tables can access the application; otherwise, they will not be able to access it.
Section access is similar to reduction but is done by the QlikView Server when a user accesses the document, and the QlikView Publisher makes reduction.
We can accomplish this by using a User ID from Active Directory or Individual User IDs and NTNAME from the NT directory.

Note: Reduction based on Section Access may cause unwanted results in the distributed documents and should be used cautiously.

There are three types of section access implementation in the QlikView Application.

Application Level
Row Level
Sheet Level

Before we start, we will discuss section access syntax and important key points to implement the code.

There are two parts to the section access.

Section Access

In this section, we can Load an Inline Table, Excel, or Database table having the below information.

Users can be assigned to the access levels ADMIN or USER in the security table. A user with ADMIN privileges can access all data in the app unless limited by the security table. A user with USER privileges can only access data as defined in the security table. The user cannot open the app if no valid access level is assigned.

We can authenticate the application in two ways.

NTNAME — the user identity from an active directory or another authentication source; using this, you can support SSO
USERID — internal QlikView users identity a user and password managed by QlikView; you can use this method as a stand-alone or as a second tier of security
REDUCTION — This field helps reduce the dashboard data. If we want to show SOUTH data to XYZ user and NORTH region data to PQR then we can just add these values in the REDUCTION column, which should be available in the dashboard to restrict the data. The meaning of * in the REDUCTION column is that a particular user has access to all the regions specified in the below table.
OMIT — OMIT field will hide the field specified in the OMIT column from the user. For example, Hide the PROFIT column from USERID XYZ.

Above are the important parts of the section access table.

Section Application

In this section, we will include our QlikView code. We can add data modeling code here in this section.

Row-level section access will work only when we have a common column between Section access and the Section Application section.

The steps for creating a Section access code in the QlikView script are listed below.

The first line of code will be the Section Access and after that, add the Inline code /Excel/CSV/Database table with important parts of the Section access table as mentioned below.
After the Section Access part, we have to write the Section Application keyword ending with a semicolon and add the actual QlikView code or business logic.
Afterward, go to Settings — Document Properties — Opening tab — Check Initial Data Reduction based on Section access and Strict Exclusion without fail.

Based on the checked status, the dashboard will reduce the data.

Section Access;
Load * INLINE [
    ACCESS, USERID, REGION, OMIT
    ADMIN, A,*, 
    USER, B, SOUTH, PROFIT,
    USER, C, EAST,
    USER, D, NORTHEAST,
];
Section Application;

Load * INLINE [
REGION, SALES, PROFIT
EAST,100,8
WEST,200,6
SOUTH,300,7
NORTH,400,4
];

QlikView Section Access Strict Exclusion Demo

We will get an error message if someone with unauthorized access tries to open an application. Also, due to Strict exclusion, USER D has NORTHEAST region access, but we don't have NORTHEAST region data in the data model in the above example so USER D will get an error.

Different types of Section Access

Below are some snapshot examples of Application, Row, and Sheet level Section Access.

Application Level

We won't have any problems if we attempt to log in using the User IDs (A, B, C, and D) listed below, but we won't be able to do so if we use any other User IDs not listed in the table below.

Below are the snapshots of Admin Access edit privileges and USER edit. USER-level access has minimal privileges.

Row Level Access

In the below example, after login by User A, we can see all the region's data.

In the below output, after login by User B, we can see only SOUTH region data.

OMIT Example :

For example, the OMIT field will remove the field specified in the OMIT column. Hide the PROFIT column from USERID B.

See the below output; you don't see the PROFIT column after login by User B.

Sheet Level Access

Sheet-level section access is a little bit different. We must maintain one inline or Excel/CSV table with the User and Sheet number. See the inline table highlighted below. '1' denotes enabling the sheet, and '0' denotes disabling the sheet.

As per the above Inline Sheet level table, we must assign SH01 and SH02. So on to respective sheets like below.

So User ID A cant see Sheets 2 and 5, and User ID B cant see Sheets 1 and 4.

User ID A doesn't see Sheet 2 and 5

User ID B doesn't see Sheet 1 and 4

Conclusion

After discussing every security component in QlikView, we can conclude that Section access is a very important part of the Qlik BI tool. We can create solid and secure applications using different levels like Application, Row, and Sheet levels.

Also, we gained knowledge about Assigning AD groups to the Qlik Applications using QlikView Management Console and covered the basics of Client Access Licenses.

We have understood the use of the OSUSER() function when we don't want to add section access code but want to hide and show the sheets based on the limited number of users.

References

Thanks to Federico Kereki !!