Snowflake : To fetch all users and their associated roles without using ACCOUNTADMIN privileges in an automated way
a) Introduction:
Snowflake has been a dominant market player when it comes to cloud data warehousing. It’s micro-partition architecture coupled with some easy to use features (zero-copy clone, snowpipes, streams, tasks, etc) makes it a great choice for cloud data warehouse activities.
b) Purpose:
Recently while working for a client; I came across a scenario where I had to quickly provide a list of all users and their roles allocated to them to one of the architects. The problem sounds easy right? But the real issue was our entire team didn’t have ACCOUNTADMIN privileges due to which snowflake db wasn’t accessible to us. However; we had SECURITYADMIN privileges. So after some digging; we came across an approach which I have described below.
[Note: This is entirely our approach and there maybe multiple ways of approaching the same issue]
c) Approach:
Step 1: Our SECURITYADMIN role already had access to a warehouse and a database. In case; you don’t have access to any warehouse or db; then ask your ACCOUNTADMIN to either allocate usage privileges on any existing warehouse and database to SECURITYADMIN role or grant the below privileges:
USE ROLE ACCOUNTADMIN;
GRANT CREATE WAREHOUSE ON ACCOUNT TO ROLE SECURITYADMIN;
GRANT CREATE DATABASE ON ACCOUNT TO ROLE SECURITYADMIN;
Step 2:
Create a new warehouse or you can use any existing one. I am creating a new warehouse i.e SAMPLE_COMPUTE for demo purposes
Step 3: Create a db and 2 permanent tables
USE ROLE SECURITYADMIN;
USE WAREHOUSE SAMPLE_COMPUTE;
CREATE DATABASE TEMP_DB;
CREATE TABLE if not exists user_roles
(
roles varchar(100),
grantee_name varchar(100),
granted_by varchar(100)
);
CREATE TABLE if not exists all_users_stm
(
username varchar(1000);
);
Step 4: Create the Snowflake stored procedure from the below repo:
https://github.com/milind-data-engineering/snowflake/blob/main/sp_find_users_and_roles
Step 5: Call the snowflake stored procedure using
call sp_find_users_and_roles()
[Note: Each time anyone needs a list of users and their roles; just run this stored procedure and you should be good. You can download the data or just provide the table access to the concerned role/user for their reference]
