Built-in access control masks and filters#

You can use Dell Data Analytics Engine, powered by Starburst Enterprise platform (SEP)’s built-in access control to restrict what data users can see at the row and column level. By masking data and filtering rows, you can allow different users to run the same queries, but be prevented from viewing sensitive data. These data obfuscation features are applied to roles using SEP’s ‘built-in access control privileges.

There are two types of data obfuscation privileges you can apply to roles:

  • Column masks are applied on one or more columns in a table, hiding the selected values according to a configured expression. For example, you can configure a privilege with masks that displays all values of an address column as null, and only displays the first few digits of an ssn column.

    Column masks can be applied to tables, views, or materialized views. Materialized views do not inherit masks applied to the underlying table, and as such masks must be applied directly to the materialized view.

  • Row filters exclude rows from being returned by a query if they match one or more SQL expressions. For example, you can configure a privilege that allows users to run a SELECT query but not see rows in the result set where an is_privileged column is set to true.

    Row filters can be applied onto tables, views, or materialized views.

Create masks and filters#

To create a column mask or row filter, navigate to the Masks and filters page in the Starburst Enterprise web UI and select either the Masks or Row filters tab.

Column masks#

A column mask consists of an expression that describes how a column’s value should be displayed to affected users. The following column masks are available by default:

  • Hash: Encrypt values with an SHA-256 hash.

  • Mask integers: Hide integer values.

  • Mask strings: Hide string values.

  • Mask_Full_String: Return all values as the string name is masked.

  • Null: Return all values as null.

  • Show first 4: Hides all but the first four characters of each value.

  • Show last 4: Hides all but the last four characters of each value.

You can also create your own column masks by clicking the Create new mask button and entering the following information:

  • Mask name: A name for the column mask.

  • Expression: The output that appears to users when the mask is applied, written as a regular expression. You can write a string in double quotes such as "String contents" to mask all values as that string, or you can write a statement using @column to extract the value and wrap it in a series of functions and operators to customize the output that appears to the user.

    Warning

    Mask expressions must not change the column type. For example, a mask expression applied to a BIGINT type column that uses functions to produce a TEXT value is invalid and causes the query to fail.

  • Description: An optional description for the column mask.

Click Save changes to save your custom column mask.

Row filters#

A row filter consists of a SQL condition that excludes rows from appearing where the condition is met. For example, a row filter containing the expression country-code<>'US' excludes any rows from the result set where the country-code column has a value of US.

Multiple row filters can apply to the same query, and are evaluated independently. If a row in the result set matches any filters applied to the query, the row is excluded from the result set.

You can create a row filter by clicking the Create new filter button and entering the following information:

  • Filter name: A name for the row filter.

  • Expression: The condition that determines what rows to exclude from the query’s result set. This condition must be a SQL expression that returns a boolean value on applicable rows, where rows are excluded if this condition returns TRUE.

    To create a filter that excludes all other rows by default, you can write an expression with a not equal (!=, <>) operator to filter out all rows except those that match the expression.

  • Description: An optional description for the row filter.

Click Save changes to save your row filter.

Apply masks and filters to user queries#

Column masks and row filters are applied to user queries based on the privileges granted to them. Masks and filters are an optional privilege that can be added to table entities that grant access to the Select action on one or more columns. You can review what roles have been assigned any given mask or filter by selecting the Edit button for that mask or filter and reviewing the Applies to roles section.

Built-in access control applies filters and masks at different steps in the query processing sequence. Once SEP determines what privileges apply to the user executing the query, filters and masks are applied as follows:

  • Row filters are appended to the query statement before the resulting query is issued to the underlying data source.

  • Column masks are applied in SEP after data is returned, before returning the masked result set to the user. Masks can be applied to tables or views. Views inherit masks applied to the underlying table. Materialized views do not inherit column masks, so they must be applied directly to the materialized view.

For more information on creating privileges in built-in access control, read Built-in access control privileges.