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.

Implementation

The key idea is to make the dimension's SQL definition dynamic based on user attributes. We achieve this using AML If-else expression and string interpolation directly in a Table Model.

Here's how it works:

Create a user attribute to determine PII access for each user
Create a user group and assign the attribute to grant access to specific users
Use an AML expression to check the user's PII access and return either the actual column reference or a redacted string literal
Inject this expression into the dimension's SQL definition via string interpolation

Step 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.

Step 2: Create user group and assign attribute

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

Add all manager-level users to this group.

Step 3: Define the PII logic using an AML expression

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

users.model.aml
const pii =
  if (H.current_user.pii_access != 1) {
    '\'(redacted)\''
  } else {
    '{{ #SOURCE.email }}'
  }

Model users {

  type: 'table'
  label: 'Users'
  description: ''
  data_source_name: 'demodb'

  ...

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

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

  // Other dimensions...

  table_name: 'ecommerce.users'
}

If the user doesn't have PII access, the expression returns a string literal '(redacted)'
If the user has access, it returns {{ #SOURCE.email }} which references the actual column

Step 4: Use the expression in the Table Model dimension

Apply the constant directly in the dimension's SQL definition using string interpolation.

users.model.aml
const pii =
  if (H.current_user.pii_access != 1) {
    '\'(redacted)\''
  } else {
    '{{ #SOURCE.email }}'
  }

Model users {

  type: 'table'
  label: 'Users'
  description: ''
  data_source_name: 'demodb'

  ...

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

  dimension email {
    label: 'Email'
    type: 'text'
    definition: @sql ${pii};;
  }

  // Other dimensions...

  table_name: 'ecommerce.users'
}

Result

Done. Now when a user explores the email field, 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.

This way:

The dimension's SQL definition is dynamically generated based on the user's pii_access attribute
For users without PII access, the SQL resolves to just '(redacted)'
For users with PII access, the SQL resolves to {{ #SOURCE.email }} which fetches the actual column value
This approach works for all users, including those with Dataset access

Introduction​

Use Case​

Implementation​

Step 1: Create user attribute pii_access​

Step 2: Create user group and assign attribute​

Step 3: Define the PII logic using an AML expression​

Step 4: Use the expression in the Table Model dimension​

Result​