Giving MANAGE GRANTS to a Role

Question

I am writing a procedure that is going to create objects (views) across multiple schemas where those views need to have different owners. I want the procedure to GRANT OWNERSHIP to the appropriate role after it creates the view, but my newly created DEV_PROC_ROLE which is used to create and maintain procedures cannot execute GRANT OWNERSHIP. Our higher level roles can, but I don't want the procedure to be managed and executed up at that level.

Here's some context https://docs.snowflake.net/manuals/sql-reference/sql/grant-ownership.html

"A role that has the MANAGE GRANTS privilege can transfer ownership of an object to any role; in contrast, a role that does not have the MANAGE GRANTS privilege can only transfer ownership from itself to a child role within the role hierarchy."

So what command do I need to execute in my higher level role to give the MANAGE GRANTS privilege to the DEV_PROC_ROLE?

I cannot find in the documentation how to grant the MANAGE GRANTS privilege to a role.

Note - once the DEV_PROC_ROLE can grant ownership, it will issue a command like this to change the ownership and retain all of the Future Select priv's of other roles setup against that schema. GRANT OWNERSHIP ON VIEW TO ROLE COPY CURRENT GRANTS;

Jiaxing Liang Jiaxing Liang · Accepted Answer · 2019-11-19T01:03:38

In the document https://docs.snowflake.net/manuals/sql-reference/sql/grant-privilege.html , you can find the syntax:

GRANT {  { globalPrivileges        | ALL [ PRIVILEGES ] } ON ACCOUNT
       | { accountObjectPrivileges | ALL [ PRIVILEGES ] } ON { RESOURCE MONITOR | WAREHOUSE | DATABASE | INTEGRATION } <object_name>
       | { schemaPrivileges        | ALL [ PRIVILEGES ] } ON { SCHEMA <schema_name> | ALL SCHEMAS IN DATABASE <db_name> }
       | { schemaObjectPrivileges  | ALL [ PRIVILEGES ] } ON { <object_type> <object_name> | ALL <object_type>S IN SCHEMA <schema_name> }
       | { schemaObjectPrivileges  | ALL [ PRIVILEGES ] } ON FUTURE <object_type>S IN SCHEMA <schema_name>
      }
 TO [ ROLE ] <role_name> [ WITH GRANT OPTION ]

Where globalPrivileges

globalPrivileges ::=
  { { CREATE { ROLE | USER | WAREHOUSE | DATABASE | INTEGRATION } } | MANAGE GRANTS | MONITOR USAGE } [ , ... ]

So basically, you can do GRANT MANAGE GRANTS ON ACCOUNT TO DEV_PROC_ROLE to grant the MANAGE GRANTS privilege to the DEV_PROC_ROLE.

Giving MANAGE GRANTS to a Role

1 Answers