How to Capture Snowflake Users, Roles, and Grants Into a Table
Consider Two Options: #1 Snowflake Stored Procedures and #2 Python
by Venkatesh Sekar
You are one of 3,000 organizations or so that has adopted Snowflake’s Cloud Data Warehouse for one or more use cases that your organization has deemed critical to proving out the service, and have successfully benefitted from Snowflake’s unique value drivers including:
- Cloud & Data Warehouse Native
- Independent Compute Clusters
- It’s SQL
- Low to No Management Overhead
- All Data & Data Sharing
- Multi-Cloud — AWS, Azure, and GCP
At some point, you may be asked to participate in a security and audit review cycle during which you have to answer some questions along these lines:
- How are our users connected to Snowflake?
- What are the custom roles that we implemented?
- Which users are assigned to which hierarchy?
- What are the privileges assigned to a particular role, and which privileges are granted via inheritance?
- Given a database object such as a table, how is that table accessed by a specific user and through which roles?
- Are there any ghost or zombie roles?
- Are the roles getting inherited by SYSADMIN or SECURITYADMIN?
- Which users are assigned to ACCOUNTADMIN?
- Are there any tables getting created and assigned to SYSADMIN and not following best practices?
- Can you provide a visual representation of users, roles, and grants in Snowflake?
And so on and so forth.
Here’s What To Do
To answer these questions, you’ll need to retrieve the users, roles, and grants (privileges) and have an understanding of how they are related to each other. Ideally, these records would be accessible in a single table to quickly perform queries and get answers to questions such as the ones above. At present, that table is not defined in Snowflake, although it’s probable that Snowflake will provide that as part of the service at some point.
Let’s Get the Information
In this post, I’m going to walk you through where the information lives in Snowflake and show you a couple of ways that I get the users, roles, and grants information combined that work well and make it very accessible for answering those security and audit questions.
Snowflake Users and Roles via the Snowflake UI
If you go to the Account Admin page, you’ll see the list of users and roles.
Next, the role hierarchy can be retrieved via the Roles tab, as shown below.
Snowflake Users and Roles via SQL
You can also retrieve users and roles directly from SQL (one of the huge advantages of Snowflake — it’s SQL!) via the SHOW command. For example, to get the list of users, you would issue the following commands:
USE ROLE SECURITYADMIN;SHOW USERS;SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
Snowflake Reference Documentation provides a step-by-step for you as well.
However, there are some things to be aware of when trying to interact with the result. You will need to issue the SHOW command for each interaction. For example, to project a specific column like I’m doing below, you would have to issue the SHOW command and then the select statement. Also, you’ll need to ensure that the column names are inside quotes. For example:
SHOW USERS;SELECT “login_name”,”default_warehouse” ,”default_role”FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))where “disabled” = false;
In order to access all users in the Snowflake subscription, you would need either SECURITYADMIN privileges or you’d need to have a custom role that has “MANAGE GRANTS” privilege.
Grants retrieval for each user needs to be very specific, and you’ll have to execute the show grants commands as shown below iteratively:
SHOW GRANTS ON USER <login_name>;SHOW GRANTS TO USER <login_name>;
For roles and other objects, you will have to issue the same commands iteratively.
But There is a Better Way
So, as you can see, the process of compiling information on users, roles, and grants can take some time and effort. In this post, I’ll show you two alternative approaches (one of them in detail) to what we just walked through that provide a quicker way to get your security and audit questions answered.
Note regarding the intended audience for this story — it does require the reader to have hands-on experience in Snowflake, specifically in the following areas:
- Use SECURITYADMIN roles or MANAGE GRANTS privilege
- Define users, custom role and role hierarchy
- Define grants and privileges for roles and database objects
Approach #1 — Use Snowflake Stored Procedures
While there are many paths to similar outcomes in Snowflake, with this approach, I’ll demonstrate how to use Stored Procedures, which are native to Snowflake, for capturing users, roles, and grants information into a table.
The idea is to implement stored procedures that issue the SHOW command and capture the results into a corresponding object table. For each object (Users, Roles, and Grants) we would implement an individual stored procedure as noted in the table below:
A SECURITYADMIN would ideally invoke the stored procedure, and once these have run, the resulting table would have the necessary records from which further security analysis can be reviewed.
Define Your Schema
We’ll kick things off by creating a separate schema that by default, is not accessible by normal users and roles. You wouldn’t want this schema available to normal users as the login_names and roles would become publicly exposed and thus vulnerable to security breaches.
You should, however, ensure that the schema is usable by the SECURITYADMIN or ACCOUNTADMIN role or a role with MANAGE GRANTS privilege.
GRANT ALL PRIVILEGES ON SCHEMA <schema name> TO ROLE SECURITYADMIN;GRANT ALL PRIVILEGES ON future TABLES in schema <schema name> to role SECURITYADMIN;GRANT USAGE ON FUTURE PROCEDURES IN SCHEMA <schema name> to role SECURITYADMIN;
Define the Tables
Next, we’ll define the tables which will hold the records for users, roles, and grants. The tables have the same structure as the output of the corresponding RESULT_SCAN.
We have an additional column ‘REFRESH_DATE’ defined, and this column holds the timestamp on which the record was inserted into the table.
These tables need to be readable and writable by the SECURITYADMIN role. When the stored procedures execute, as SECURITYADMIN, they would insert records into the table.
The DBUsers table holds the users who access Snowflake — both normal users and any service accounts.
CREATE OR REPLACE TABLE DBUSERS (NAME VARCHAR,CREATED_ON TIMESTAMP_LTZ,LOGIN_NAME VARCHAR,DISPLAY_NAME VARCHAR,FIRST_NAME VARCHAR,LAST_NAME VARCHAR,EMAIL VARCHAR,MINS_TO_UNLOCK VARCHAR,DAYS_TO_EXPIRY VARCHAR,TCOMMENT VARCHAR,DISABLED VARCHAR,MUST_CHANGE_PASSWORD VARCHAR,SNOWFLAKE_LOCK VARCHAR,DEFAULT_WAREHOUSE VARCHAR,DEFAULT_NAMESPACE VARCHAR,DEFAULT_ROLE VARCHAR,EXT_AUTHN_DUO VARCHAR,EXT_AUTHN_UID VARCHAR,MINS_TO_BYPASS_MFA VARCHAR,OWNER VARCHAR,LAST_SUCCESS_LOGIN TIMESTAMP_LTZ,EXPIRES_AT_TIME TIMESTAMP_LTZ,LOCKED_UNTIL_TIME TIMESTAMP_LTZ,HAS_PASSWORD VARCHAR,HAS_RSA_PUBLIC_KEY VARCHAR,REFRESH_DATE TIMESTAMP_LTZ DEFAULT CURRENT_TIMESTAMP())COMMENT = ‘stores snapshot of current snowflake users’ ;
The DBRoles table holds the roles which operate on Snowflake and it contains both the default and user-defined custom roles.
CREATE OR REPLACE TABLE DBROLES (CREATED_ON TIMESTAMP_LTZ,NAME VARCHAR,IS_DEFAULT VARCHAR,IS_CURRENT VARCHAR,IS_INHERITED VARCHAR,ASSIGNED_TO_USERS NUMBER,GRANTED_TO_ROLES NUMBER,GRANTED_ROLES NUMBER,OWNER VARCHAR,RCOMMENT VARCHAR,REFRESH_DATE TIMESTAMP_LTZ DEFAULT CURRENT_TIMESTAMP())COMMENT = ‘stores snapshot of current snowflake roles’ ;
The DBGrants table holds the records which define the following:
- The privileges assigned to roles on database objects (tables, views, etc.)
- The roles assigned to another role (role hierarchy)
- The users and roles relationship
- The ownership role on a specific object
CREATE OR REPLACE TABLE DBGRANTS (CREATED_ON TIMESTAMP_LTZ,PRIVILEGE VARCHAR,GRANTED_ON VARCHAR,NAME VARCHAR,GRANTED_TO VARCHAR,GRANTEE_NAME VARCHAR,GRANT_OPTION VARCHAR,GRANTED_BY VARCHAR,REFRESH_DATE TIMESTAMP_LTZ DEFAULT CURRENT_TIMESTAMP())COMMENT = ‘stores snapshot of current grants’ ;
Stored Procedure Definitions
As mentioned previously, we are “gathering up” the users, roles, and grants via the stored procedure. The code for implementing the stored procedure is detailed in the below sections.
One key point regarding invoking the SHOW command inside of a stored procedure is that the procedure should be declared “EXECUTE AS CALLER” — this is explained further in this Snowflake Community article: How-to-USE-SHOW-COMMANDS-in-Stored-Procedures.
The stored procedure is invoked by the SECURITYADMIN role or a role with MANAGE GRANTS privilege.
Capturing the List of Users
The stored procedure ‘SNAPSHOT_USERS’ is used to capture the result of the SHOW users command into the DBUsers table.
Capturing the List of Roles
The stored procedure ‘SNAPSHOT_ROLES’ is used to capture the result of SHOW roles command into the DBRoles table.
Capturing the List of Grants
The stored procedure ‘SNAPSHOT_GRANTS’ is used to capture the result of SHOW grants command into the DBGrants table.
Unlike the SHOW commands for roles and users, retrieving the grants has to be executed for each specific role and user. Therefore, the logic involves iterating each record from the DBUsers and DBRoles table, issuing the SHOW grants command, and then capturing the result.
Executing the Stored Procedures
The above-defined stored procedures should be invoked by the SECURITYADMIN role or a role with MANAGE GRANTS privilege as the information is available only for these roles, and they should be invoked in the following order:
USE ROLE SECURITYADMIN;call SNAPSHOT_USERS();call SNAPSHOT_ROLES();call SNAPSHOT_GRANTS();
Once the execution is complete, the tables are populated accordingly. The ‘SNAPSHOT_GRANTS’ call takes a bit longer to execute.
Below is a screenshot of the result of calling the SNAPSHOT_USERS stored procedure.
And below is a sample query result from the DBGrants table:
Approach #2 — Use Python for Capturing Users, Roles, & Grants
I hope that the detail on using Snowflake Stored Procedures for capturing users, roles, and grants information into a table was helpful.
Another way that I’ve gone after the same result is to use Python. When I used the Python-based approach, at the time, Python and Java were the only languages which allowed the SHOW command to be called, and I used Python to:
- Issue the SHOW commands
- Capture the result into a file
- Issue a COPY INTO command into the corresponding dbusers, dbroles, and dbgrants tables
I’ll save the code demonstration with Python for another day, but one observation I made is that the Python execution was much faster in comparison to the stored procedure implementation. Which implementation you prefer is entirely left up to you, your needs, and your skillset.
Example Queries for Analysis
I’ve put together some queries for demonstrations purposes and to give you a perspective of what now can be achieved once you have captured the records into tables either via Stored Procedures or a Python implementation.
Default Role Assigned User Count
List the roles and the count of users who are defaulted to this role:
SELECT DEFAULT_ROLE ,COUNT(*) USER_COUNTSFROM DBUSERSWHERE DEFAULT_ROLE IS NOT NULLGROUP BY DEFAULT_ROLEORDER BY USER_COUNTS DESC;
Users Without Default Roles
List the users who don’t have default roles or warehouse or namespace assigned:
SELECT NAMEFROM DBUSERSWHERE (DEFAULT_ROLE = ‘’ OR DEFAULT_WAREHOUSE = ‘’ OR DEFAULT_NAMESPACE = ‘’);
Roles to Users Counts
SELECT PRIVILEGE ,COUNT(*) USERS_COUNTFROM DBGRANTSWHERE GRANTED_TO NOT IN (‘ROLE’ ,’SECURITYADMIN’ ,’ACCOUNTADMIN’)GROUP BY PRIVILEGE;
I’ve given you a few examples, and from here, you can explore a wide range of varying scenarios that are of interest to you and your organization and most importantly, you can be ready if you get a barrage of questions from your security and audit team.
Also, I’ll keep an eye out for Snowflake implementing users, roles, and grants into a table, but until that’s available, I hope this helps you out!
Lastly, I get asked by a lot of clients about visualization of Snowflake users, role hierarchy, and grants, so look for that topic from me in a future story.
Some of My Other Snowflake Stories
I hope you’ll check out some of my other recent stories also…
Doing DevOps for Snowflake with dbt in Azure
How to Use CI/CD to Deploy Your Snowflake Database Scripts with dbt in Azure DevOps
Don’t Do Analytics Engineering in Snowflake Until You Read This (Hint: dbt)
Using dbt To Create Tables Using Custom Materialization
Feel free to share on other channels and be sure and keep up with all new content from Hashmap here.
Venkat Sekar is Regional Director for Hashmap Canada and is an architect and consultant providing Data, Cloud, IoT, and AI/ML solutions and expertise across industries with a group of innovative technologists and domain experts accelerating high-value business outcomes for our customers.