Role Hierarchy:

Read Time:2 Minute, 16 Second

We will discuss about Role Hierarchy, to map Custom Role to SYSADMIN role.

Following privileges needs to be granted to Custom Role:
•USAGE on the database that contains the schema
•ALL on the schema that contains the tables to query
•USAGE on a warehouse used to execute queries on the tables in the schema

 Create new user sachintest.

• create role custom comment = 'This is Custom Role;
• grant usage on database DEMO_DB to role custom;
• grant all on schema DEMO_DB.PUBLIC to role custom;
• grant usage on warehouse COMPUTE_WH to role custom;
• grant role custom to user sachintest;

/* To grant all privileges to the schema/tables
grant all privileges on all schemas in database DEMO_DB to role custom;
grant all privileges on all tables in schema DEMO_DB.PUBLIC to role custom;
grant all privileges on all views in schema DEMO_DB.PUBLIC to role custom;
*/

Login to the sachintest user:

• User would be having the access to DEMO_DB,COMPUTE_WH warehouse and PUBLIC schema, but he is not authorized to view any tables available inside the Public schema created by ACCOUNTADMIN user.
• Though user has access to create the new tables/views/schema but can not access/view the tables in PUBLIC schema.

Firstly, Login to the Master account: Provide ‘SELECT’ Privileges on Tables to Custom Role.

 grant select on all tables in schema DEMO_DB.PUBLIC to role custom;

Secondly, Login to the sachintest user:

• Now User is able to view all the tables in PUBLIC schema and also able to Query them but not authorized to perform any DML as we have granted only SELECT privileges to the custom Role.

Thirdly, Login to the Master account: Provide ALL privileges to Tables to Custom Role.

grant all privileges on all tables in schema DEMO_DB.PUBLIC to role custom;

Fourth, Login to the sachintest user:
• User is able to perform DDL/DML operations on the table.

Fifth, Login to the Master account with default SYSADMIN role:
• User is not able to see the objects created by sachintest user. With role ACCOUNTADMIN the user can see the objects.

• Though you are able to view the table created by sachintest user via ACCOUNTADMIN role but query on this table lead to below SQL access control error: Insufficient privileges to operate on table ‘TEST1’.
• Assign the role to a higher-level role in a role hierarchy. Assigning the custom role to the SYSADMIN role. The SYSADMIN role inherits any object privileges granted to
the custom role: