Row filters and column masks#

Row filters and column masks limit what a query returns from a table. You create them once and select them in a policy. They don’t grant access on their own.

You can also manage row filters and column masks with the Control Plane API.

Row filters#

A row filter limits the rows that a query returns from a table. The filter is a SQL expression, and a row appears in the results only when the expression is true for that row. For example:

  • nationkey % 2 = 1 returns only the rows with an odd nationkey value.

  • region = 'east' returns only the rows where region is east.

  • market_segment = 'BUILDING' returns only the rows where market_segment is BUILDING.

A row filter applies to every table that a policy using it selects. If the filter uses a column that one of those tables doesn’t have, queries on that table fail.

Each row filter has an owner role. The filter expression runs with the privileges of the owner role, not the role that runs the query. For example, if the expression reads from another table in a subquery, the owner role needs access to that table. For more information, see Ownership.

Create a row filter#

  1. In the Control Plane UI, go to Access > Row filters.

  2. Click Create row filter.

  3. Enter a name of up to 30 characters, a description, and the filter expression.

  4. Click Create row filter.

You can also add a row filter while you create a policy. In the last step, click Add row filter, and then select an existing filter. To create a new filter, select Create new row filter, create the filter, and then select it.

To edit a row filter, open its menu in the list, select Edit row filter settings, make your changes, and click Save. To delete a row filter, open its menu and select Delete row filter. You can’t delete a row filter while a policy uses it. The Use count column of the list shows how many policies use each filter.

Column masks#

A column mask replaces the values in a column with a SQL expression. To use the column’s own value in the expression, refer to it as "@column". For example:

  • NULL returns NULL for every value.

  • 'REDACTED' returns the string REDACTED for every value.

  • lower(to_hex(sha256(to_utf8(cast("@column" as varchar))))) returns the SHA-256 hash of each value.

In a policy, a column mask applies to the columns that the policy selects. For example, a policy with the matching expression HAS_TAG(pii) and a column mask that returns 'REDACTED' redacts the columns tagged pii. If the tag is on a table, schema, or catalog, the mask applies to all of its columns.

Each column mask has an owner role. The mask expression runs with the privileges of the owner role, not the role that runs the query. For more information, see Ownership.

Mask types#

Each column mask has a type, which is the data type of the columns that it applies to. For each masked column, Starburst access control chooses one mask from the masks that apply to it, in this order:

  1. A mask with the same type as the column.

  2. A mask with a compatible type.

  3. A mask with the type ANY.

If none of the masks fits the column’s type, the column isn’t masked. To make sure that every selected column is masked, add a mask with the type ANY.

Starburst access control converts the result of the mask to the column’s data type. If the result can’t be converted, the column shows NULL.

Predefined column masks#

Starburst access control includes the following column masks, owned by the accountadmin role:

Name

Type

Description

hash

VARCHAR

Hash values with SHA-256.

mask_full_string

VARCHAR

Return all values as the string name is masked.

mask_integers

VARCHAR

Hide integer values.

mask_strings

VARCHAR

Hide string values.

null

ANY

Return all values as NULL.

show_first_4

VARCHAR

Hide all but the first four characters of each value.

show_last_4

VARCHAR

Hide all but the last four characters of each value.

Create a column mask#

  1. In the Control Plane UI, go to Access > Column masks.

  2. Click Create column mask.

  3. Enter a name of up to 30 characters, and select the column mask type.

  4. Enter a description and the column mask expression.

  5. Click Create column mask.

You can also add a column mask while you create a policy. In the last step:

  1. Click Apply to defined scope, and in Scope, select which of the policy’s scopes the mask applies to.

  2. Click Add column mask, and select an existing mask. To create a new mask, select Create new column mask, create the mask, and then select it.

To edit a column mask, open its menu in the list, select Edit column mask settings, make your changes, and click Save. To delete a column mask, open its menu and select Delete column mask. You can’t delete a column mask while a policy uses it. The Use count column of the list shows how many policies use each mask.