HOW TO CAPTURE SNOWFLAKE USERS, ROLES, AND GRANTS INTO A TABLE

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.

DBUsers 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,DEFAULT_SECONDARY_ROLES 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' ;

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

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 (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.

CREATE OR REPLACE PROCEDURE SNAPSHOT_USERS()
RETURNS VARCHAR
LANGUAGE JAVASCRIPT 
COMMENT = 'Captures the snapshot of users and inserts the records into dbusers'
EXECUTE AS CALLER
AS
$$
var result = "SUCCESS";
try {
    snowflake.execute( {sqlText: "TRUNCATE TABLE DBUSERS;"} );
    snowflake.execute( {sqlText: "show users;"} );
    var dbusers_tbl_sql = `insert into dbusersselect * ,CURRENT_TIMESTAMP() from table(result_scan(last_query_id()));`;
    snowflake.execute( {sqlText: dbusers_tbl_sql} );
} 
catch (err) {
    result = "FAILED: Code: " + err.code + "\n State: " + err.state;result += "\n Message: " + err.message;result += "\nStack Trace:\n" + err.stackTraceTxt;
}
    return result;
$$;

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()
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
COMMENT = 'Captures the snapshot of roles and inserts the records into dbroles'
EXECUTE AS CALLER
AS
$$
var result = SUCCESS";
try {
    snowflake.execute( {sqlText: "truncate table DBROLES;"} );
    snowflake.execute( {sqlText: "show roles;"} );
    var dbroles_tbl_sql = `insert into dbrolesselect *,CURRENT_TIMESTAMP() from table(result_scan(last_query_id()));`;
    snowflake.execute( {sqlText: dbroles_tbl_sql} );
} 
catch (err) {
    result = "FAILED: Code: " + err.code + "\n State: " + err.state;result += "\n Message: " + err.message;result += "\nStack Trace:\n" + err.stackTraceTxt;
}
    return result;
$$;

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()
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
COMMENT = 'Captures the snapshot of grants and inserts the records into dbgrants'
EXECUTE AS CALLER
AS
$$
function role_grants() 
{
    var obj_rs = snowflake.execute({sqlText: `SELECT NAME FROM DBROLES;`});
    while(obj_rs.next()) {
        snowflake.execute({sqlText: `show grants to role "` + obj_rs.getColumnValue(1) + `" ;` });
        snowflake.execute( {sqlText:`insert into dbgrantsselect *,CURRENT_TIMESTAMP() from table(result_scan(last_query_id()));`});
        snowflake.execute({sqlText: `show grants on role "` + obj_rs.getColumnValue(1) + `" ;` });
        snowflake.execute( {sqlText:`insert into dbgrantsselect *,CURRENT_TIMESTAMP()from table(result_scan(last_query_id()));`});
    }
}
// — — — — — — — — — — — — — — — — — — — — — — — —
function user_grants()
{
    var obj_rs = snowflake.execute({sqlText: `SELECT NAME FROM DBUSERS;`});
    while(obj_rs.next()) {
      snowflake.execute({sqlText: `show grants to user "` + obj_rs.getColumnValue(1) + `" ;` });
      snowflake.execute( {sqlText:`insert into dbgrantsselect *,null,null,null,CURRENT_TIMESTAMP() from table(result_scan(last_query_id()));`});
      snowflake.execute({sqlText: `show grants on user "` + obj_rs.getColumnValue(1) + `" ;` });
      snowflake.execute( {sqlText:`insert into dbgrantsselect *,CURRENT_TIMESTAMP() from table(result_scan(last_query_id()));`});
    }
}
// — — — — — — — — — — — — — — — — — — — — — — — —
var result = "SUCCESS";
try {
    snowflake.execute( {sqlText: "truncate table DBGRANTS;"} );
    role_grants();
    user_grants();
} 
catch (err) {
    result = "FAILED: Code: " + err.code + "\n State: " + err.state;result += "\n Message: " + err.message;result += "\nStack Trace:\n" + err.stackTraceTxt;
}
    return 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_COUNTS
FROM DBUSERSWHERE DEFAULT_ROLE IS NOT NULL
GROUP 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 NAME
FROM DBUSERS
WHERE (DEFAULT_ROLE = '' OR DEFAULT_WAREHOUSE = '' OR DEFAULT_NAMESPACE = '');

Roles to Users Counts

SELECT PRIVILEGE ,COUNT(*) USERS_COUNT
FROM DBGRANTS
WHERE GRANTED_TO NOT IN ('ROLE' ,'SECURITYADMIN' ,'ACCOUNTADMIN')
GROUP BY PRIVILEGE;

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.

SanjayLakhanpal