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 = 1returns only the rows with an oddnationkeyvalue.region = 'east'returns only the rows whereregioniseast.market_segment = 'BUILDING'returns only the rows wheremarket_segmentisBUILDING.
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#
In the Control Plane UI, go to Access > Row filters.
Click Create row filter.
Enter a name of up to 30 characters, a description, and the filter expression.
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:
NULLreturnsNULLfor every value.'REDACTED'returns the stringREDACTEDfor 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:
A mask with the same type as the column.
A mask with a compatible type.
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 values with SHA-256. |
|
|
Return all values as the string |
|
|
Hide integer values. |
|
|
Hide string values. |
|
|
Return all values as |
|
|
Hide all but the first four characters of each value. |
|
|
Hide all but the last four characters of each value. |
Create a column mask#
In the Control Plane UI, go to Access > Column masks.
Click Create column mask.
Enter a name of up to 30 characters, and select the column mask type.
Enter a description and the column mask expression.
Click Create column mask.
You can also add a column mask while you create a policy. In the last step:
Click Apply to defined scope, and in Scope, select which of the policy’s scopes the mask applies to.
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.