Elevate your data protection with Snowflake’s IS_DATABASE_ROLE_IN_SESSION()

In this blog, we explore the new IS_DATABASE_ROLE_IN_SESSION() context function, which is designed to leverage database roles for data protection across accounts. We’ll use this function in 2 governance scenarios to see how this function can be beneficial followed by some points to note.

Snowflake’s role-based access control is essential for improving security and managing data effectively. The IS_DATABASE_ROLE_IN_SESSION function plays an important role by verifying if a specific database role is part of the user’s role hierarchy during the current session. This function extends RBAC to database roles enabling simpler governance for data shared across accounts.

Let’s explore 2 governance scenarios, along with self-contained code that you can use to replicate these scenarios in your environment!

Governance Scenario I — Centralized database with policies

In the first governance scenario, a centralized database stores policies, which call the IS_DATABASE_ROLE_IN_SESSION context function. These policies can be applied to tables and views in other databases. To ensure data protection, database roles are created in the database where the data, such as tables and views resides.

The diagram above illustrates data setup for the 1st Governance Scenario.
A database called CENTRALIZED_POLICY_DB contains a row access policy (lock).
A database called ORDERS_DB contains the data table and the database roles (keys).

We start by setting up the centralized database with a basic row access policy that calls the IS_DATABASE_ROLE_IN_SESSION function, as follows-

-- governance scenario I
USE ROLE accountadmin;

-- setup the centralized database to store policies
CREATE OR REPLACE DATABASE centralized_policy_db;
CREATE OR REPLACE SCHEMA sch;

-- create row access policy
CREATE OR REPLACE ROW ACCESS POLICY centralized_policy_db.policy_sch.db_role_rls_policy 
AS (authz_role VARCHAR) RETURNS BOOLEAN ->
    IS_DATABASE_ROLE_IN_SESSION(authz_role)
;

Next, we set up the ORDERS_DB database that contains a table called ORDER_AUTHZ with 2 columns for order numbers and authorization role names. We also protect the ORDER_AUTHZ table using the row access policy from the centralized database. We add the row access policy to the AUTHZ_ROLE column which specifies the database roles allowed to see that particular order number.

-- setup ORDERS database 
CREATE OR REPLACE DATABASE orders_db;
CREATE OR REPLACE SCHEMA policy_sch;
CREATE OR REPLACE table order_authz (order_number NUMBER, authz_role VARCHAR)
  WITH ROW ACCESS POLICY centralized_policy_db.policy_sch.db_role_rls_policy 
  ON (authz_role);

We then insert some data into the table, where the authorization role names correspond to the database role names that are allowed to see the particular order ID details. In this case, we include 2 database roles

• FINANCE_DB_ROLE, that should see 2 order numbers — 101 and 303
• LEGAL_DB_ROLE, that should see 1 order number — 202

INSERT INTO orders_db.sch.order_authz VALUES 
  (101, 'FINANCE_DB_ROLE'), 
  (202, 'LEGAL_DB_ROLE'), 
  (303, 'FINANCE_DB_ROLE');

Initially, no rows are returned because no database roles have been granted to the role running this query.

-- returns 0 rows
SELECT * FROM orders_db.sch.order_authz;

Next, we create and grant the FINANCE_DB_ROLE database role. We should now be able to see 2 order numbers — 101 and 303

-- create and grant database roles
USE DATABASE orders_db;
CREATE OR REPLACE DATABASE ROLE finance_db_role;
GRANT DATABASE ROLE finance_db_role TO ROLE accountadmin;

-- returns 2 order numbers
SELECT * FROM orders_db.sch.order_authz;

Screenshot 1 — Granting the FINANCE_DB_ROLE database role allows SELECT query to show 2 order numbers.

Screenshot 2 — Similarly, we could also create and grant the LEGAL_DB_ROLE database role. This will allow us to see all 3 order numbers, as seen above.

In the above scenario we were able to use a policy from a centralized database to protect data in another database using database roles. Since we used the non-literal argument type for the IS_DATABASE_ROLE_IN_SESSION() function, we can create more database roles and add them to the table, without having to modify the policy body.

Governance Scenario II — Data sharing between accounts

Snowflake’s Data Sharing feature allows you to share selected objects in a database in your account with other Snowflake accounts. In this governance scenario, we share the ORDER_AUTHZ table in the ORDERS_DB database with another account. We extend the example from the above setup and re-use the same setup.

The diagram above illustrates the data setup for the 2nd Governance Scenario.
The left side of the diagram indicates the provider account and is re-purposed from the 1st Governance Scenario. The right side of the diagram represents the consumer account which mounts the shared database and is able to select from the shared protected table.

Without making any modifications to the existing governance policies, we seamlessly share policy protected data using the following code snippet. On the provider account, we create a new share and add another Snowflake account which would be the consumer account, in this case it is the CONSUMER_ACCOUNT account.

-- setup share
CREATE OR REPLACE SHARE consumer_share;
GRANT USAGE ON DATABASE orders_db TO SHARE consumer_share;
GRANT USAGE ON SCHEMA orders_db.sch TO SHARE consumer_share;
GRANT SELECT ON TABLE orders_db.sch.order_authz TO SHARE consumer_share;
ALTER SHARE consumer_share ADD ACCOUNTS=CONSUMER_ACCOUNT;

On the CONSUMER_ACCOUNT account, we mount the shared database and query the shared table -

-- Next, we use the following code snippet on the destination (consumer) account.
-- consumer account 
USE ROLE accountadmin;

-- mount database
CREATE OR REPLACE DATABASE orders_db_consumer FROM SHARE DATA_GOVERNANCE.consumer_share;

-- no results yet (db role not shared)
use database orders_db_consumer;
SELECT * FROM orders_db_consumer.sch.order_authz;

Initially, we notice no results when querying the table on the consumer side. This is because the database role has not been granted yet and the policy protected table always returns false, thus showing no data. On the provider account, we grant the database role to the share using the following script-

-- provider account
USE DATABASE orders_db;
GRANT DATABASE ROLE finance_db_role TO SHARE consumer_share;

On the consumer account we run the following command which shows us 2 rows out of 3. This is because we have only granted the FINANCE_DB_ROLE database role which can see 2 rows and not the LEGAL_DB_ROLE which can see 1 row.

-- after database role has been granted to share, should return 2 rows 
SELECT * FROM orders_db_consumer.sch.order_authz;

Screenshot 3 — After the provider grants the FINANCE_DB_ROLE database role to the consumer share. Querying the shared table shows us 2 rows that can be seen by the database role.

Above 2 scenarios highlight how we can leverage the IS_DATABASE_ROLE_IN_SESSION() function in governance policies to protect data within an account as well as seamlessly share data across accounts while maintaining data protection.

Points to note

We’ve discussed some interesting use cases and scenarios for database roles in the context of the IS_DATABASE_ROLE_IN_SESSION() function. Following are some details to note-

• When the function is called within a policy, it always resolves and evaluates the database roles with respect to the database where the policy protected table is created.
• The provider side shared database and the consumer side mounted database can have different names.
• The IS_DATABASE_ROLE_IN_SESSION() function can also take a literal argument to directly indicate a database role name.

Try it today

I hope this blog has given you insights into Snowflake’s database roles and how the IS_DATABASE_ROLE_IN_SESSION() function can be used in policies to effectively govern data across databases and accounts. All code used in the above Governance Scenario’s is self sufficient and can be re-created in Enterprise+ Snowflake accounts!