0
0
During this post we will understand how the Object OWNERSHIP gets transferred based on the action performed on the ROLES. For the first scenario we see what happens to the Object ownership if the ROLE owns these objects drop from the system.
Scenario1: Ownership of any objects owned by the dropped role is transferred to the role that executes the DROP ROLE command.
Say we have FLM1 role in the system. This Role has privileges to create ROLE and USERS respectively. So inside the FLM1 we have created a new Role and User.
Login To FLM:
CREATE or replace ROLE REP2;
create or replace user REP2 password = ‘REP2’ default_role = REP2 must_change_password = false;
grant role REP2 to user REP2;
grant role REP2 to role FLM1; //REP2 reports to FLM1
grant usage, monitor, create schema on database ROLES_DB to role REP2;
grant ALL on schema ROLES_DB.public to role REP2;
//grant warehouse to Role
grant usage on warehouse ROLES_WH to role REP2;
Login to REP2:
Login to REP2:
Create the table and observe the Qwnership privilges which belongs to REP2.
Login to the FLM and Drop the Role:
DROP ROLE REP2;
Now verify the grantee_name on Campaign table, it has pointed to FLM1 , the role who dropped its REP2.
Scenario 2: What if we want to transfer the ownership of each of these objects (from REP2) to a different or New role (REP3) and the ROLE lies in the Hierarchy (REP3->FLM). It can be possible by use:
GRANT OWNERSHIP … COPY CURRENT GRANTS.
Login to the FLM:
- Create Role REP2, as we have created in scenario one.
- Create the table inside the REP2 and verify the ownership. It should be REP2.
- Now create one more Role REP3.
CREATE or replace ROLE REP3;
create or replace user REP3 password = ‘REP3’ default_role = REP3 must_change_password = false;
grant role REP3 to user REP3;
grant role REP3 to role FLM1; //REP3 reports to FLM1
grant usage,monitor,create schema on database ROLES_DB to role REP3;
grant usage on warehouse ROLES_WH to role REP3;
grant ALL on schema ROLES_DB.public to role REP3;
Login to REP3: Currently don’t have access to any tables:
Login to the FLM and run the following command:
grant ownership on all tables in schema ROLES_DB.public to role REP3 copy current grants;
LOGIN to REP2:
Will not be having access to CAMPAIGN table:
Though we have one more scenario when ROLE does not lie into the hierarchy . Please click here to get the details:
