How to capture Snowflake users, roles and grants into tables.

manager Sun, 10/25/2020 - 20:34

Based on post from Venkatesh Sekar, but with lots of code cleanup.

You may be asked to participate in a security and audit review and have to answer some questions like these:

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

Create tables to show all the privileges in Snowflake

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.

 

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. 

Use Snowflake Stored Procedures

Define your Schema and Tables

use role accountadmin;
use warehouse interactive_wh;
use database DB_CORP;
use schema AUDIT_SNOWFLAKE;


CREATE DATABASE DB_CORP;
CREATE SCHEMA DB_CORP.AUDIT_SNOWFLAKE;
CREATE role if not exists AUDIT_SNOWFLAKE;
grant role AUDIT_SNOWFLAKE to role accountadmin;

grant ownership on SCHEMA DB_CORP.AUDIT_SNOWFLAKE to AUDIT_SNOWFLAKE REVOKE CURRENT GRANTS;

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

GRANT select,delete,update on all     tables  in database DB_CORP to role AUDIT_SNOWFLAKE;
GRANT select,delete,update on future  tables  in database DB_CORP to role AUDIT_SNOWFLAKE;
GRANT create table, modify on all     schemas in database DB_CORP to role AUDIT_SNOWFLAKE;
GRANT create table, modify on future  schemas in database DB_CORP to role AUDIT_SNOWFLAKE;
GRANT usage                on all     schemas in database DB_CORP to role AUDIT_SNOWFLAKE;
GRANT usage                on future  schemas in database DB_CORP to role AUDIT_SNOWFLAKE;
GRANT usage                on         database            DB_CORP to role AUDIT_SNOWFLAKE;

Stored Procedure Definitions

-- stored procedure --
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 dbusers select * ,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;$$;

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 dbroles select *,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;$$;

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 dbgrants select *,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 dbgrants select *,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 dbgrants select *,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 dbgrants select *,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

call SNAPSHOT_USERS();
call SNAPSHOT_ROLES();
call SNAPSHOT_GRANTS();

Example Queries for Analysis

--List the roles and the count of users who are defaulted to this role:
SELECT DEFAULT_ROLE ,COUNT(*) USER_COUNTS 
FROM DBUSERS WHERE DEFAULT_ROLE IS NOT NULL
GROUP BY DEFAULT_ROLE 
ORDER BY USER_COUNTS DESC;

-- Users Without Default Roles
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;

-- Show users

SELECT PRIVILEGE , NAME, GRANTED_ON
FROM DBGRANTS
WHERE GRANTED_TO NOT IN ('ROLE' ,'SECURITYADMIN' ,'ACCOUNTADMIN');