0

I'm trying to write SELECT statements that would generate 'grant' scripts that would grant all the privs to the appropriate roles in the entire account.

This is needed to migrate a Snowflake account from one region to another.

Has anyone come up with such a script ?

I have the following so far (runs ok, but its not validated yet,too many rows,perhaps its a cartesian product) : 

select 'grant ' || gt.privilege || ' on ' || GT.TABLE_CATALOG || '.' || gt.table_schema || '.' || gt.granted_on || ' '  || gt.name || ' to role ' ||  gt.grantee_name || ';' from account_usage.grants_to_roles gt
 where gt.table_catalog IS NOT NULL and gt.table_schema IS NOT NULL order by gt.grantee_name, gt.name, gt.granted_on,gt.privilege

Thanks

NJ_JA

biruk1230
  • 3,042
  • 4
  • 16
  • 29
NJ_JA
  • 1
  • As an interim solution, I'm using : select 'grant ' || gt.privilege || ' on ' || GT.TABLE_CATALOG || '.' || gt.table_schema || '.' || gt.name || ' to role ' || gt.grantee_name || ';' from account_usage.grants_to_roles gt where gt.grantee_name = 'ROLE_NAME' and gt.granted_on in ('TABLE','VIEW') and gt.table_catalog = 'DATABASE_NAME' and gt.table_schema='SCHEMA_NAME'; But there are 100+ roles or schemas in diff databases; – NJ_JA Feb 15 '20 at 23:22
  • That query looks reasonable to me, can you put in an example of where it's returning something you don't expect, and what you need instead? There's only one table you're querying from so shouldn't be a cartesian join issue. – David Garrison Feb 15 '20 at 23:37
  • Can't be a cartesian product, since you don't even have a join in your query. I think what you have is good. The reason it might have a lot of records is that this table shows you each grant, where in some cases you'd probably use `grant all` type of statements if you weren't scripting. Make sense? – Mike Walton Feb 16 '20 at 16:44
  • David, Mike, thank you for your quick response! The reason it looks like a cartesian product is this view grants_to_roles has 'all' the 'privileges' that have been granted to 'all' the roles. grant SELECT on TABLE .. to role ; – NJ_JA Feb 16 '20 at 18:19
  • David, Mike, thank you for your quick response! The reason it looks like a cartesian product is this view grants_to_roles has 'all' the 'privileges' that have been granted to 'all' the roles. So, the grants that get generated show incorrect privs being granted to incorrect roles. Eg: the select privs are granted to roles that are named like ___INQ - not as ___USAGE; – NJ_JA Feb 16 '20 at 18:27
  • eg: grant SELECT on TABLE .. to role ; So all kinds of criss-cross grants get generated. since this view has 'all the privs granted to all the roles, this view needs to be self-joined in multiple ways.... and I'm trying to get to that... Anyway... I've been moving with the slower alternate method, generating the grant script for each role – NJ_JA Feb 16 '20 at 18:27
  • Odd. one note here from Snowflake docs referring to `GRANT ALL`: "internally, the command is expanded into a series of individual GRANT commands..." so under the hood when querying this table you will see all the individual permissions. – David Garrison Feb 17 '20 at 04:13

0 Answers0