Advanced Column-level Security Topics

This topic provides an introduction to two advanced concepts related to Dynamic Data Masking policies:

  1. Using Context Functions.

  2. Role hierarchy.

Column-level Security with Context Functions and Role Hierarchy

Column-level security supports using Context Functions in the conditions of the masking policy body to enforce whether a user has authorization to see data. To determine whether a user can see data in a given SQL statement, it is helpful to consider:

The current session

Masking policy conditions using CURRENT_ROLE target the role in use for the current session.

The executing role

Masking policy conditions using INVOKER_ROLE target the executing role in a SQL statement.

Role hierarchy

Determine if a specified role in a masking policy condition (e.g. ANALYST custom role) is a lower privilege role in the CURRENT_ROLE or INVOKER_ROLE role hierarchy. If so, then the role returned by the CURRENT_ROLE or INVOKER_ROLE functions inherits the privileges of the specified role. For more information about role hierarchy and privilege inheritance, see:

The following table shows common context functions in masking policies that target the current session, the executing role, and role hierarchy.

Context function

Description

CURRENT_ROLE

Returns the name of the role in use for the current session.

IS_ROLE_IN_SESSION

Returns TRUE if the role name passed in the argument is one of the activated roles in the session (i.e. the role returned by the CURRENT_ROLE function inherits the privileges of the specified role).

INVOKER_ROLE

Returns the name of the executing role.

IS_GRANTED_TO_INVOKER_ROLE

Returns TRUE if the role returned by the INVOKER_ROLE function inherits the privileges of the specified role in the argument.

INVOKER_SHARE

Returns the name of the share in use by the data consumer executing the query.

Using CURRENT_ROLE and IS_ROLE_IN_SESSION

A masking policy condition using CURRENT_ROLE targets the current session and is not affected by the execution context of the SQL statement.

Consider the following masking policy body:

case
  when current_role() in ('ANALYST') then val
  else '********'
end;

To determine whether a given user has authorization to see data in a column where this masking policy is set on that column, complete the following steps:

  1. Evaluate the masking policy conditions.

  2. Determine if the specified role is in the CURRENT_ROLE hierarchy.

  3. Run a test query to verify.

Step 1: Evaluate the Masking Policy Conditions

The following table summarizes the consequences of the masking policy body conditions.

Context

Sees unmasked data

Sees masked data

CURRENT_ROLE = ANALYST custom role.

CURRENT_ROLE is in the ANALYST custom role in hierarchy.

CURRENT_ROLE is not in the ANALYST custom role hierarchy.

Next, evaluate the role hierarchy.

Step 2: Determine if the Specified Role is in the CURRENT_ROLE Hierarchy

Assuming that the CURRENT_ROLE is not the ANALYST custom role, determine if the CURRENT_ROLE inherits the privileges granted to the ANALYST custom role.

Execute the following statement:

select is_role_in_session('ANALYST');

+-------------------------------+
| IS_ROLE_IN_SESSION('ANALYST') |
+-------------------------------+
| FALSE                         |
+-------------------------------+

Since Snowflake returns FALSE, the CURRENT_ROLE does not inherit privileges granted to the ANALYST custom role. Therefore, based on the masking policy body in this example, the user should see a fixed mask value.

Step 3: Run a Test Query to Verify

Execute a query on the column that has the masking policy in this example applied to that column to verify that the user sees a fixed masked value.

use role ANALYST;

select <column> from <table_or_view>;

Using INVOKER_ROLE

A masking policy condition using INVOKER_ROLE targets the execution context of the SQL statement.

The following table summarizes the execution context and the value that INVOKER_ROLE returns in a masking policy condition:

Query where the masking policy applies

Value Returned by INVOKER_ROLE

View

View owner role.

UDF

UDF owner role.

Stored procedure with callers right

CURRENT_ROLE.

Stored procedure with owners right

Stored procedure owner role.

Task

Task owner role.

Stream

The role that queries a given stream.

Consider the following masking policy body that is applied to a single view on a table:

case
  when invoker_role() in ('ANALYST') then val
  else '********'
end;

To determine whether a given user running a query on the column has authorization to see data, complete the following steps:

  1. Evaluate the masking policy conditions.

  2. Determine if the specified role owns the view.

  3. Run a test query to verify.

Step 1: Evaluate the Masking Policy Conditions

The following table summarizes the consequences of the masking policy body conditions applied to a view column.

Context

Sees unmasked data

Sees masked data

ANALYST custom role is the view owner role.

ANALYST custom role is not the view owner role.

Next, determine if the ANALYST custom role owns the view.

Step 2: Determine if the ANALYST Owns the View

To determine if the ANALYST custom role owns the view, execute the following statement:

show grants to role analyst;

If the ANALYST custom role owns the view, then a query on the view column should result in unmasked data.

If the ANALYST custom role does not own the view, masked data should be seen.

Step 3: Run a Test Query to Verify

Execute a query on the view column to determine whether the ANALYST custom role sees masked or unmasked data.

use role ANALYST;

select <column> from <view_name>;

Using IS_GRANTED_TO_INVOKER_ROLE

The IS_GRANTED_TO_INVOKER_ROLE function can be passed into a masking policy body as part of a condition. When the function evaluates to TRUE, the role in the function argument is in the INVOKER_ROLE hierarchy.

Consider the following masking policy body that is applied to a view column of social security numbers (SSNs):

case
  when is_granted_to_invoker_role('PAYROLL') then val
  when is_granted_to_invoker_role('ANALYST') then regexp_replace(val, '[0-9]', '*', 7)
  else '*******'
end;

To determine whether a given user running a query on the view column has authorization to see data, complete the following steps:

  1. Evaluate the masking policy conditions.

  2. Determine if the specified role is in the view owner role hierarchy.

  3. Run a test query to verify.

Step 1: Evaluate the Masking Policy Conditions

The following table summarizes the consequences of the masking policy body conditions applied to a view column and viewing data in the view column.

Context

Unmasked data

Partially masked data

Masked data

PAYROLL custom role is in the view owner role hierarchy.

ANALYST custom role is in the view owner role hierarchy.

Neither the PAYROLL nor ANALYST custom roles . are in the view owner hierarchy.

Step 2: Determine if the Specified Role is in the View Owner Role Hierarchy

If either the PAYROLL or ANALYST custom roles are in the view owner hierarchy, then executing a SHOW GRANTS command on the view owner role can verify the role hierarchy. For example:

show grants to role <view_owner_role>;

The outputs of the SQL statement will state whether the view owner role has been granted either the PAYROLL or ANALYST custom roles.

Step 3: Run a Test Query to Verify

Execute a query on the column that has the masking policy in this example applied to that column to verify how the user sees data in the view column.

use role PAYROLL;

select <column> from <view>;

use role ANALYST;

select <column> from <view>;