With the emergence and rapid innovation of AI and ML, especially from Snowflake, the basics are often overlooked for the “new and exciting”. One of the most fundamental aspects of managing a Snowflake account and environment is ensuring proper security and access controls. Snowflake relies on role-based access control (RBAC). Ideally, roles are hierarchical to allow the assignment of user least privilege appropriately.
In this blog post, we’ll walk through a Snowflake script designed to create and manage roles hierarchically. This script ensures that all necessary roles are created and correctly assigned, streamlining the process of role management.
Step 1: Setting Up the Environment
To begin, ensure we are operating with the correct role and warehouse to create the roles and grants. This could be a native Snowflake role like ACCOUNTADMIN or SECURITYADMIN, or depending on your account setup, could be a DBA or InternalAdmin role. For this example, I’ll use the default ACCOUNTADMIN role and COMPUTE_WH warehouse from my demo account.
USE ROLE ACCOUNTADMIN;
USE WAREHOUSE COMPUTE_WH;
Step 2: Creating a Temporary Table for Role Hierarchy
The next step is to create a temporary table to hold the role hierarchy. This table will store pairs of child and parent roles, defining the structure of the role hierarchy. Additionally, to we’ll also include a shared child role “ST_CORTEX_USERS” that would be used for granting access to Snowflake Cortex AI functions:
/*CREATE TEMP TABLE TO HOLD ROLE HIERARCHY*/
CREATE OR REPLACE TEMPORARY TABLE ROLE_HIER(CHILD_ROLE STRING,PARENT_ROLE STRING);
INSERT INTO ROLE_HIER(CHILD_ROLE,PARENT_ROLE)
VALUES
('ST_DEV_MGR','ST_MGR'),
('ST_ANLY_MGR','ST_MGR'),
('ST_DEV','ST_DEV_MGR'),
('ST_ANLY','ST_ANLY_MGR'),
('ST_MGR','SYSADMIN'),
('ST_CORTEX_USERS','ST_DEV'),
('ST_CORTEX_USERS','ST_ANLY');
Step 3: Creating Roles Dynamically
With the hierarchy defined, we need to ensure that all roles specified in our hierarchy exist. The following block declares a cursor to iterate over all unique roles in our temporary table and creates them if they do not already exist:
/*CREATE ROLES IF NOT ALREADY IN THE ACCOUNT*/
DECLARE
DYN_SQL STRING;
BEGIN
LET rsRoles RESULTSET :=(
SELECT ROLE_NAME FROM(
SELECT CHILD_ROLE AS ROLE_NAME FROM ROLE_HIER
UNION SELECT PARENT_ROLE FROM ROLE_HIER
) GROUP BY ROLE_NAME
);
LET cRoles CURSOR for rsRoles;
FOR r IN cRoles DO
DYN_SQL := 'CREATE ROLE IF NOT EXISTS '|| r.ROLE_NAME;
EXECUTE IMMEDIATE(:DYN_SQL);
END FOR;
END;
Step 4: Granting Child Roles to Parent Roles
After ensuring all roles exist, we need to assign the child roles to their respective parent roles. Again, the code below creates a cursor to iterate through all the role grants as defined in the temporary table:
/*GRANT CHILD ROLES TO PARENT ROLES*/
DECLARE
DYN_SQL STRING;
BEGIN
LET rsRoles RESULTSET :=(
SELECT CHILD_ROLE,PARENT_ROLE FROM ROLE_HIER
GROUP BY ALL
);
LET cRoles CURSOR for rsRoles;
FOR r IN cRoles DO
DYN_SQL := CONCAT('GRANT ROLE ',r.CHILD_ROLE, ' TO ROLE ',r.PARENT_ROLE);
EXECUTE IMMEDIATE(:DYN_SQL);
END FOR;
END;
Conclusion
By following these steps, you can efficiently create and manage a role hierarchy in Snowflake. This script ensures that all roles are created and properly assigned, which is crucial for maintaining a secure and organized Snowflake environment. Feel free to customize the role hierarchy table (`ROLE_HIER`) to fit the specific needs of your organization.
Managing roles in Snowflake doesn’t have to be complicated. With the right approach and tools, you can set up a scalable and maintainable access control structure that supports your business needs.
The full SQL script is available on my GitHub Repo.
Follow me on LinkedIn and Medium for more content on Data Management and demos including Snowflake, Streamlit, and SQL Server.
Eric Heilman 