Working Around Account Admin Privileges in Snowflake

A guide to finding all users and their associated roles in Snowflake without using account admin privileges

Photo by Danial Igdery on Unsplash

Snowflake has been a dominant market player when it comes to cloud data warehousing. It’s micro-partition architecture coupled with easy-to-use features — such as zero-copy clone, snowpipes, streams, and tasks — make it a great choice for cloud data warehouse activities.

Recently while working for a client, I came across a scenario where I had to quickly provide a list of all users and their allocated roles to one of the architects. While this might sound easy to address, the real issue was that no one on our team had account admin privileges, meaning Snowflake’s database wasn’t accessible to us. However, we had security admin privileges and found a couple of different ways to approach the problem, which I’ve outlined below.

Our automated approach

When scheduled and monitored, the automated approach ensures no users are granted privileges that they shouldn’t have on Snowflake. This is particularly helpful as you onboard more users to Snowflake.

Step one:

Our security admin role already had access to a warehouse and a database. If you don’t have access to any warehouse or database, ask your account admin to either allocate usage privileges on any existing warehouse and database to a security admin role or grant the below privileges:

1. USE ROLE ACCOUNTADMIN;
2. GRANT CREATE WAREHOUSE ON ACCOUNT TO ROLE SECURITYADMIN;
3. GRANT CREATE DATABASE ON ACCOUNT TO ROLE SECURITYADMIN;

Step two:

You can create a new warehouse or simply use an existing one. I created a new warehouse (i.e., SAMPLE_COMPUTE) for demo purposes.

Step three:

Create a database and two permanent tables:

1. USE ROLE SECURITYADMIN;
2. USE WAREHOUSE SAMPLE_COMPUTE;
3. CREATE DATABASE TEMP_DB;
4. CREATE TABLE if not exists user_roles
   (
   roles varchar(100),
   grantee_name varchar(100),
   granted_by varchar(100)
   );
5. CREATE TABLE if not exists all_users_stm
   (
   username varchar(1000)
   );

Step four:

Create the Snowflake stored procedure from this Github repository.

Step five:

Any time someone needs a list of users and their roles, run the below stored procedure:
call sp_find_users_and_roles()

You can download the data or provide the table access to the user for reference.

Our manual approach

Having an automated way to find users and their roles using a Snowflake-stored procedure is useful in many cases, but there are scenarios where you might simply need the report for a one-time activity. In these cases, we would manually create a temporary table so that the data isn’t persistent.

Step one:

Use an existing warehouse or database, or create one before proceeding. Then set Snowflake context using:

1. USE ROLE SECURITYADMIN;
2. USE WAREHOUSE SAMPLE_COMPUTE;
3. USE DATABASE TEMP_DB;
   CREATE TEMPORARY TABLE if not exists user_roles
   (
   roles varchar(100),
   grantee_name varchar(100),
   granted_by varchar(100)
   );

Step two:

1. show users;
   select listagg(‘show grants to user ‘||”name”|| ‘;’ || ‘\n’ ||’insert into user_roles select ‘|| ‘“role”,”grantee_name”,”granted_by”’||
   ‘ from table(result_scan(-1));’,’\n’)
   from table(result_scan(-1));

Basically, for every user in the account, we want to generate the below sample statements:

show grants to user USER1;insert into user_roles select
“role”,”grantee_name”,”granted_by” from table(result_scan(-1));show grants to user USER2;insert into user_roles select “role”,”grantee_name”,”granted_by” from table(result_scan(-1));

So, if you have 100 users, then you would have 100 such statements. RESULT_SCAN(-1) ensures that Snowflake captures results from the last executed query. So for every user, we will show their grants and then capture them into a table.

Step three:

Click on the query output in Snowflake UI and click the “copy” button, then paste the content and execute all of these statements together.

Step four:

Verify the table ‘user_roles’ and download the data to share. Remember that it’s a temporary table, so you can’t check it in another Snowflake session.

Conclusion

Often IT architects, admins, or IT security teams will need to revisit the users and their allocated roles to ensure that users don’t have privileges to a specific role they shouldn’t have access to. This is pivotal for ensuring the security of your Snowflake environment.

Slalom is a global consulting firm focused on strategy, technology, and business transformation. Learn more and reach out today.

Found this article useful? Hit that clap button. Really like it? Hold the clap, give it two, or fifty!