Row and Column-based Security

To control the data returned to users, you can set row-based permissions and column masking, together or separately, using the SYSADMIN.setPermissions system procedure.

Row-based Permissions

A permission for a fully qualified table, view, or procedure may specify a condition. Here are some points to keep in mind:

A condition is always applied, not just at the user query level;
Tables and views referenced via subqueries will still have row-based filters and column masking applied to them;
The condition can be any valid SQL statement referencing the columns of the table or view;
The condition will act as a row-based filter and as a checked constraint for INSERT/UPDATE operations;
The condition will be present regardless of how the table or view is used in a query (via a union, join, etc.).

As a condition is applied conjunctively to the UPDATE/DELETE/SELECT/WHERE clauses against the specified resource, those queries will only affect the subset of rows that pass the condition. Inserts and updates against affected physical tables are further validated so that the INSERT/CHANGE values comply with the condition (evaluate to TRUE) for the INSERT/UPDATE to succeed. You can disable the INSERT/UPDATE constraint check by setting the condition constraint flag to FALSE.

Currently, conditions on source tables acting as check constraints must not contain correlated subqueries.

Across multiple applicable roles, if more than one condition applies to the same resource, the conditions will be accumulated disjunctively via OR. This means that granting permission with the TRUE condition will allow users with this role to see all rows of the given resource.

Handling of NULL values is up to the implementer of the specific data role and may require ISNULL checks to ensure that NULL values are allowed when a column is nullable.

Here are some examples of row-based permissions setting:

1. In this example, we ask to show rows of test_schema.test_view1 view if col1 > 10:

SQL

CALL "SYSADMIN.setPermissions"(
    "role_name" => 'user-role-1',
    "resourceName" => 'test_schema.test_view1',
    "permissions" => 'CRUDEAL',
    "condition" => 'col1 > 10'
);;

2. In this example, we ask to show rows of test_tables_pg.test_d if MOD(d, 100) IN (SELECT a FROM test_tables_pg.test_a):

SQL

CALL "SYSADMIN.setPermissions"(
    "role_name" => 'user-role-1',
    "resourceName" => 'test_tables_pg.test_d',
    "permissions" => 'CRUDEAL',
    "condition" => 'MOD(d, 100) IN (SELECT a FROM test_tables_pg.test_a)'
);;

Row-based Permissions and Materialization

Row-based permissions apply to materialized tables only if those permissions are assigned directly to the object being materialized. Any row-based permissions assigned to parent objects are ignored because a materialized table functions as a static snapshot created on behalf of the user executing the materialization. This snapshot fully replaces not just the object being materialized, but the entire branch of the query plan that includes all underlying tables and views referenced in the view definition.

For example, assume we have a role (ROLE_A), with a row-level permission that returns only rows where col_a is greater than 10:

SQL

CALL "SYSADMIN.setPermissions"(
"role_name" => 'ROLE_A',
"resourceName" => 'test_tables.base_table',
"permissions" => 'CRUDEAL',
"condition" => 'col_a > 10'
) ;;

in the following table:

SQL

CREATE TABLE "test_tables.base_table" ("col_a" INTEGER) ;;

A user (USER_A), who has ROLE_A, creates the following view:

SQL

CREATE VIEW "views.view_child" AS SELECT COUNT("col_a") FROM "test_tables.base_table" ;;

If USER_A queries view_child without materialization involved, the row-level permission is enforced in the query plan, ensuring only rows where col_a > 10 are included in the result. However, if an admin user materializes view_child, the system creates a materialized table on behalf of the admin, containing all rows from base_table, ignoring the row-based permissions applied to ROLE_A. Since the materialized table fully replaces the corresponding branch of the query plan, any objects referenced in the original view definition, including base_table, are no longer considered when querying view_child. As a result, row-based permissions assigned to base_table (or any other objects in the original query definition) are not applied to the materialized view.

Column Masking

A permission for a fully qualified table, view, or procedure column may also specify a mask and optionally a condition. When the query is submitted, the roles are checked, and the relevant mask or condition information is combined to form a searched case expression to mask the values that would have been returned according to the access. The resulting mask is always applied, not just at the user query level. The condition and expression can be any valid SQL statement referencing the table's columns, view, or procedure.

Column masking is applied only against a SELECT statement and in the second turn, after row-based security. However, since both views and source tables may have row and column-based security, the actual view-level masking may take place on top of source-level masking. If the condition is specified along with the mask, then the effective mask expression affects only a subset of the rows: CASE WHEN condition THEN mask ELSE column. Otherwise, the condition is assumed to be TRUE, meaning that the mask applies to all rows.

If multiple roles specify a mask against a column, the mask order argument will determine their precedence from highest to lowest as part of a larger searched case expression. For example, a mask with the default order of 0 and a mask with an order of 1 would be combined as CASE WHEN condition1 THEN mask1 ELSE CASE WHEN condition0 THEN mask0 ELSE column END END.

Here are some examples:

1. In this example, we are asking to show '1111' instead of col2 value if col2 > 3:

SQL

CALL "SYSADMIN.setPermissions"(
    "role_name" => 'user-role-1',
    "resourceName" => 'test_schema.colMask_view1',
    "permissions" => 'CRUDEAL'
);;

CALL "SYSADMIN.setPermissions"(
    "role_name" => 'user-role-1',
    "resourceName" => 'test_schema.colMask_view1.col2',
    "permissions" => 'CRUDEAL',
    "condition" => 'col2 > 3',
    "mask" => 1111,
    "maskOrder" => 1
);;

2. This example illustrates mask order: we are asking to show col2 as CASE WHEN col2 <= 2 THEN 2222 ELSE CASE WHEN col2 >= 2 THEN 1111 ELSE col2 END END:

SQL

CALL "SYSADMIN.setPermissions"(
    "role_name" => 'user-role-1',
    "resourceName" => 'test_schema.colMask_view1',
    "permissions" => 'CRUDEAL'
);;

CALL "SYSADMIN.setPermissions"(
    "role_name" => 'user-role-1',
    "resourceName" => 'test_schema.colMask_view1.col2',
    "permissions" => 'CRUDEAL',
    "condition" => 'col2 >= 2',
    "mask" => 1111,
    "maskOrder" => 1
);;

CALL "SYSADMIN.setPermissions"(
    "role_name" => 'user-role-2',
    "resourceName" => 'test_schema.colMask_view1.col2',
    "permissions" => 'CRUDEAL',
    "condition" => 'col2 <= 2',
    "mask" => 2222,
    "maskOrder" => 2
);;