1.3 Data Security Architecture & Section Access Implementation

Data Security Architecture & Section Access Implementation

In enterprise analytics, data security is non-negotiable. Organizations must guarantee that confidential financial metrics, patient healthcare records, and regional sales performance are restricted strictly to authorized personnel. In Qlik Sense, this authorization architecture is managed through Section Access.

Section Access provides in-memory, row-level data reduction and column-level security (OMIT), ensuring that users only see data they are permitted to view without requiring separate applications for different user groups.

---

1. Security Architecture: Section Access vs. Section Application

A Qlik Sense script with data security is split into two distinct execution realms:

1. Section Access;: The security header of the script. It loads an authorization table containing user identities, access privileges, and reduction keys. When an application is opened, the QIX engine evaluates this table to authenticate the user and reduce the data model.
2. Section Application;: The business logic realm of the script. It loads the associative data model—fact tables, dimension tables, and master calendars. The statement Section Application; terminates the security section and restores standard data loading.

┌────────────────────────────────────────────────────────┐
│                     Section Access                     │
│  - ACCESS (ADMIN vs. USER)                             │
│  - Identity (USERID, USER.EMAIL, GROUP)                │
│  - Data Reduction Key (e.g., REDUCTION_KEY)            │
│  - Column Security (OMIT)                              │
└───────────────────────────┬────────────────────────────┘
                            │ Hidden associative filter applied
                            ▼
┌────────────────────────────────────────────────────────┐
│                  Section Application                   │
│  - Fact Tables                                         │
│  - Dimension Tables (linked via REDUCTION_KEY)         │
│  - Visualizations and Sheets                           │
└────────────────────────────────────────────────────────┘

The QIX Reduction Mechanism

When an authenticated user opens a Qlik Sense application:

1. Identity Resolution: QIX compares the signed-in user with the identity fields in the security table: USERID (the DOMAIN\USER name supplied by the proxy on client-managed Qlik Sense, or the identity provider subject in Qlik Cloud), USER.EMAIL, and GROUP.
2. Authorization Lookup: The engine scans the Section Access table for matching credentials. If no match is found, the user receives an immediate "Access Denied" message.
3. Privilege Assignment: The user is assigned ADMIN or USER rights based on the ACCESS field.
4. Associative Data Reduction: QIX identifies the reduction values associated with that user (e.g., COUNTRY = 'US') and applies an internal, permanent selection on that field. Associative propagation instantly filters all connected tables in Section Application. All unselected rows (the "gray" data) are physically excluded from the user's session.
5. Column Redaction: If the user's record specifies fields in the OMIT column, those fields and their symbol tables are completely excised from the data model for that user session.

---

2. System Reserved Fields & Authorization Syntax

Section Access utilizes reserved system fields to control authentication, authorization, and data reduction:

Differences Between ADMIN and USER

• ADMIN: Grants administrative privileges within the application. Administrators bypass sheet-level restrictions, retain access to view and edit the data load script (subject to Qlik Management Console security rules), and can debug data models. However, ADMIN users are still subject to row-level data reduction if reduction field values are explicitly assigned to their account.
• USER: Grants standard consumer privileges. Users cannot view or modify the data load script, cannot view omitted fields, and are restricted strictly to their reduced data subset.

---

3. Critical Formatting Rules: Case Sensitivity & Text Handling

If a data architect writes:

// INCORRECT: Mixed case will fail or cause access denial
Section Access;
LOAD * INLINE [
    Access, UserId, Region
    USER, JohnDoe, North
];

This script will fail or deny access. In Section Access, the engine enforces binary ASCII matching. If Region in Section Access contains 'NORTH', but Region in Section Application contains 'North', the values do not match. The associative engine will find zero matching records, locking the user out or presenting completely blank sheets.

Best Practice Transformation Pattern

Always enforce uppercase conversion and trim whitespace on reduction keys in both sections:

Section Access;
LOAD
    ACCESS,
    USERID,
    Upper(Trim(REDUCTION_KEY)) as REDUCTION_KEY
FROM [lib://Security/UserAccess.qvd] (qvd);

Section Application;
Fact_Sales:
LOAD
    OrderID,
    SalesAmount,
    Upper(Trim(RegionCode)) as REDUCTION_KEY
FROM [lib://Data/Sales.qvd] (qvd);

---

4. Wildcard Reduction Semantics: The Asterisk (*) Rules

One of the most frequently tested topics on the QSDA exam is the exact operational behavior of the asterisk (*) wildcard in Section Access reduction fields.

The Core Rule of the Asterisk

Architectural Definition: An asterisk (*) in a Section Access reduction field grants access to all values that are explicitly listed in that reduction field within the Section Access table itself. It does NOT inherently grant access to every value existing in the underlying database.

Consider this security configuration:

Section Access;
LOAD * INLINE [
    ACCESS, USERID, REGION_KEY
    ADMIN, CEO,     *
    USER,  ALICE,   NORTH
    USER,  BOB,     SOUTH
];

Section Application;
Fact_Sales:
LOAD * INLINE [
    OrderID, REGION_KEY, SalesAmount
    1,       NORTH,      500
    2,       SOUTH,      300
    3,       WEST,       700
];

What does the CEO see?

• The Section Access table explicitly lists only two values for REGION_KEY: NORTH and SOUTH.
• The wildcard * assigned to CEO expands strictly to the set {NORTH, SOUTH}.
• In Section Application, Order 3 has REGION_KEY = 'WEST'. Because WEST was never explicitly declared in the Section Access table, the CEO cannot see Order 3!
• To allow the * wildcard to include WEST, the architect must ensure WEST exists in the Section Access table (e.g., via a dummy administrative row or an explicit reduction key list).

Handling Nulls in Reduction Fields

In Qlik Sense, NULL values cannot be associated through Section Access. If a fact record has a NULL in the reduction field, no user—not even a user with *—can view that record. If unassigned records must be visible, the architect must replace NULL with an explicit string such as 'UNASSIGNED' in both Section Access and Section Application.

---

5. Column-Level Security via the OMIT Statement

While row-level security limits which records a user sees, column-level security controls which attributes exist in the user's data model. This is governed by the reserved OMIT field.

Mechanics of OMIT

• When an application opens, QIX reads the OMIT column for the authenticated user.
• The engine identifies the named fields and completely strips them from the data model. The field's symbol table and data table pointers are expunged from the user's active RAM session.
• If a user opens a sheet containing a visualization that needs an omitted field, Qlik can show Incomplete visualization for that user.
• Qlik recommends not omitting key fields: an omitted key still appears in the data model viewer without content, which confuses users.

Syntax for Multiple Omitted Fields

To omit multiple fields for a single user or group, the architect must declare multiple distinct rows in Section Access, one for each omitted field:

Section Access;
LOAD * INLINE [
    ACCESS, USERID, GROUP, REDUCTION_KEY, OMIT
    ADMIN,  DIR01,  EXEC,  *,             
    USER,   MGR01,  SALES, NORTH,         SALARY
    USER,   MGR01,  SALES, NORTH,         COMMISSION_PCT
    USER,   MGR01,  SALES, NORTH,         MARGIN_AMOUNT
];

In this example, manager MGR01 has access to NORTH regional records, but the fields SALARY, COMMISSION_PCT, and MARGIN_AMOUNT are completely removed from their data model.

---

6. Server Architecture & Automated Reload Governance

In enterprise deployments, data architects must ensure automated reload tasks succeed while maintaining strict data governance.

The INTERNAL\SA_SCHEDULER Requirement

On client-managed Qlik Sense Enterprise on Windows, scheduled reload tasks are executed by the Qlik Engine Service running under the built-in system identity: INTERNAL\SA_SCHEDULER.

• When the scheduler triggers a task reload, it opens the application binary.
• If Section Access is present, the engine authenticates INTERNAL\SA_SCHEDULER against the security table.
• Critical Failure: If INTERNAL\SA_SCHEDULER is omitted from Section Access, the reload task fails immediately with an "Access Denied" error, or the engine reloads an empty data model because the account was reduced to zero rows.

To ensure background reloads succeed, architects must always include INTERNAL\SA_SCHEDULER as an ADMIN with wildcard access:

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

If you do not want to use INTERNAL\SA_SCHEDULER, an administrator can enable impersonation in the QMC so reload tasks run with the app owner's permissions; in that case the app owner, not the scheduler account, needs the ADMIN row.

Qlik Cloud Section Access Considerations

In Qlik Cloud, authentication utilizes Identity Provider (IdP) subjects rather than Windows NT domain accounts:

• USERID is compared with the identity provider's subject claim (for example auth0|64a2f8...); alternatively use USER.EMAIL.
• To reload an app without data reduction, Qlik recommends USER.EMAIL rows with ADMIN access for the users who edit and reload it (for example ADMIN, TEST@EXAMPLE.COM, *), or an ADMIN row for a developer GROUP.
• INTERNAL\SA_SCHEDULER is a client-managed account; Section Access written for Qlik Sense Enterprise on Windows does not work unchanged in Qlik Cloud.

Preventing Developer Lockout

When authoring Section Access, a developer can inadvertently lock themselves out of the application if their own account is missing from the table or mistyped. Recovery means opening the app without data and editing the access section, which requires edit and reload rights to the app.

7. Complete Production Script Implementation

The following complete script demonstrates an enterprise-grade Section Access implementation, incorporating user directories, group inheritance, wildcard permissions, column omission, and automated scheduler accounts:

///$tab Section_Access_Security
// ---------------------------------------------------------------------------
// ENTERPRISE DATA SECURITY GATEWAY
// ---------------------------------------------------------------------------
Section Access;

// Load Authorization Matrix from Secure Governance Directory
// Rows (comments must stay outside the INLINE brackets, or they load as data):
//   1. Scheduler account for QMC reload tasks   2. Lead data architect
//   3. Executive group: all listed regions      4-5. Regional directors
//   6-9. Junior analysts: one region each, SALARY and PROFIT_MARGIN omitted
LOAD
    ACCESS,
    USERID,
    GROUP,
    Upper(Trim(REDUCTION_KEY)) as REDUCTION_KEY,
    Upper(Trim(OMIT))          as OMIT
INLINE [
    ACCESS, USERID,                 GROUP, REDUCTION_KEY, OMIT
    ADMIN,  INTERNAL\SA_SCHEDULER, *,     *,
    ADMIN,  CORP\QLIK_ADMIN,       *,     *,
    USER,   *,                      EXEC,  *,
    USER,   CORP\DIR_NORTH,        *,     NORTH,
    USER,   CORP\DIR_SOUTH,        *,     SOUTH,
    USER,   CORP\ANALYST_N1,       *,     NORTH,         SALARY
    USER,   CORP\ANALYST_N1,       *,     NORTH,         PROFIT_MARGIN
    USER,   CORP\ANALYST_S1,       *,     SOUTH,         SALARY
    USER,   CORP\ANALYST_S1,       *,     SOUTH,         PROFIT_MARGIN
];

// ---------------------------------------------------------------------------
// BUSINESS APPLICATION DOMAIN
// ---------------------------------------------------------------------------
Section Application;

// Load Dimensional Attributes
Dim_Region:
LOAD
    Upper(Trim(RegionCode)) as REDUCTION_KEY,
    RegionName,
    RegionalDirector
INLINE [
    RegionCode, RegionName,    RegionalDirector
    NORTH,      North Region,  Sarah Connor
    SOUTH,      South Region,  John Matrix
    EAST,       East Region,   Ellen Ripley
    WEST,       West Region,   James Braddock
];

// Load Fact Transactions with Matching Casing
Fact_Sales:
LOAD
    TransactionID,
    Upper(Trim(RegionCode)) as REDUCTION_KEY,
    SalesAmount,
    Salary       as SALARY,          // OMIT values are uppercase, so the field names must be too
    ProfitMargin as PROFIT_MARGIN
FROM [lib://SecureData/Transactions.qvd] (qvd);

---

8. Common Exam Pitfalls & Traps