Managing data security with Section Access

Section Access is used to control the security of an application. It is basically a part of the data load script where you add a security table to define who gets to see what. Qlik Sense uses this information to reduce data to the appropriate scope when the user opens the application, that is, some of the data in the app is hidden from the user based on their identity. Section Access is tightly integrated with the data in the app and relies upon it to control access. This form of dynamic data reduction can target table rows, columns, or a combination of both. For more information, see Trust and Security at Qlik.

Sections in the load script

Data access control is managed through one or more security tables loaded in the same way that 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:

Section Access; Load * INLINE [ ACCESS, USERID, REDUCTION USER, AD_DOMAIN\ADMIN, * USER, AD_DOMAIN\A, 1 USER, AD_DOMAIN\B, 2 USER, AD_DOMAIN\C, 3 ADMIN, INTERNAL\SA_SCHEDULER, ]; Section Application; T1: Load *, NUM AS REDUCTION; LOAD Chr(RecNo()+ord('A')-1) AS ALPHA, RecNo() AS NUM AUTOGENERATE 3;

Note that after making changes to the load script, you must always reload the data for the changes to take effect.

Section Access system fields

The access levels are assigned to users in one or more security tables loaded within the Section Access part of the script. These tables must contain, as a minimum, two system fields: ACCESS, which is the field defining the access level, and USERID or USER.EMAIL . Other optional system fields can be added depending on the use case. The full set of Section Access system fields is described below.

ACCESS

Defines what access the corresponding user should have.

Access to Qlik Sense apps can be authorized for specified users. In the security table, users can be assigned to the access levels ADMIN or USER. A user with ADMIN privileges has access to 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. If no valid access level is assigned, the user cannot open the app.

If Section Access is used in a reload scenario, INTERNAL\SA_SCHEDULER, which is the scheduler service user, needs ADMIN access to perform reloads. For example:

Section Access; LOAD * inline [ ACCESS, USERID ADMIN, INTERNAL\SA_SCHEDULER ];

If you do not want to use the INTERNAL\SA_SCHEDULER account, see Using impersonation to reload data for an alternate method.

If Section Access is used in an on-demand app generation (ODAG) scenario in the template app, the INTERNAL\SA_API user must be included as ADMIN in the Section Access table. For example:

Section Access; LOAD * inline [ ACCESS, USERID ADMIN, INTERNAL\SA_API ];

USERID

Contains a string corresponding to a Qlik Sense domain name and user name. Qlik Sense will get the login information from the proxy service and compare it to the value in this field.

A wildcard character (*) is interpreted as all users, subject to further conditions specified in the security table. For example, in the following security table, users who are in the Qlik Sense Tenant Admins can see all listed REDUCTION values.

Section Access; LOAD * INLINE [ ACCESS, USERID, GROUP, REDUCTION ADMIN, *, Qlik Sense Tenant Admins, * USER, QLIK-POC\SOMEOTHERUSER1, *, 1 USER, QLIK-POC\SOMEOTHERUSER2, *, 2 ... ];

USERID and the NTNAME both use the same authentication information, so it is not necessary to check both on the same row in the security table. The difference between the two fields is that NTNAME checks groups also.

NTNAME

NTNAME is a legacy QlikView field and it is recommended to use USERID if QlikView is not using the same security table.

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. Qlik Sense will fetch the login information from the OS and compare it to the value in this field.

GROUP

Contains a string corresponding to a group in Qlik Sense. Qlik Sense will resolve the user supplied by the proxy service against this group.

SERIAL

SERIAL is a legacy QlikView field and is not used if you are only using Qlik Sense.

Contains a string corresponding to the platform. If the field contains the string ‘QLIKSENSE’ or a wildcard ‘*’, access may be granted, depending on the other fields in the security table.

If the field SERIAL contains a license number the Section Access row will deny access to the document. This setting is only valid in QlikView.

OMIT

Contains the name of the field that is to be omitted for this specific user. Wildcards may be used and the field may be empty.

It is recommended that you do not apply OMIT on key fields. Key fields that are omitted are visible in the data model viewer, but the content is not available, which can be confusing for a user. Additionally, applying OMIT on fields that are used in a visualization can result in an incomplete visualization for users that do not have access to the omitted fields.

Managing user access to an app

Section access, in its simplest form, can be used to restrict specific users from accessing an app. Users are denied access to an app through exclusion. In other words, if a specific user ID is not listed in the security table, they will not be able to access the app. The only exception to this rule is if a wildcard (*) is assigned to the USERID field in one of the rows in the security table. A wildcard, in this case, means that all authenticated users can access the app. Here is an example of a security table with a list of user IDs:

Section Access; LOAD * inline [ ACCESS, USERID ADMIN, AD_DOMAIN\ADMIN USER, AD_DOMAIN\A USER, AD_DOMAIN\B ]; Section Application;

Managing user access to specific data in an app

Dynamic data reduction limits access to rows and columns in the data tables within Qlik Sense apps after a user has been authorized to access the app itself.

Managing access to row-level data

Restrict access to row-level data by adding a data reduction column to the security table in the access section of the load script. Specific records (rows) can be hidden from users by linking the Section Access data with the real data. The selection of data to be shown or excluded is controlled by having one or more reduction fields with common names in Section Access and Section Application parts of the script. After user login, Qlik Sense matches the selections in reduction fields in the access section to any fields in the application section with exactly the same field names (the field names must be written in uppercase). After the selections have been made, Qlik Sense permanently hides all data excluded by these selections from the user. If a wildcard (*) is used as a field value in the data reduction column, it is interpreted as allowing the user to access records associated with all selected reduction fields in the security table.

When Qlik Sense is comparing the reduction field in Section Access to fields in the data model, the following behaviors are expected:

If a field value in the data model matches the reduction field in Section Access, the app will open showing data associated with the match for the specified user. Other data will be hidden.
If the reducing field value does not match any of the values in the data model, the app will not open for a normal USER. It will, however, open unreduced for a user marked as ADMIN.

Using several reducing fields in Section Access is not recommended, since it will allow other access combinations than the intended ones.

The wildcard character * in the data reduction column refers only to all values in the security table. If there are values in Section Application that are not available in the reduction column of the security table, they will be reduced.

All field names used in the transfer described above and all field values in these fields must be uppercase because all field names and field values are, by default, converted to uppercase in the access section.

By default, if you want to enable reload of the script in a Qlik Management Console task, the INTERNAL\SA_SCHEDULER account user with ADMIN access is required. If you do not want to use the INTERNAL\SA_SCHEDULER account, see Using impersonation to reload data for an alternate method.

Example: Row-level data reduction based on user identity

Section Access; Authorization: LOAD * inline [ ACCESS, USERID, REDUCTION ADMIN, AD_DOMAIN\ADMIN, * USER, AD_DOMAIN\A, 1 USER, AD_DOMAIN\B, 2 USER, AD_DOMAIN\C, * ADMIN, INTERNAL\SA_SCHEDULER, * ]; Section Application; T1: LOAD *, NUM AS REDUCTION; LOAD RecNo() AS NUM AUTOGENERATE 3;

In this example, the field REDUCTION (uppercase) now exists in both Section Access and Section Application (all field values are also uppercase). The two fields would normally be different and separated, but using Section Access, these fields are linked and the number of records displayed to the user is reduced.

The result will be:

User ADMIN can see all fields and only those records other users can see when REDUCTION = 1 or REDUCTION =2.
User A can see all fields, but only those records associated with REDUCTION=1.
User B can see all fields, but only those records associated with REDUCTION=2.
User C can see all fields and only those records other users can see when REDUCTION = 1 or REDUCTION =2.

Managing access to column-level data

Restrict access to column-level data by adding the OMIT system field to the security table in the Section Access script. The following example builds on the previous example where row data reduction is already in place.

Example: Column data reduction based on user identity

Section Access; LOAD * inline [ ACCESS, USERID, REDUCTION, OMIT ADMIN, AD_DOMAIN\ADMIN, *, USER, AD_DOMAIN\A, 1, USER, AD_DOMAIN\B, 2, NUM USER, AD_DOMAIN\C, 3, ALPHA ADMIN, INTERNAL\SA_SCHEDULER, *, ]; Section Application; T1: LOAD *, NUM AS REDUCTION; LOAD Chr( RecNo()+ord('A')-1) AS ALPHA, RecNo() AS NUM AUTOGENERATE 3;

The field OMIT in Section Access defines the fields that should be hidden from the user.

The result will be:

User ADMIN can see all fields and only those records other users can see in this example when REDUCTION is 1, 2, or 3.
User A can see all fields, but only those records associated with REDUCTION=1.
User B can see all fields except NUM, and only those records associated with REDUCTION=2.
User C can see all fields except ALPHA, and only those records associated with REDUCTION=3.

Some visualizations have minimum data requirements that must be met in order to render. As a result, "Incomplete visualization" might be displayed when a column-level field is omitted from a user's data view.

Managing access to user groups

Section Access offers you the option to limit the scope of data visible to users through group membership. To restrict your data using user groups, add the GROUP field name to the security table in the access section and define values for the GROUP field.

Example: Data reduction based on user groups

Section Access; LOAD * inline [ ACCESS, USERID, GROUP, REDUCTION, OMIT USER, *, ADMIN, *, USER, *, A, 1, USER, *, B, 2, NUM USER, *, C, 3, ALPHA USER, *, GROUP1, 3, ADMIN, INTERNAL\SA_SCHEDULER, *, *, ]; section application; T1: LOAD *, NUM AS REDUCTION; LOAD Chr( RecNo()+ord('A')-1) AS ALPHA, RecNo() AS NUM AUTOGENERATE 3;

The result will be:

Users belonging to the ADMIN group are allowed to see all fields and only those records other users can see in this example when REDUCTION is 1, 2, or 3.
Users belonging to the A group are allowed to see data associated with REDUCTION=1 across all fields.
Users belonging to the B group are allowed to see data associated with REDUCTION=2, but not in the NUM field
Users belonging to the C group are allowed to see data associated with REDUCTION=3, but not in the ALPHA field
Users belonging to the GROUP1 group are allowed to see data associated with REDUCTION=3 across all fields

Qlik Sense compares the user with UserID and resolves the user against groups in the table. If the user belongs to a group that is allowed access, or the user matches, they can access the app.

Using impersonation to reload data

By default, the internal system account, SA_SCHEDULER, is used to run reload tasks. This account has elevated privileges and, technically, can use any data source. There is a setting, however, in the QMC that uses impersonation to run reload tasks with the permissions of the app owner instead of the internal system account. By configuring this setting, the app owner and not SA_SCHEDULER is used for reloads, meaning that you do not add SA_SCHEDULER in the Section Access table but instead add the app owner. Within a task chain, apps can have different owners with permissions to sources dependent on each owner's access rights. See Service cluster for more information.

Managing user access in a multi-cloud environment

A Qlik Sense multi-cloud environment involves a mix of user authentication mechanisms. Typically, with Qlik Sense Enterprise on Windows, the USERID in the Section Access security table is verified by the proxy service. In Qlik Cloud, an Identity Provider assumes that authentication role. Consequently, Section Access that is set up for an on-premises environment such as Qlik Sense Enterprise on Windows will not work in a cloud environment.

When using an OIDC or SAML identity provider (Qlik IdP or custom IdP) with Qlik Cloud, the subject claim is used to identify users when logging in. With Section Access, the value of the USERID field in the security table is compared to the value of the subject claim. When you set up your tenant, make sure that the SAM account name is mapped to the subject claim of your identity provider. So, for example, if your SAM account name is AD_DOMAIN\Dev, set the subject claim to AD_DOMAIN\Dev. If you want to see the value of the subject claim of the IdP, append /api/v1/diagnose-claims to the tenant URL in the browser, for example, your-tenant.us.qlikcloud.com/api/v1/diagnose-claims. In the JSON response, the subject claim is called sub.

If you are unable to use the SAM account name, there is an alternate way to authenticate a user. Since e-mail addresses tend to remain the same in different environments, you can use the USER.EMAIL field instead of USERID in the security table. Here is an example of what the security table could look like:

ACCESS	USERID	USER.EMAIL	Comment	COUNTRY
USER	ABC\Joe	*	Access-on-prem	United States
USER	*	joe.smith@example.com	Access-in-cloud	United States
USER	ABC\Ursula	*	Access-on-prem	Germany
USER	*	ursula.schultz@example.com	Access-in-cloud	Germany
USER	ABC\Stefan	*	Access-on-prem	Sweden
USER	*	stefan.svensson@example.com	Access-in-cloud	Sweden

Authorization script:

Section Access; LOAD * INLINE [ ACCESS, USERID, USER.EMAIL, COUNTRY USER, ABC\Joe, *, United States USER, *, joe.smith@example.com, United States USER, ABC\Ursula, *, Germany USER, *, ursula.schultz@example.com, Germany USER, ABC\Stefan, *, Sweden USER, *, stefan.svensson@example.com, Sweden ];

Note that each user has two records: One for on-premises access and one for cloud access. The wildcards ensure that only the relevant authenticating fields are used. In this example, COUNTRY is used as a data reduction field.

Using Section Access and Insight Advisor Chat

To make apps using section access to be available in Insight Advisor Chat, you must ensure the following service users have admin access in the section access script:

INTERNAL/sa_repository: This makes the section access script available with the repository service for controlling user access.
INTERNAL/sa_scheduler: This allows the app to reload using QMC tasks.

If you have sensitive information in app names, field names, or master item names, these may be exposed by making apps using Section Access available for Insight Advisor Chat. App suggestions for queries include apps in streams to which users have access. These may include apps to which users do not have access in an app's Section Access. Selecting these apps will do nothing, however. When clicking Dimensions or Measures to view the available items from an app using Section Access, users may see items to which they do not have access. Clicking on these items will not provide any data to the users, however.

For example:

Section Access; LOAD * inline [ USERID ,ACCESS INTERNAL\sa_repository ,ADMIN INTERNAL\sa_scheduler ,ADMIN DOMAINNAME\user1 ,ADMIN DOMAINNAME\user2 ,USER DOMAINNAME\user3 ,USER ];

Once these users are in the Section Access script, you can make the app available for Insight Advisor Chat. Once the app is reloaded, the app will be available in Insight Advisor Chat.

Using QVDs with Section Access

QVD files can be read as a regular load or as an optimized load. An optimized load is when no data transformations are made during loading and there are no filters in a WHERE clause.

Optimized loads do not work when using QVDs with Section Access. If you want to use a QVD file to load data into Section Access, you must expand the QVD file. The easiest way to expand the QVD file is to make a formatting change when loading the data.

In the following example, the QVD file is not expanded as no formatting is done to the data.

Example: Non-working example with no data formatting (optimized load)

section access; LOAD ACCESS, USERID, PASSWORD, [GROUP] FROM SAccess.qvd (qvd);

Instead, you can for example use the upper() function to format the data which will expand the QVD file.

Example: Working example with data formatting

section access; LOAD ACCESS, USERID, PASSWORD, upper([GROUP]) as [GROUP] FROM SAccess.qvd (qvd);

You can also add a Where 1=1 statement to the LOAD statement.

Example: Another working example with data formatting

section access; LOAD ACCESS, USERID, PASSWORD, [GROUP] FROM SAccess.qvd (qvd) where 1=1;

Guidelines and tips for using Section Access

Here are some important facts and helpful hints to know about Section Access.

All the field names and values listed in LOAD or SELECT statements in the access section must be written in uppercase. Convert any field name containing lowercase letters in the database to uppercase using the Upper function before reading the field by the LOAD or SELECT statement.
For more information, see Upper - script and chart function.
You cannot use the Section Access system field names listed as field names in your data model.
Apps must be published before Section Access controls will be applied. Reloading the app does not apply any new or changed Section Access scripts.
A snapshot shows data according to the access rights of the user who takes the snapshot, and the snapshot can then be shared in a story. However, when users return to a visualization from a story to see the live data in the app, they are restricted by their own access rights.
Do not assign colors to master dimension values if you use section access or work with sensitive data, because the values might be exposed by the color configuration.
To avoid exposing restricted data, remove all attached files with section access settings before publishing the app. Attached files are included when the app is published. If the published app is copied, the attached files are included in the copy. However, if section access restrictions have been applied to the attached data files, the section access settings are not retained when the files are copied, so users of the copied app will be able to see all the data in the attached files.
A wildcard (*) is interpreted as all (listed) values of the field in the table. If used in one of the system fields (USERID, GROUP) in a table loaded in the access section of the script, it is interpreted as all (also not listed) possible values of this field.
Security fields can be put in different tables.
When loading data from a QVD file, the Upper function slows down the loading speed.
If you have locked yourself out of an app by setting Section Access, you can open the app without data, and edit the access section in the data load script. This requires that you have access to edit and reload the data load script.
For more information, see Opening an app without data.
A binary load will cause the access restrictions to be inherited by the new Qlik Sense app.

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!

Leave your feedback here