Role & Access Control is most powerful concept in snowflake and every snowflake developer must understand it.
Access Control in Snowflake
Role, Grants & Role Hierarchy in snowflakes is such an important topic and every snowflake engineer must understand it. This episode is a comprehensive & practical guide with hands-on to demonstrate to you how role, role hierarchy, default role & secondary role work in snowflake. This guide will help you to answer the following questions
- How RBAC & DAC work in snowflake?
- How many default roles are there in snowflake?
- How to setup role hierarchy in snowflake?
- Most powreful role in snowflake?
- What is the purpose of secondary role in snowflake?
Watch Role, Grants & Role Hierarchy Video
You can watch the complete hands on video tutorial
Role/Grant SQL Script
Step-1: Create Snowflake User Without Role & Default Role
The first step is to create database and schemas where all our tables and other objects will reside.
select current_account(), current_role(), current_user();
select current_role();
use role accountadmin;
-- lets create a user01 and this user will gets default role
CREATE USER user01
PASSWORD = 'Test@12$4'
COMMENT = 'this is a trial user with name user01'
MUST_CHANGE_PASSWORD = FALSE;
Step-2: Create Snowflake User With Multiple Roles
Following SQLs will help you to create a user and assign multiple roles.
-- create user02 and have multiple roles
CREATE USER user02
PASSWORD = 'Test@12$4'
DEFAULT_ROLE = "SYSADMIN"
MUST_CHANGE_PASSWORD = FALSE;
GRANT ROLE "SYSADMIN" TO USER user02;
-- adding more roles to user02
-- and refresh the user02 screen and see the result
GRANT ROLE "SECURITYADMIN" TO USER user02;
GRANT ROLE "USERADMIN" TO USER user02;
Step-3: Show User & Role Grants
This command will help you with roles assigned to a role
show grants to user user02;
show grants on user user02;
Step-4: Creating Role Hierarchy With Example
select current_account(),current_user(), current_role();
create warehouse load_wh
with
warehouse_size = 'xlarge'
warehouse_type = 'standard'
auto_suspend = 300
auto_resume = true
min_cluster_count = 1
max_cluster_count = 1
scaling_policy = 'standard';
create warehouse adhoc_wh
with
warehouse_size = 'xsmall'
warehouse_type = 'standard'
auto_suspend = 300
auto_resume = true
min_cluster_count = 1
max_cluster_count = 1
scaling_policy = 'standard';
show warehouses;
create database sales_db;
create schema sales_schema;
create table order_tables (c1 varchar);
insert into order_tables (c1) values ('by role DE_PM_ROLE');
select * from order_tables;
Step-4.1: Role Creation & Granting it
grant USAGE on database sales_db to role "DE_DEV_TEAM";
grant all privileges on schema sales_schema to role "DE_DEV_TEAM";
grant all privileges on all tables in schema sales_schema to role "DE_DEV_TEAM";
grant USAGE on warehouse load_wh to role "DE_DEV_TEAM";
grant USAGE on warehouse adhoc_wh to role "DE_DEV_TEAM";
grant USAGE on database sales_db to role "DE_QA";
grant USAGE on schema sales_schema to role "DE_QA";
grant select on all tables in schema sales_schema to role "DE_QA";
grant USAGE on warehouse adhoc_wh to role "DE_QA";
grant USAGE on database sales_db to role "DE_ANALYST";
grant USAGE on schema sales_schema to role "DE_ANALYST";
grant select on all tables in schema sales_schema to role "DE_ANALYST";
grant USAGE on warehouse adhoc_wh to role "DE_ANALYST";
Step-5:Setting Up Multi Tanent Project
use role securityadmin;
create role "ANALYTICS_HEAD" comment = 'this is analytics head';
grant role "ANALYTICS_HEAD" to role "SECURITYADMIN";
create role "SALE_PRJ_PM" comment = 'this is project manager role for sales project';
create role "MAKETING_PRJ_PM" comment = 'this is project manager role for marketing project';
grant role "SALE_PRJ_PM" to role "ANALYTICS_HEAD";
grant role "MAKETING_PRJ_PM" to role "ANALYTICS_HEAD";
create role "SALE_PRJ_DEV_TEAM" comment = 'this is developoment team for sales project';
create role "MAKETING_PRJ_DEV_TEAM" comment = 'this is developoment team marketing project';
grant role "SALE_PRJ_DEV_TEAM" to role "SALE_PRJ_PM";
grant role "MAKETING_PRJ_DEV_TEAM" to role "MAKETING_PRJ_PM";
create role "SALE_PRJ_ANALYST" comment = 'this is analyst role for sales project';
create role "MAKETING_PRJ_ANALYST" comment = 'this is analyst role for sales project';
grant role "SALE_PRJ_ANALYST" to role "SALE_PRJ_DEV_TEAM";
grant role "MAKETING_PRJ_ANALYST" to role "MAKETING_PRJ_DEV_TEAM";
create role "SALE_PRJ_QA" comment = 'this is qa role for sales project';
create role "MAKETING_PRJ_QA" comment = 'this is qa role for marketing project';
grant role "SALE_PRJ_QA" to role "SALE_PRJ_ANALYST";
grant role "MAKETING_PRJ_QA" to role "MAKETING_PRJ_ANALYST";
use role userdmin;
create user head_analytics password = 'Test@12$4' comment = 'this is a head_analytics' must_change_password = false;
create user pm_user_01 password = 'Test@12$4' comment = 'this is a pm pm_user_01' must_change_password = false;
create user pm_user_02 password = 'Test@12$4' comment = 'this is a pm pm_user_02' must_change_password = false;
create user pm_user_03 password = 'Test@12$4' comment = 'this is a pm pm_user_03' must_change_password = false;
create user pm_user_04 password = 'Test@12$4' comment = 'this is a pm pm_user_04' must_change_password = false;
create user ba_user_01 password = 'Test@12$4' comment = 'this is a ba ba_user_01' must_change_password = false;
create user ba_user_02 password = 'Test@12$4' comment = 'this is a ba ba_user_02' must_change_password = false;
create user qa_user011 password = 'Test@12$4' comment = 'this is a qa qa_user011' must_change_password = false;
create user qa_user012 password = 'Test@12$4' comment = 'this is a qa qa_user012' must_change_password = false;
create user dev_user11 password = 'Test@12$4' comment = 'this is a dev-011 user' must_change_password = false;
create user dev_user22 password = 'Test@12$4' comment = 'this is a dev-22 user' must_change_password = false;
create user dev_user33 password = 'Test@12$4' comment = 'this is a dev-33 user' must_change_password = false;
create user dev_user44 password = 'Test@12$4' comment = 'this is a dev-044 user' must_change_password = false;
create user dev_user55 password = 'Test@12$4' comment = 'this is a dev-55 user' must_change_password = false;
use role securityadmin;
grant role "ANALYTICS_HEAD" to user head_analytics;
grant role "SALE_PRJ_PM" to user pm_user_01;
grant role "SALE_PRJ_PM" to user pm_user_02;
grant role "MAKETING_PRJ_PM" to user pm_user_03;
grant role "MAKETING_PRJ_PM" to user pm_user_04;
grant role "SALE_PRJ_DEV_TEAM" to user dev_user11;
grant role "SALE_PRJ_DEV_TEAM" to user dev_user22;
grant role "SALE_PRJ_DEV_TEAM" to user dev_user33;
grant role "SALE_PRJ_DEV_TEAM" to user dev_user44;
grant role "MAKETING_PRJ_DEV_TEAM" to user dev_user33;
grant role "MAKETING_PRJ_DEV_TEAM" to user dev_user44;
grant role "MAKETING_PRJ_DEV_TEAM" to user dev_user55;
grant role "SALE_PRJ_ANALYST" to user ba_user_01;
grant role "SALE_PRJ_ANALYST" to user ba_user_02;
grant role "MAKETING_PRJ_ANALYST" to user ba_user_01;
grant role "MAKETING_PRJ_ANALYST" to user ba_user_02;
grant role "SALE_PRJ_QA" to user qa_user011;
grant role "SALE_PRJ_QA" to user qa_user012;
grant role "MAKETING_PRJ_QA" to user qa_user011;
grant role "MAKETING_PRJ_QA" to user qa_user012;
use role sysadmin;
grant create warehouse on account to role "ANALYTICS_HEAD";
grant create database on account to role "ANALYTICS_HEAD";
use role useradmin;
alter user head_analytics set DEFAULT_ROLE='ANALYTICS_HEAD';
alter user head_analytics set DEFAULT_SECONDARY_ROLES = ( 'ALL' );
Step-5:Secondary Role Concept
use role useradmin;
create user sam password = 'Test@12$4' comment = 'this sahred resource' must_change_password = false;
-- switch to the securityadmin role
use role securityadmin;
grant role "MAKETING_PRJ_DEV_TEAM" to user sam;
-- assign sam a new role called sales project qa
grant role "SALE_PRJ_QA" to user sam;
-- set his primary role as marketing project dev team member
alter user sam set DEFAULT_ROLE='MAKETING_PRJ_DEV_TEAM';
-- his secondary role as all means marketing + sales
alter user sam set DEFAULT_SECONDARY_ROLES = ( 'ALL' );