How to Capture Snowflake Users, Roles, and Grants Into a Table

Consider Two Options: #1 Snowflake Stored Procedures and #2 Python

Hashmap
Hashmap
Oct 31 · 11 min read

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
  • Consumption-Based
  • 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;

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;

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>;

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;

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.

DBUsers Table

The DBUsers table holds the users who access Snowflake — both normal users and any service accounts.

CREATE OR REPLACE TABLE DBUSERS (

DBRoles Table

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 (

DBGrants Table

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 (

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.

CREATE OR REPLACE PROCEDURE SNAPSHOT_USERS()

Capturing the List of Roles

The stored procedure ‘SNAPSHOT_ROLES’ is used to capture the result of SHOW roles command into the DBRoles table.

CREATE OR REPLACE PROCEDURE SNAPSHOT_ROLES()

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.

CREATE OR REPLACE PROCEDURE SNAPSHOT_GRANTS()

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;

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_COUNTS

Users Without Default Roles

List the users who don’t have default roles or warehouse or namespace assigned:

SELECT NAME

Roles to Users Counts

SELECT PRIVILEGE ,COUNT(*) USERS_COUNT

Final Thoughts

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…


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.

HashmapInc

Innovative technologists and domain experts helping accelerate the value of Data, Cloud, IIoT/IoT, and AI/ML for the community and our clients by creating smart, flexible and high-value solutions and service offerings that work across industries. http://hashmapinc.com

Hashmap

Written by

Hashmap

Innovative technologists and domain experts accelerating the value of Data, Cloud, IIoT/IoT, and AI/ML for the community and our customers http://hashmapinc.com

HashmapInc

Innovative technologists and domain experts helping accelerate the value of Data, Cloud, IIoT/IoT, and AI/ML for the community and our clients by creating smart, flexible and high-value solutions and service offerings that work across industries. http://hashmapinc.com

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade