Advanced Column-level Security topics¶

This topic provides an introduction to two advanced concepts related to Column-level Security masking policies:

Role hierarchy.
Using multiple Context functions.

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.

Database and schema:

If you specify the CURRENT_DATABASE or CURRENT_SCHEMA function in the body of a masking or row access policy, the function returns the database or schema that contains the protected table, not the database or schema in use for the session.

The executing role:

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

Role hierarchy:

If role hierarchy is necessary in the policy conditions, use IS_ROLE_IN_SESSION.

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 session, the executing role, and role hierarchy.

Context function	Description
CURRENT_ROLE	Returns the name of the role in use for the current session.
CURRENT_DATABASE	In a policy body, returns the database that contains the table that is protected by the masking policy.
CURRENT_SCHEMA	In a policy body, returns the schema that contains the table that is protected by the masking policy.
IS_ROLE_IN_SESSION	Returns TRUE if the user’s current role in the session (i.e. the role returned by CURRENT_ROLE) 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 based on the context in which the function is called.
INVOKER_SHARE	Returns the name of the share that directly accessed the table or view where the INVOKER_SHARE function is invoked.

Use 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.

If role activation and role hierarchy is necessary in the policy conditions, use IS_ROLE_IN_SESSION.

Consider the following masking policy body:

CREATE OR REPLACE MASKING POLICY mask_string AS
(val string) RETURNS string ->
CASE
  WHEN CURRENT_ROLE() IN ('ANALYST') THEN val
  ELSE '********'
END;

Copy

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:

Evaluate the masking policy conditions.
Determine if the specified role is in the CURRENT_ROLE hierarchy.
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');

Copy

+-------------------------------+
| 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 * FROM mydb.mysch.mytable;

Copy

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

Context	Evaluated role
User	CURRENT_ROLE
Table	CURRENT_ROLE.
View	View owner role.
UDF	UDF owner role.
Stored procedure with caller’s right	CURRENT_ROLE.
Stored procedure with owner’s 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:

CREATE OR REPLACE MASKING POLICY mask_string AS
(val string) RETURNS string ->
CASE
  WHEN INVOKER_ROLE() IN ('ANALYST') THEN val
  ELSE '********'
END;

Copy

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

Evaluate the masking policy conditions.
Determine if the specified role owns the view.
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 role owns the view¶

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

SHOW GRANTS OF ROLE analyst;

Copy

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 * FROM mydb.mysch.myview;

Copy

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

CREATE OR REPLACE MASKING POLICY mask_string AS
(val string) RETURNS string ->
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;

Copy

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

Evaluate the masking policy conditions.
Determine if the specified role is in invoker role hierarchy. For example, if the policy is set on a view, the specified role must be in the view owner role hierarchy to return TRUE. For details, see the usage notes.
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;

Copy

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 * FROM mydb.mysch.myview;

USE ROLE analyst;

SELECT * FROM mydb.mysch.myview;

Copy

Combine CURRENT_ROLE and INVOKER_ROLE in masking policies¶

Snowflake supports creating a single masking policy to differentiate the role in use for the session that executes a query (i.e. CURRENT_ROLE) and the object owner executing a query (e.g. view owner, INVOKER_ROLE). Uses cases of this type are typically more complicated than simply determining a set of values to mask and a relatively small audience (e.g. users with the analyst custom role) that can see unmasked values.

Hashing, cryptographic, and encryption functions in masking policies¶

Hashing and cryptographic/checksum can be used in masking policies to mask sensitive data.

Before implementing any of these functions in a masking policy, it is important to consider whether your use case with these functions involve JOIN operations. Under certain masking policy implementations, creative JOIN operations that involve tables and views can lead to reverse engineering the masked value to its true value based upon the following limitation:

It is possible that collisions may occur because there may not be a 1:1 representation of the actual value (i.e. input) and the hashed, cryptographic, or checksum value based on the total number of values (i.e. output, the range of values) to transform.

A 1:1 representation is more likely to occur until the total number of input values reaches the square root of the output values to transform.

For example, if the output values to hash is 144, then it is reasonable to expect that the first 12 values (i.e. 144^(1/2) – the square root of 144) will be unique and that collisions might occur for the remaining 132 values. Since this limitation and its consequence is possible, it is advisable to never use hashed, cryptographic, or checksum functions in masking policies whose values may be used in JOIN operations.

Tip

If the masking policy use case prioritizes collision avoidance for enhanced security, implement External Tokenization. Tokenization does not result in collisions because there is always a 1:1 representation of the input and output values.

If tokenization is not possible, one possible workaround is to implement a masking policy to differentiate between the session role executing a query (i.e. CURRENT_ROLE) and the object owner executing a query (i.e. INVOKER_ROLE).

For example, the following masking policy assumes two different custom roles, CSR_EMPL_INFO and DBA_EMPL_INFO, to regulate access to employee information.

CREATE OR REPLACE MASKING POLICY mask_string AS
(val string) RETURNS string ->
CASE
    WHEN CURRENT_ROLE() IN ('CSR_EMPL_INFO') THEN HASH(val)
    WHEN INVOKER_ROLE() IN ('DBA_EMPL_INFO') THEN val
    ELSE null
END;

Copy

If the policy is applied to the table, then the policy will be inherited to any view created from the table. If the custom role dba_empl_info owns the view created from this table (i.e. has the OWNERSHIP privilege on the view), then only users with this custom role can see the actual values if querying the view. Users with the csr_empl_info custom role always see a hashed value whether query is made on the table or view. All other users see NULL.