3
1
During this post we will discuss the significance of Database role in snowflake.
Scenario 1: Consider a scenario there is SALES_ANALYST Account role exists in our Snowflake Account. This Role is authorized to have access on Database SALES_DB, Schema inside the DB, and all respective tables. To allow access on SALES_DB, will run the below GRANT commands accordingly.
Emphatically run the below GRANTS:
GRANT ALL ON DATABASE SALES_DB TO ROLE SALES_ANALYST;
GRANT ALL on schema SALES_DB. PUBLIC to ROLE SALES_ANALYST;
GRANT ALL ON ALL TABLES IN SCHEMA SALES_DB. PUBLIC TO ROLE SALES_ANALYST;
However, Assume a few more Roles needs to be created inside the Account having access on SALES_DB as well as on other DB based on their business needs.
E.g. Say, SALES_READER, SALES_DEVELOPER, SALES_FUNCTIONAL, SALES_ADMIN roles are new entrant to Snowflake. These Roles require access to the SALES_DB database. However, One possible approach is that we can create Role hierarchy and this way all SALES_ANALYST privileges would be passed to Role in hierarchy. But what if all these Roles are at same level and so Role Hierarchy is not recommended approach. Hence, to provide access to the SALES_DB, the above GRANT command has to be written for each role. Therefore, this becomes a challenge if we see a higher number of new roles and issuing the same GRANT command for each role seems irritating.
Therefore, Database Role plays a significant Role and helps in avoiding writing the same number of Grant statements again and again.
Eventually,Grant DB role to Account Role.
USE DATABASE SALES_DB;
CREATE OR REPLACE DATABASE ROLE DB_ACCESS.
GRANT ALL on schema SALES_DB.PUBLIC to DATABASE role DB_ ACCESS;
GRANT SELECT ON ALL TABLES IN SCHEMA SALES_DB.PUBLIC TO DATABASE ROLE DB_ ACCESS;
GRANT DATABASE ROLE DB_ ACCESS to role SALES_ANALYST;
Likewise, For every other Roles we can grant the Database Roles only:
GRANT DATABASE ROLE DB_ ACCESS to role SALES_ READER;
GRANT DATABASE ROLE DB_ ACCESS to role SALES_ DEVELOPER;
Scenario2:
Scenario2: Some of the Snowflake Account has very complex Role because of the Custom Roles. Therefore, Adding or Removing the privileges from the Role in such Complex hierarchy is not recommended or requires too much brainstorming.
Nevertheless,What if we have Database Roles in place and assign the required privileges to this Database Role only. This way simplifies the whole process and in case of any future modification can be handle simpler.
E.g. Say to implement the below hierarchy:
Firstly,Analyst (SELECT)–>Data Modeler (INSERT,UPDATE)–>Data Admin(INSERT,UPDATE,DELETE)
Secondly,Junior Analyst(SELECT)–>Data Modeler (INSERT,UPDATE)–>Data Admin(INSERT,UPDATE,DELETE)
ANALYST:
CREATE or replace ROLE ANALYST;
create or replace user ANALYST password = 'ANALYST' must_change_password = false;
grant modify,monitor,operate,usage on warehouse COMPUTE_WH to role ANALYST;
USE DATABASE SALES_DB
CREATE OR REPLACE DATABASE ROLE DB_ACCESS;
GRANT ALL on schema SALES_DB.public to DATABASE role DB_ACCESS;
GRANT SELECT ON ALL TABLES IN SCHEMA SALES_DB.PUBLIC TO DATABASE ROLE DB_ACCESS;
GRANT DATABASE ROLE DB_ACCESS to role ANALYST;
GRANT ROLE ANALYST TO USER ANALYST;
JUNIOR ANALYST:
CREATE or replace ROLE J_ANALYST;
create or replace user J_ANALYST password = 'JANALYST' must_change_password = false;
grant modify,monitor,operate,usage on warehouse COMPUTE_WH to role J_ANALYST;
//No repetitive GRANT statement, just assign Database Role
GRANT DATABASE ROLE DB_ACCESS to role J_ANALYST;
GRANT ROLE J_ANALYST TO USER J_ANALYST;
Data Modeler:
CREATE or replace ROLE D_MODELER;
create or replace user D_MODELER password = 'D_MODELER' must_change_password = false;
grant modify,monitor,operate,usage on warehouse COMPUTE_WH to role D_MODELER;
CREATE OR REPLACE DATABASE ROLE MOD_ACCESS;
GRANT ALL on schema SALES_DB.public to DATABASE role MOD_ACCESS;
GRANT INSERT,UPDATE ON ALL TABLES IN SCHEMA SALES_DB.PUBLIC TO DATABASE ROLE MOD_ACCESS;
//Setting up the hierarchy,In future if we need to change in any privilges we will handle at Database Role only, No need to touch the Account Role:
GRANT DATABASE ROLE DB_ACCESS to DATABASE role MOD_ACCESS;
GRANT DATABASE ROLE MOD_ACCESS to role D_MODELER;
DATA Admin:
CREATE or replace ROLE DATA_ADMIN;
create or replace user DATA_ADMIN password = 'DATA_ADMIN' must_change_password = false;
grant modify,monitor,operate,usage on warehouse COMPUTE_WH to role DATA_ADMIN;
CREATE OR REPLACE DATABASE ROLE ADMIN_ACCESS;
grant ALL on schema SALES_DB.public to DATABASE role ADMIN_ACCESS;
GRANT DELETE ON ALL TABLES IN SCHEMA SALES_DB.PUBLIC TO DATABASE ROLE ADMIN_ACCESS;
//Two Level Hierarchy
//Finally, Grant above two DB roles to new DB Role.
GRANT DATABASE ROLE MOD_ACCESS to DATABASE role ADMIN_ACCESS;
GRANT DATABASE ROLE ADMIN_ACCESS to role DATA_ADMIN;
