During the older posts we have discussed the Access Control mechanism and RBAC hierarchy in great detail. In this post, we will be focusing on special privilege called OWNERSHIP. OWNERSHIP a type of privilege that can only be grant from one role to another role; it cannot be revoke. Many times it arouses the confusion related to the technical difference between ownership of a role and an object. During this discussion we will try to understand Object Ownership, Role Ownership, Inheritance(Role Hierarchy).
Object Ownership: Role having OWNERSHIP privileges owns the Database, Schema and Tables and hence have the full control over the objects. Therefore can perform Insert, Update and delete from the tables as well grant access to other roles. Effectively this role manages the objects.
Role ownership: It has no access to the underlying data, important to understand, he cannot actually view the data in DB. Also not authorized to perform DML operations on the data
However, can grant the role(SF_DBA) to other users or roles (including itself). Has the ability to alter or grant the role to others
Inheritance: SF_DBA has been granted the ROLE1 role. This means ROLE1 inherits all of the underlying grants and effectively also owns the underlying Database, Schema and Tables. This means ROLE1 user has the exact same privileges on the database as SF_DBA user.
We will try to understand the above concepts by the below architecture:
1. Created the DBA role who would be owning the Database objects like DB, Schema and Tables
create role SF_DBA
create or replace user SF_DBA password = 'SF_DBA' default_role = SF_DBA must_change_password = false;
grant role SF_DBA to user SF_DBA;
grant usage on warehouse COMPUTE_WH to role SF_DBA;
grant role SYSADMIN TO ROLE SF_DBA
2. Now login to the SF_DBA user and create the DB,Schema and tables.
create database sample_Db
USE DATABASE sample_Db
CREATE SCHEMA SAMPLE_SCHEMA
CREATE or replace TABLE Campaign (camp_id number);
INSERT INTO Campaign(CAMP_ID)VALUES(1)
Run the Query to validate THE OWNER of the objects:
select * from information_Schema.object_privileges where grantor = 'SF_DBA' AND OBJECT_NAME IN('SAMPLE_DB','SAMPLE_SCHEMA','CAMPAIGN') AND PRIVILEGE_TYPE = 'OWNERSHIP'
Object Ownership: Role SF_DBA owns the objects and have the privileges to grant access on DB objects to other roles.
To test how the object ownerships works we have created a new Role: ROLE3 and assigned to the user ROLE3.
create or replace user ROLE3 password = 'ROLE3' default_role = ROLE3 must_change_password = false;
grant role ROLE3 to user ROLE3;
grant usage on warehouse COMPUTE_WH to role ROLE3;
Login to the SF_DBA user and grant the required privileges to access the DB and tables to the ROLE3 .
grant usage,monitor on database sample_Db to role ROLE3;
grant usage on schema sample_Db.SAMPLE_SCHEMA to role ROLE3;
grant select on all tables in schema sample_Db.SAMPLE_SCHEMA to role ROLE3;
Now Login to the ROLE3 user and validate if we could able to see the DB objects owned by SF_DBA ROLE.
Create the SF_ADMIN role and will grant the SF_DBA Role ownership to SF_ADMIN role in following way.
create role SF_ADMIN;
grant ownership on role SF_DBA to role SF_ADMIN;
create or replace user SF_ADMIN password = 'SF_ADMIN' default_role = SF_ADMIN must_change_password = false;
grant role SF_ADMIN to user SF_ADMIN;
Login to the SF_ADMIN user and try to access the table lies in SAMPLE_DB database.
An error would be generated , which clearly implies Role ownerships has no access to the underlying data or objects.
Though granting the SF_DBA role to other users or roles (including itself) is very much possible. Hence Role ownership: allows user the ability to alter or grant the role to others.
Now ROLE2 owns the ownership of SF_DBA and it can furthermore grant access to other role.
Inheritance (Role Hierarchy) : Granting the Role to other Role, inherits all of the underlying grants and effectively also owns the underlying Database, Schema and Tables.
Create a new Role ROLE1 and will grant SF_DBA role to the ROLE1 in below way, This would imply ROLE1 and SF_DBA posses the same privileges.
create or replace user ROLE1 password = 'ROLE1' default_role = ROLE1 must_change_password = false;
grant role ROLE1 to user ROLE1;
grant usage on warehouse COMPUTE_WH to role ROLE1;
grant role SF_DBA to role role1
Login to the ROLE1 and you will see the user has access to the DB, Schema and Tables. Moreover it has all DML privileges and it can insert the data into table as well.