Column-level Permission

(Or restricting user access control at the column level)

Introduction

Sometimes in a report/dashboard, you want to restrict access control at the column level, allow/disallow certain users to see certain columns. In Holistics, we call this Column-level Permission.

Consider some of these real-world use cases:

• Restrict access based on users: In a department (e.g sales), you want each salesperson to see others' data, but they can only see customer's information on the deals they own.
• Restrict PII Data Access: For reports with PII (personally identifiable information) data, you want to grant access to selected set of users. Other users can still see report data, but cannot see the PII-related columns.

Unlike Row-level Permission, this approach doesn't completely remove the rows for the users. Instead, it masks the column values (e.g., showing "(redacted)") for users who shouldn't see them.

Use Case

In this post, we'll share with you how to implement the PII access example above where:

• Managers have full PII access.
• At staff-level, PII access is granted on a case-by-case basis.

Once set up, whenever a user views the Orders report or dataset, appropriate PII check will take place to determine the user can see the customer information.

Approach 1: Set up Column-level Permission for Viewers only

Viewers Only

This approach only works for users without Dataset access (e.g., Viewers).

Users with Dataset access can bypass this Column-level Permission rule by referencing the original un-masked fields directly, for example through ad-hoc calculations.

For protecting data from Explorers, see Approach 2 below.

To determine if a user can see PII data, we create a user attribute (e.g pii_access). We create a Managers group with default pii_access turned on. All manager-level users will be added to this group.

In the data model, we create a custom field (e.g customer_email_pii) with logic to check on PII. We use this field as the main email going forward. The custom field's logic will look like (pseudocode):

if $user.pii_access == true
  then model.customer_email
  else '(redacted)'
end

So when a manager (with pii_access = true) views a dashboard, they will see the full data. When a non-manager views, they will see the customer email redacted.

Implementation

Let's walk through the detailed steps below.

1. Create user_attribute pii_access

Create a user attribute called pii_access with Number type. 0 will mean no access, and 1 will mean access.

2. Create group 'Managers' and add all managers to the group

Create a usergroup 'Managers'. Also set pii_access to 1 for this usergroup.

Add all manager-level users to this group.

3. Create custom field with PII-aware logic

Go to your users model (users is the model that contains list of users in your system), and create a custom field email_pii using AQL expression:

Model users {

  dimension email { // this is your original email dimension
    label: 'Email'
    type: 'text'
    hidden: true  // Note: This only hides from the field picker UI.
                  // It is NOT a security control - users with Dataset access
                  // can still reference this field in custom calculations.
    definition: @sql {{ #SOURCE.email }};;
  }

  // define your Email PII field below
  dimension email_pii {
    label: 'Email PII'
    description: 'The email for the user. [sensitive data may be restricted]'
    type: 'text'
    hidden: false
    definition: @aql
      case(
        when:
          in(1, H.current_user.pii_access),
        then: users.email,
        else: '(redacted)'
      )
    ;;
  }

}

hidden: true is not a security control

Setting hidden: true only hides the field from the field picker UI. Users with Dataset access can still reference hidden fields in custom calculations. Do not rely on this as a security measure.

4. Result

Done. Now when a user explores email_pii, or views a chart/dashboard that uses this field, the system checks their PII access and shows the customer email only when they have been granted permission.

---

Approach 2: Set up Column-level Permission for Explorers

The approach above works well for Viewers, but Explorers (who get shared the underlying dataset) can bypass it since they have access to the underlying model fields. This section covers how to enforce column-level permission for users with Dataset access.

High-level Approach

The key idea is to completely remove the sensitive column from the model so that Explorers cannot access it, even with ad-hoc dimensions. We achieve this using a Query Model with AML If-else expression and string interpolation.

Here's how it works:

1. Create a Query Model that queries the original table but explicitly excludes the sensitive column from the SELECT statement
2. Use an AML expression to check the user's PII access and return either the real column name or a redacted string literal
3. Inject this expression into the Query Model's SQL via string interpolation

Implementation

Prerequisite

This section assumes you have already set up the pii_access user attribute and user groups as described in Step 1 and Step 2 above.

1. Create a Query Model that excludes the raw PII column

Instead of selecting all columns from your source table, explicitly list only the non-sensitive columns. Do not include the original email column, we'll add a safe, computed version later.

pii_users.model.aml

Model pii_users {

  ...

  dimension id {
    label: 'Id'
    type: 'number'
    definition: @sql {{ #SOURCE.id }};;
  }

  dimension first_name {
    label: 'First Name'
    type: 'text'
    definition: @sql {{ #SOURCE.first_name }};;
  }

  dimension last_name {
    label: 'Last Name'
    type: 'text'
    definition: @sql {{ #SOURCE.last_name }};;
  }

  // Other non-sensitive dimensions...

  query: @sql
    select
      id, sign_up_at, first_name, last_name, birth_date, gender, city_id
    from ecommerce.users
  ;;

}

Notice that email is not in the SELECT statement.

2. Define the PII logic using an AML expression

Create a constant that checks the user's pii_access attribute and returns either the real column name or a redacted string literal.

pii_users.model.aml

const pii =
  if (H.current_user.pii_access != 1) {
    '\'(redacted)\''
  } else {
    'email'
  }

Model pii_users {
  ...
}

• If the user doesn't have PII access, the expression injects a string literal '(redacted)'
• If the user has access, it injects the real column name email

3. Add the safe email into the Query Model via interpolation

Inject the pii expression into the Query Model's SQL and alias it as email.

pii_users.model.aml

const pii =
  if (H.current_user.pii_access != 1) {
    '\'(redacted)\''
  } else {
    'email'
  }

Model pii_users {

  dimension id {
    label: 'Id'
    type: 'number'
    definition: @sql {{ #SOURCE.id }};;
  }

  dimension email {
    label: 'Email'
    type: 'text'
    definition: @sql {{ #SOURCE.email }};;
  }

  // Other dimensions...

  query: @sql
    select
      id, sign_up_at, first_name, last_name, birth_date, gender, city_id,
      ${pii} as email
    from ecommerce.users
  ;;

}

This way:

• The original users.email never appears in the model's base SELECT
• The only email field that exists in pii_users is the safe one we created
• Explorers cannot access or reference the raw column, even with ad-hoc dimensions, because it isn't part of the model