I'd like to understand the roles assigned future ownership of database objects in Snowflake. For instance, let's say I have a database called 'DB' and a role named 'Role_1,' which currently has ownership of all database objects.
Now, I've changed the ownership of all future objects to 'Role_2.' If I create a new table or schema, I can see that 'Role_2' is the owner, while for existing tables, the owner remains 'Role_1.' This is the expected outcome.
However, I'm curious if there's a way to determine the future owner of an object before its creation. In above case befor creating new table or schema I was not aware about the Role_2 will be owner
You can check the below grants to check the role details
SHOW FUTURE GRANTS TO ROLE <role_name> [ LIMIT <rows> ]
SHOW FUTURE GRANTS TO DATABASE ROLE <database_role_name>
SHOW FUTURE GRANTS IN SCHEMA { <schema_name> } [ LIMIT <rows> ]
SHOW FUTURE GRANTS IN DATABASE { <database_name> } [ LIMIT <rows> ]
Also note that, when future grants are defined on the same object type for a database and a schema in the same database, the schema-level grants take precedence over the database-level grants, and the database-level grants are ignored. This behavior applies to privileges on future objects granted to one role or different roles
The below articles can be used as a reference https://community.snowflake.com/s/article/Behavior-of-future-grants-when-defined-at-both-database-and-schema-level https://community.snowflake.com/s/article/Precedence-rule-for-future-grants