Cloning a role or copying grants to a new role in Snowflake

user_12534182 0
Viewed 2482

I would like to create a new role (role_new) that initially has identical grants to another role (role_old). Ideally, I would be able to clone a role. However, it doesn't look like it's possible to clone a role in Snowflake. The reason I don't want to use inheritance here is because I plan on revoking some grants from role_old, and therefore they will also get revoked from role_new.

It doesn't look like there is a way to copy grants from one role to another. Is this correct? The closest thing we have is inheritance.

Has anyone encountered something similar? Any ideas on how to handle this?

snowflake-cloud-data-platform
3 Answers

I don't think there's an easy way to clone an existing role's grants/permissions. Typically, this is a reason why I recommend people maintaining their Snowflake environment through code via Git and other tools like Flyway.

Otherwise you can view Grants and Roles by executing "Show Grants" and "Show Roles". And you can also look at using SnowReport or snowflakeinspector to visualize your security setup. But I think your best bet is to store your security framework via code so you can copy/modify/paste and execute it that way.

user_18549350

You can use a SQL generator to do this.

Step 1: Run a "show" to get the grants for a role.

Step 2: Run a SQL generator from the result_scan to create a list of grant statements.

Step 3: (Optional) Automate running them all using a stored procedure.

I haven't tested this much, but it would look a little like this:

show grants to role SYSADMIN;

select 'grant ' || "privilege" || ' on ' || "granted_on" || ' to MYNEWROLE;' as SQL_COMMAND from table(result_scan(last_query_id()));

If you want to run all your generated SQL statements, I wrote a stored procedure to do that specifically for generated commands:

https://support.snowflake.net/s/article/Executing-Multiple-SQL-Statements-in-a-Stored-Procedure

user_127563810

From post above Make sure to add the name:

show grants to role ANALYST;

select 'grant ' || "privilege" || ' on ' || "granted_on" ||' '||"name"|| ' to Genpactanalyst;' as SQL_COMMAND from table(result_scan(last_query_id()));
user_112578630
Related