Share data protected by a policy

This preview enables data sharing consumers to use a shared database role to access shared data protected by a masking policy or a row access policy.

Overview

A data sharing provider can use the IS_DATABASE_ROLE_IN_SESSION context function in the conditions of a masking policy or a row access policy to allow a data sharing consumer to access shared data that is protected by either of these policies. This provides more options to the provider to share data and allows the consumer to access sensitive data that the provider makes available.

The provider can choose to group the policy and shared objects in a single database or in different databases. When the policies and protected tables are in different databases, the provider must:

  • Share both databases to the consumer account.

  • Create the database role in the same database as the policy.

  • Grant the database role to the share containing the policy.

When the consumer creates a database from the share, the database roles in the share are granted to the role that creates the database from the share. This allows the account role in the consumer account to meet the policy conditions that specify the database role and access the shared data.

To access the shared data protected by the policy, the consumer must specify the database containing the shared database role to make the shared database role active in the current session. In this context, making the database role active means that the database role is available in the role hierarchy of the current role for the user. If you do not specify this shared database, users in the consumer account cannot access shared data that is protected by a policy. You can specify this database using either of the following options:

  • Activate the database in the session with the USE <object> command or select the database in the worksheet. For example:

    USE DATABASE mounted_db;
    
    Copy

    Note that mounted_db is the name of the database the consumer creates from the share.

  • For a specific query, use the fully qualified name of the object that is in the same database as the database role. For example:

    SELECT * FROM mounted_db.myschema.mytable;
    
    Copy

Call the function

There are two different ways to specify arguments in the IS_DATABASE_ROLE_IN_SESSION context function: a string literal or a nonliteral (i.e. column name).

  • When you specify a database role as a string in the IS_DATABASE_ROLE_IN_SESSION context function, the result of calling the function depends on how the function is called. For example:

    • In a worksheet, Snowflake looks at the database that is in use for the session or the database that is specified in the query. This applies to both the provider account and the consumer account.

    • In a policy, UDF, or view, Snowflake looks at the database where the policy is defined when the policy is shared. When the policy is not shared, Snowflake returns an error if the database role lookup results in the database role being defined in a different database.

      A Role Context Function cannot reference a DB Role in another database.
      
      Copy
  • When you specify a column name as the argument in the IS_DATABASE_ROLE_IN_SESSION context function:

    • If a table query calls the function, the column maps to the table identifier of the table containing the column. Snowflake then looks at the database roles in the database containing the table. For example, to specify the AUTHZ_ROLE (i.e. authorized role) column as the argument:

      SELECT * FROM mydb.myschema.t WHERE IS_DATABASE_ROLE_IN_SESSION(AUTHZ_ROLE);
      
      Copy
    • If a masking policy, row access policy, or UDF calls the function, the lookup occurs in the database that contains the policy or the UDF.

General workflow

Sharing policy-protected data with the IS_DATABASE_ROLE_IN_SESSION function in the policy requires the same steps to create a policy to call the function and share data. To summarize:

  1. The provider creates an account role.

  2. The provider creates a policy and sets the policy on a table or column.

  3. The provider tests the policy with the account role.

  4. The provider creates a database role and tests the policy with the database role.

  5. The provider creates a share and grants privileges to the share, including granting the database role to the share.

  6. The consumer creates a database from the share (i.e. the mounted database).

  7. The consumer queries the shared object that is protected by the policy.

Example: All objects in the same database

In this example, the database roles, masking policy, and the protected table are all in the same database named mydb.

For reference:

  • The database roles are mydb.analyst and mydb.support.

  • The masking policy is defined as follows:

    CREATE OR REPLACE MASKING POLICY mydb.policies.email_mask
      AS (val string) RETURNS string ->
      CASE
        WHEN IS_DATABASE_ROLE_IN_SESSION('MYDB.ANALYST')
          THEN val
        WHEN IS_DATABASE_ROLE_IN_SESSION('MYDB.SUPPORT')
          THEN REGEXP_REPLACE(val,'.+\@','*****@')
        ELSE '********'
      END
      COMMENT = 'use database role for shared data'
      ;
    
    Copy
  • The EMAIL column is in a table named mydb.tables.empl_info and the masking policy is set on this column.

Complete the following steps to share the database mydb and allow the consumer to use the shared database role to query the shared data protected by the shared masking policy. These steps assume the provider has already tested the masking policy on the EMAIL column with their account roles and database roles.

  1. In the provider account, execute the CREATE SHARE command to create a share for the mydb.analyst_share database role:

    USE ROLE r1;
    CREATE SHARE analyst_share;
    
    Copy
  2. Grant privileges to the share. The same privileges are required for each share:

    USE ROLE r1;
    GRANT USAGE ON DATABASE mydb TO SHARE analyst_share;
    GRANT USAGE ON SCHEMA mydb.policies TO SHARE analyst_share;
    GRANT USAGE ON SCHEMA mydb.tables TO SHARE analyst_share;
    GRANT SELECT ON TABLE mydb.tables.empl_info TO SHARE analyst_share;
    GRANT DATABASE ROLE mydb.analyst TO SHARE analyst_share;
    
    Copy
  3. Add the consumer account to the share:

    ALTER SHARE analyst_share ADD ACCOUNTS = consumer_account;
    
    Copy
  4. In the consumer account, create the account role r1 and grant privileges to this role to import the share:

    USE ROLE ACCOUNTADMIN;
    CREATE ROLE r1;
    
    GRANT USAGE ON WAREHOUSE my_warehouse TO ROLE r1;
    GRANT CREATE DATABASE ON ACCOUNT TO ROLE r1;
    GRANT IMPORT SHARE ON ACCOUNT TO ROLE r1;
    GRANT ROLE r1 TO ROLE ACCOUNTADMIN;
    
    Copy
  5. Import the share:

    USE ROLE r1;
    CREATE DATABASE mounted_db FROM SHARE provider_account.analyst_share;
    
    Copy
  6. Verify the database role is in session:

    USE DATABASE mounted_db;
    USE SCHEMA mounted_db.tables;
    
    SELECT IS_DATABASE_ROLE_IN_SESSION('mounted_db.analyst');
    
    Copy

    The SELECT statement should return True.

  7. Query the protected table:

    SELECT * FROM empl_info;
    
    Copy

    The SELECT statement should return the unmasked email addresses.

  8. Grant the database roles to account roles so that users with these roles can query the protected table and view data based on the masking policy definition.

    After repeating the previous two steps, a user that is granted the mydb.support database role should see a partially masked email address.

Example: Masking policy and protected data in different databases

When the policy and the protected table are in different databases, both databases must be shared with the consumer.

For example:

  • mydb1 contains the masking policy and the mydb1.analyst database role. You must group the policy and the database role in the same database.

  • mydb2 contains the table named mydb2.tables.empl_info, which contains the EMAIL column. The masking policy is set on this column.

The provider follows the same procedure as the previous example in terms of creating a share, granting privileges to the share, and granting the database role to the share.

The consumer follows the same procedure as the previous example in terms of creating a database from the share. However, the consumer must have the database containing the policy in use to activate the database role. Then the consumer can query the protected table by specifying the fully qualified name of the table.

  1. In the provider account, execute the CREATE SHARE command to create a share for each database:

    USE ROLE r1;
    CREATE SHARE analyst_policy_share;
    CREATE SHARE analyst_table_share;
    
    Copy
  2. Grant privileges to the share named analyst_policy_share:

    USE ROLE r1;
    GRANT USAGE ON DATABASE mydb1 TO SHARE analyst_policy_share;
    GRANT USAGE ON SCHEMA mydb1.policies TO SHARE analyst_policy_share;
    GRANT DATABASE ROLE mydb1.analyst TO SHARE analyst_policy_share;
    
    Copy
  3. Grant privileges to the share named analyst_table_share:

    USE ROLE r1;
    GRANT USAGE ON SCHEMA mydb2.tables TO SHARE analyst_table_share;
    GRANT SELECT ON TABLE mydb2.tables.empl_info TO SHARE analyst_table_share;
    
    Copy
  4. Add the consumer account to the share:

    ALTER SHARE analyst_policy_share ADD ACCOUNTS = consumer_account;
    ALTER SHARE analyst_table_share ADD ACCOUNTS = consumer_account;
    
    Copy
  5. In the consumer account, create the account role r1 and grant privileges to this role to import the share:

    USE ROLE ACCOUNTADMIN;
    CREATE ROLE r1;
    
    GRANT USAGE ON WAREHOUSE my_warehouse TO ROLE r1;
    GRANT CREATE DATABASE ON ACCOUNT TO ROLE r1;
    GRANT IMPORT SHARE ON ACCOUNT TO ROLE r1;
    GRANT ROLE r1 TO ROLE ACCOUNTADMIN;
    
    Copy
  6. Import each share:

    USE ROLE r1;
    CREATE DATABASE mounted_db1 FROM SHARE provider_account.analyst_policy_share;
    CREATE DATABASE mounted_db2 FROM SHARE provider_account.analyst_table_share;
    
    Copy
  7. Verify the database role is in session:

    USE DATABASE mounted_db1;
    USE SCHEMA mounted_db1.policies;
    
    SELECT IS_DATABASE_ROLE_IN_SESSION('mounted_db1.analyst');
    
    Copy

    The SELECT statement should return True.

  8. Query the protected table:

    SELECT * FROM mounted_db2.tables.empl_info;
    
    Copy

    The SELECT statement should return the unmasked email addresses.

Example: Row access policy without mapping table

In this example, the row access policy calls the IS_DATABASE_ROLE_IN_SESSION function to lookup the role name in the AUTHZ_ROLE (i.e. authorized role) column. Note the nonliteral syntax and that function lookup occurs in the database that contains the policy. In this scenario, the mapping table should be in the same database as the row access policy.

Create the policy:

CREATE OR REPLACE ROW ACCESS POLICY rap_authz_role AS (authz_role string)
RETURNS boolean ->
IS_DATABASE_ROLE_IN_SESSION(authz_role);
Copy

Add the policy to a table:

ALTER TABLE allowed_roles
  ADD ROW ACCESS POLICY rap_authz_role ON (authz_role);
Copy

The provider can choose to share objects in a single database or in multiple databases as shown in the masking policy examples. The consumer follows the same procedure to create a database from a share for each database that the provider makes available.

Example: Row access policy with mapping table

In this example, the row access policy calls the IS_DATABASE_ROLE_IN_SESSION function to look up the authorized role from a mapping table column called ROLE_NAME. Note the nonliteral syntax and that function lookup occurs in the database that contains the policy. In this scenario, the mapping table must be in the same database as the row access policy. After creating the policy, add the policy to the table containing the AUTHZ_ROLE column.

Create the policy:

CREATE OR REPLACE ROW ACCESS POLICY rap_authz_role_map AS (authz_role string)
RETURNS boolean ->
EXISTS (
  SELECT 1 FROM mapping_table m
  WHERE authz_role = m.key AND IS_DATABASE_ROLE_IN_SESSION(m.role_name)
);
Copy

Add the policy to a table:

ALTER TABLE allowed_roles
  ADD ROW ACCESS POLICY rap_authz_role_map ON (authz_role);
Copy

The provider can choose to share objects in a single database or in multiple databases as shown in the masking policy examples. The consumer follows the same procedure to create a database from a share for each database that the provider makes available.