0
0
In last post we discussed how the object ownership changes when any action performed on the ROLES. We have seen the ownership of objects transferred among the Roles but with restriction that both ROLES should lies in the same hierarchy. Now consider the scenario :
When want to transfer the ownership of object to the ROLE which is not part of the Hierarchy:
Lets try to understand with example:
In previous post we have followed the below hierarchy where REP2 and REP3 reports to FLM. After executing the grant ownership statement by FLM ,ownership transferred to REP3 from REP2.
Now we want to transfer the ownership from REP3 to MANAGER Role ,which is not part of FLM hierarchy.
CREATE or replace ROLE MANAGER;
create or replace user MANAGER password = 'MANAGER' default_role = MANAGER must_change_password = false;
grant role MANAGER to user MANAGER;
grant usage,monitor,create schema on database ROLES_DB to role MANAGER;
grant usage on warehouse ROLES_WH to role MANAGER;
grant ALL on schema ROLES_DB.public to role MANAGER;
Now Run the following command to grant ownership:
grant ownership on all tables in schema ROLES_DB.public to role MANAGER copy current grants;
Surprise, we see the above error while the same command executed for REP2->Rep3.
While transferring the ownership to the ROLE which is not part of Hierarchy, it is must the executor ROLE should have MANAGE GRANTS privilege’s.
Login to AccountAdmin and grant the “MANAGE GRANTS” privileges to role FLM1
GRANT MANAGE GRANTS ON ACCOUNT TO role FLM1;
Now Login to FLM and run the command:
So now when you will login the Manager , will see the ownership has changed to MANAGER:
This way I tried to cover the possible scenario with Object ownership inconjuction with ROLES.
