A security mechanism in QlikView can be set up in two different ways: It can either be built into the QlikView document script, or it can be set up through the use of QlikView Publisher.
Authentication and Authorization
Authentication is any process by which it is verified that someone is who they claim they are. QlikView can either let the Windows operating system do the authentication, or prompt for a User ID and Password (different from the Windows User ID and Password) or use the QlikView license key as a simple authentication method.
Authorization is finding out if the person, once identified, is permitted to have the resource. QlikView can either let the Windows operating system do the authorization or do the authorization itself. For the latter, a security table must be built into the script.
Security Using the QlikView Publisher
If the QlikView Publisher is set up to handle security, then each QlikView file will be split up into several files, each containing the data pertaining to the relevant user or user group. These files will be stored in folders with the correct OS security settings, that is, QlikView lets the operating system handle Authentication and Authorization.
There is, however, no security built into the file itself, so there is no protection on a downloaded file.
The file sizes will usually be smaller, since one single file will be split into several and the user only opens the file with his own data. However, this also means that a QlikView Server can potentially use more memory than if all data are kept in one file, since several files containing the same data sometimes will be loaded.
For further information, see the QlikView Publisher documentation.
Security Using the Section Access in the QlikView Script
If the Section Access in the QlikView script is set up to handle security, then one single file can be made to hold the data for a number of users or user groups. QlikView will use the information in the Section Access for Authentication and Authorization and dynamically reduce the data, so that the user only sees his own data.
The security is built into the file itself, so also a downloaded file is to some extent protected. However, if the security demands are high, downloads of files and offline use should be prevented. The files should be published by the QlikView Server only.
Since all data are kept in one file, the size of this file can potentially be very large.
QlikView documents can be made invisible in offline mode. To make an offline user document invisible, add the following attribute in the document information section of a user document using the QMC:
- Name: Invisible
- Value: True
All information below refers to the security method of using Section Access in the QlikView script.
Sections in the Script
Row-level access is managed through one or several security tables loaded in the same way as data is normally loaded. This makes it possible to store these tables in a standard database or in a spreadsheet. The script statements managing the security tables are given within an authorization section, which in the script is initiated by the statement Section Access.
If an authorization section is defined in the script, the part of the script loading the app data must be put in a different section, initiated by the statement Section Application.
Example:
Access Levels in Section Access
Access to QlikView documents can be authorized for specified users or groups of users. In the security table, users can be assigned to the access levels ADMIN or USER. If no access level is assigned, the user cannot open the QlikView document.
A person with ADMIN access can change everything in the document. Using the Security page in the Document Properties and Sheet Properties dialogs, a person with ADMIN access can limit the users’ possibilities of modifying the document. A person with USER privileges cannot access the Security pages.
Section Access System Fields
The access levels are assigned to users in one or several tables loaded within the section access. These tables can contain several different user-specific system fields, typically USERID and PASSWORD, and the field defining the access level, ACCESS. All Section Access system fields will be used for authentication or authorization. The full set of section access system fields are described below.
None, all, or any combination of the security fields may be loaded in the access section. It is thus not necessary to use USERID – an authorization can be made using other fields, for example, serial number only.
| Field | Description |
|---|---|
| ACCESS | A field that defines what access the corresponding user should have. |
| USERID | A field that should contain an accepted user ID. QlikView will prompt for a User ID and compare it to the value in this field. This user ID is not the same as the Windows user ID. |
| USER.EMAIL | Currently not supported, will in QlikView only match on wildcard. |
| PASSWORD | A field that should contain an accepted password. QlikView will prompt for a Password and compare it to the value in this field. This password is not the same as the Windows password. |
| SERIAL | A field that should contain a number corresponding to the QlikView serial
number or the string 'QLIKVIEW'. Example: 4900 2394 7113 7304 QlikView will check the serial number of the user or the string 'QLIKVIEW' and compare it to the value in this field. |
| NTNAME | A field that should contain a string corresponding to a Windows NT Domain user name or group name. If a different authentication system is used, it should contain the name of an authenticated user. QlikView will fetch the logon information from the OS and compare it to the value in this field. |
| NTDOMAINSID | A field that should contain a string corresponding to a Windows NT Domain
SID. Example: S-1-5-21-125976590-4672381061092489882 QlikView will fetch the logon information from the OS and compare it to the value in this field. |
| NTSID | A field that should contain a Windows NT SID. Example: S-15-21-125976590-467238106-1092489882-1378 QlikView will fetch the logon information from the OS and compare it to the value in this field. |
| OMIT |
A field that should contain the field that should be omitted for this specific user. Wildcards may be used and the field may be empty. A facile way of doing this is to use a subfield. Information noteYou should not apply OMIT on key fields, as this will change the underlying data structure. This may create logical islands and calculation inconsistencies.
|
QlikView will compare the QlikView serial number with the field SERIAL, the Windows NT User name and groups with NTNAME, the Windows NT Domain SID with NTDOMAINSID and the Windows NT SID with NTSID. It will further prompt for User ID and Password and compare these with the fields USERID and PASSWORD.
If the found combination of user ID, password and environment properties is also found in the section access table, then the document is opened with the corresponding access level. If not, QlikView will deny the user access to the document. If the User ID and/or the Password are not entered correctly within three attempts the entire log-on procedure must be repeated.
Since the same internal logic that is the hallmark of QlikView is used also in the access section, the security fields may be put in different tables. (It is thus possible for a system manager to make a QlikView document out of the security tables. In this case a correct serial number, password etc. is simulated by a click on the corresponding field value.)
In the logon procedure, QlikView will first check SERIAL, NTNAME, NTDOMAINSID and NTSID to see if this information is enough to grant the user access to the document. If so, QlikView will open the document without prompting for User ID and Password.
If only some of the access fields are loaded, the appropriate of the above requirements are used.
All the fields listed in Load or Select statements in the section access must be written in UPPER CASE. Any field name containing lower case letters in the database should be converted to upper case using the upper function before being read by the Load or Select statement.
Upper - script and chart function
However the user ID and the password entered by the end-user opening the QlikView documents are case insensitive.
A wildcard (*) is interpreted as all (listed) values of this field, that is, a value listed elsewhere in this table. If used in one of the system fields (USERID, PASSWORD, NTNAME or SERIAL) in a table loaded in the access section of the script, it is interpreted as all (also not listed) possible values of this field.
Example 1:
Only serial number is checked. One specific computer gets ADMIN access. Everyone else gets USER access. Note that a star can be used to mark “any serial number”.
| ACCESS | SERIAL |
|---|---|
| ADMIN | 4900 2394 7113 7304 |
| USER | * |
Example 2:
The administrator and the server on which QlikView runs as a batch job get ADMIN access. Everyone else in the Domain gets USER access when entering “USER” as user ID and password.
| ACCESS | SERIAL | NTDOMAINSID | USERID | PASSWORD |
|---|---|---|---|---|
| ADMIN | * | S-1-5-21-125976590-467238106-1092489882 | ADMIN | ADMIN |
| ADMIN | 4900 2394 7113 7304 | * | * | * |
| USER | * | S-1-5-21-125976590-467238106-1092489882 | USER | USER |
Mixed environments
If you plan to use the same authorization table in both QlikView and Qlik Sense SaaS, there are a couple of things to be aware of:
• USERID has different meanings in QlikView and Qlik Sense SaaS, and could, if used, cause security problems. Use NTNAME instead or combine it with SERIAL as described below.
• GROUP and fields beginning with ‘USER.’, such as 'USER.NAME' and 'USER.EMAIL', etc. are (or will be) authenticating fields in Qlik Sense Enterprise SaaS. If you use these fields in your Section Access, access may be denied in Qlik Sense SaaS.
• PASSWORD, NTSID and NTDOMAINSID cannot be used in Qlik Sense SaaS. Access will be denied, unless a wildcard is used.
• SERIAL cannot be used to check license number in Qlik Sense SaaS. However, if this field contains the string ‘QLIKCLOUD’ or 'QLIKVIEW', access might be granted. This means that it is possible to have an authorization table like the following, where line 1 will grant access in QlikView (but not in Qlik Sense SaaS), and line 2 will grant access in Qlik Sense SaaS (but not in QlikView).
| Line | SERIAL | USERID | Comment |
|---|---|---|---|
| 1 | 4600 0123 4567 8901 | * | Grants access to correct license number in QlikView. |
| 2 | QLIKCLOUD | John Doe | Grants access to correct user in Qlik Sense Enterprise SaaS. |
| Line | SERIAL | USERID | Comment |
|---|---|---|---|
| 1 | QLIKVIEW | * | Grants access to QlikView. |
| 2 | QLIKCLOUD | John Doe | Grants access to correct user in Qlik Sense Enterprise SaaS. |
Restrictions on QlikView Functionality
The controls found on the Document Properties: Security page and the Sheet Properties: Security page make it possible to disallow the access to certain menu items and prohibit changes in the layout. If these settings are to be used as a truly protective measure, it is important that the document users are logged in as USER. Anyone logged in as ADMIN can change the security settings at any time.
A user that has opened the document with USER rights does not have the Security pages in the Properties dialogs.
Dynamic Data Reduction
QlikView and QlikView Server support a feature by which some of the data in a document can be hidden from the user based on the section access login.
First of all, fields (columns) can be hidden by the use of the system field OMIT.
Secondly, records (rows) can be hidden by linking the Section Access data with the real data: The selection of values to be shown/excluded is controlled by means of having one or more fields with common names in section access and section application. After user login QlikView will attempt to copy the selections in fields in section access to any fields in section application with exactly the same field names (the field names must be written in UPPER CASE). After the selections have been made, QlikView will permanently hide all data excluded by these selections from the user.
In order for this procedure to take place, the option Initial Data Reduction Based on Section Access on the Document Properties: Opening page must be selected. If this feature is used in documents that are to be distributed by other means than via QlikView Server, the option Prohibit Binary Load on the same page of the Document Properties must be selected in order to maintain data protection.
Example:
section access;
LOAD * inline [
ACCESS, USERID, REDUCTION, OMIT
ADMIN, ADMIN, *,
USER, A, 1
USER, B, 2, NUM
USER, C, 3, ALPHA
];
section application;
T1:
LOAD *,
NUM AS REDUCTION;
LOAD
Chr( RecNo()+ord('A')-1) AS ALPHA,
RecNo() AS NUM
AUTOGENERATE 3;
The field REDUCTION (upper case) now exists in both section access and section application (all field values are also upper case). The two fields would normally be totally different and separated, but if the Initial Data Reduction Based on Section Access option has been selected, they will link and reduce the number of records displayed to the user.
The field OMIT in section access defines the fields that should be hidden from the user.
The result will be as follows:
User A can see all fields, but only those records connected to REDUCTION=1.
User B can see all fields except NUM, and only those records connected to REDUCTION=2.
User C can see all fields except ALPHA, and only those records connected to REDUCTION=3.
Inherited Access Restrictions
A binary load will cause the access restrictions to be inherited by the new QlikView document. A person with ADMIN rights to this new document may change the access rights of this new document by adding a new access section. A person with USER rights can execute the script and change the script, thus adding own data to the binary loaded file. A person with USER rights cannot change the access rights. This makes it possible for a database administrator to control the user access also to binary loaded QlikView documents.
Encryption
The communication between a QlikView Server and a QlikView Windows client is encrypted. If, however, the AJAX client is used, the communication is not encrypted.
In addition, all QlikView documents are scrambled which makes the information unreadable with viewers, debuggers etc.
You can also encrypt sensitive data in QVD files with customer supplied key pairs which allows you to control who gets access to your data. See QVD Encryption.
