Snowflake: Row Access Policy with Hierarchical Roles

“Row Access Policy with Hierarchical Roles” is a mouthful! What in the world am I talking about? I’ll start with hierarchical roles since most organizations have some hierarchical people reporting structure. Next, I’ll discuss Snowflake’s row access policy and how it applies to hierarchical roles.

Hierarchical Roles

Snowflake uses role-based access control (RBAC) for object security. A role itself is an object to which privileges are granted and in turn a role can be assigned to a user. Roles can also be assigned to other roles therefore allowing “parent” roles to inherit the privileges of their “children”.

When designing a security model for Snowflake, it is imperative to understand and balance data security needs with the relationships between users who access the data. What data access privileges are common and could be inherited through hierarchical data roles in Snowflake?

Row Access Policy

Snowflake utilizes row access policies to allow data admins to set row-level security on data. Applying a row access policy on a table or view limits data returned by queries according to the definition of the row access policy. Policies can be as simple or complex as needed for the situation. See the Snowflake doc for more details.

Note: Row Access Policies are only available on Snowflake Enterprise Edition and above.

Sales Org Example

Imagine a sales organization where the sales manager has two salespersons reporting to them. Each salesperson is responsible for one or more sales regions. Each salesperson should only access the data for their regions; however, the sales manager should have access to all regions. This sales org is an example of where a row access policy on a global view of all data would be handy to limit the data for each user.

Business Requirement for Data Access

The “non-code” definition for how to apply the data access for this example is as follows:

1. Only members of the sales org can see sales data.
2. The Americas/Europe sales team can only see data for the AMERICA and EUROPE regions.
3. The non-America/Europe sales team can see data for all regions other than AMERICA and EUROPE.
4. Sales Managers can see data for all regions.

Creating the Sales Org Roles in Snowflake

To create and apply a row access policy, I’ll first need the roles defined in my Snowflake instance. I’ll need a sales manager role and two salesperson roles that are children of the sales manager role. Additionally, to apply those “common” privileges, I’ll need a generic role that is a shared child of both salesperson roles. Data object privileges will be applied to the shared child role which will then be inherited by all the parent roles. 

/*CREATE NEW ROLES*/
USE ROLE SECURITYADMIN;

--SALES MANAGER
CREATE ROLE SALES_MGR;
GRANT ROLE SALES_MGR TO ROLE SYSADMIN;

--AMERICA/EUROPE SALES
CREATE ROLE AM_EU_SALES;
GRANT ROLE AM_EU_SALES TO ROLE SALES_MGR;

--NON AMERICA/EUROPE SALES
CREATE ROLE NON_AM_EU_SALES;
GRANT ROLE NON_AM_EU_SALES TO ROLE SALES_MGR;

--CHILD GRANT FOR ACCESS
CREATE ROLE SNOW_SALES;
GRANT ROLE SNOW_SALES TO ROLE AM_EU_SALES;
GRANT ROLE SNOW_SALES TO ROLE NON_AM_EU_SALES;

Apply Permissions to Data Objects

With the roles created as shown above, the next step is to grant the minimum privileges needed to query the data in the sales data view. I am reusing the VW_SALES_DATA from my Streamlit demo for this example.

--GRANT PERMISSIONS
/*VWH*/
USE ROLE ACCOUNTADMIN;
GRANT USAGE ON WAREHOUSE ST_DEMO_XS_WH TO ROLE SNOW_SALES;

/*SALES DATA VIEW*/
USE ROLE ST_DEMO_ROLE; --> DATA OWNER ROLE
GRANT USAGE ON DATABASE STREAMLIT_DB TO ROLE SNOW_SALES;
GRANT USAGE ON SCHEMA STREAMLIT_DATA TO ROLE SNOW_SALES;
GRANT SELECT ON STREAMLIT_DB.STREAMLIT_DATA.VW_SALES_DATA TO ROLE SNOW_SALES;

Create and Apply the Row Access Policy

Now that roles and permissions are all set, I’m ready to create my row access policy and add it to the sales data view. Referencing the business requirements above, I see that I will need to limit the data to only the sales org and that the REGION_NAME column will be how the data is restricted. Also, since I have applied hierarchical roles to the sales org in Snowflake, I can use the function IS_ROLE_IN_SESSION to take advantage of the hierarchical roles.

The IS_ROLE_IN_SESSION function returns TRUE if the role name passed to it is in the current hierarchical chain of the role executing the query, otherwise, it will return false. The function validates using both primary and secondary roles to determine inheritance. Since the SALES_MGR role inherits both the AM_EU_SALES and NON_AM_EU_SALES, the IS_ROLE_IN_SESSION function when executed by the SALES_MGR role would return TRUE for both AM_EU_SALES and NON_AM_EU_SALES.

USE ROLE SALES_MGR;
SELECT
    CURRENT_ROLE(),
    IS_ROLE_IN_SESSION('AM_EU_SALES'),
    IS_ROLE_IN_SESSION('NON_AM_EU_SALES');

USE ROLE AM_EU_SALES;
SELECT
    CURRENT_ROLE(),
    IS_ROLE_IN_SESSION('AM_EU_SALES'),
    IS_ROLE_IN_SESSION('NON_AM_EU_SALES');

Row access policies return a Boolean of true or false to indicate to Snowflake whether or not to allow the current query to return that particular row. Additionally, the policy needs a column value(s) passed to it to use in its evaluation. The input parameters don’t necessarily need to match the column name on the data allowing for a more abstract application of the policy against multiple objects. For this example, I’ll keep all the column names the same for ease of reading.

Once the policy is created, I’ll apply it to the sales data view using the REGION_NAME column as my input/evaluation column for the policy. Note that if the policy needs to be updated in the future, it will need to be removed from all objects first, updated, and then re-applied to each object.

/*CREATE ROW ACCESS POLICY*/
USE ROLE ST_DEMO_ROLE; --> TABLE OWNER
CREATE OR REPLACE ROW ACCESS POLICY REGION_SALES_ACCESS
    AS (REGION_NAME VARCHAR) RETURNS BOOLEAN -> 
    CASE
        WHEN IS_ROLE_IN_SESSION('AM_EU_SALES')
        AND REGION_NAME IN('AMERICA', 'EUROPE') THEN TRUE
        WHEN IS_ROLE_IN_SESSION('NON_AM_EU_SALES')
        AND REGION_NAME NOT IN('AMERICA', 'EUROPE') THEN TRUE
        ELSE FALSE
    END;
    
/*APPLY TO VIEW*/
    ALTER VIEW VW_SALES_DATA
ADD
    ROW ACCESS POLICY
REGION_SALES_ACCESS ON(REGION_NAME);

Testing the Row Access Policy

Now that the policy is in place, I can use each of the new roles to verify that I can only see the appropriate data. I’ll also test using the ST_DEMO_ROLE which should not have access to any of the data since ST_DEMO_ROLE is not part of the sales org. The code block below shows the regions that each role can access in the sales view validating that the row access policy is running as expected.

/*CREATE TEMP TABLE TO STORE RESULTS*/
USE ROLE ST_DEMO_ROLE;
CREATE OR REPLACE TEMPORARY TABLE TBL_ROLE_ACCESS(
    ROLE_NAME VARCHAR(100),
    ALLOWED_REGIONS VARCHAR(100)
);
GRANT INSERT ON TABLE TBL_ROLE_ACCESS TO ROLE SNOW_SALES;


/*RUN INSERT FOR EACH ROLE*/
USE ROLE AM_EU_SALES;
USE ROLE NON_AM_EU_SALES;
USE ROLE SALES_MGR;
USE ROLE ST_DEMO_ROLE;

INSERT INTO TBL_ROLE_ACCESS
SELECT DISTINCT CURRENT_ROLE(),
LISTAGG(DISTINCT REGION_NAME,',') WITHIN GROUP (ORDER BY REGION_NAME) AS ALLOWED_REGIONS
FROM VW_SALES_DATA;

/*SHOW ALLOWED REGIONS BY ROLE*/
SELECT * FROM TBL_ROLE_ACCESS;

How does this help an organization?

By connecting a dashboard or reporting tool such as PowerBI or Tableau to Snowflake, organizations can deploy a single dashboard that will “auto filter” the data to what that user is permitted to see. Imagine only maintaining one version of a highly complicated dashboard that “magically” adjusts itself by the incoming data being limited from the source. That’s just one use case – there are probably hundreds of others.

Conclusion

In this article, I discussed an application of a row access policy and how to apply it to data using hierarchical roles in Snowflake. Row access policies can be a valuable tool for data security; however, their application requires considerable planning of the policies and how the user roles behave within Snowflake.