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:

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):

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)'
);;

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:

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:

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
);;