Menu
I am trying to change the OWNERSHIP from one role to other role in Snowflake DWH. Say Old Owner Role is the role with current Ownership, New Owner Roleis the role to which I wish to give ownership and R3 be a role which already has some access to the object of interest. I am getting below error when I am trying to do so.
SQL execution error: Dependent grant of privilege 'SELECT' on securable '' to role 'R3' exists. It must be revoked first. More than one dependent grant may exist
Why should an existing role be a problem for Ownership change?
395
Transfers ownership of an object (or all objects of a specified type in a schema) from one role to another role. OWNERSHIP is a special type of privilege that can only be granted from one role to another role; it cannot be revoked.
When you activate modeling views in HANA, SYS REPO provides the read access to users on these modeling views. That is why it is required to grant _SYS_REPO with SELECT with GRANT privilege to user schemas. This is required when you use objects of a table/view of a schema to build HANA Modeling Views.
Kannan, according to the Snowflake documentation (https://docs.snowflake.net/manuals/sql-reference/sql/grant-ownership.html), ownership of an object can't be granted (transferred) to another role unless all current privileges granted on the object have been revoked (using the REVOKE command). It looks like either the current owner or a member of a role with the appropriate privileges previously granted the SELECT privilege on the object (presumably a table or view) to the R3 role. That privilege will need to be revoked before ownership of the object can be transferred.
According to the same doc page, the restriction exists to prevent the new owner from unknowingly inheriting the object with grants already made on it.
56
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
