Snowflake Ninja

Roles, Grants & Role Hierarchy in Snowflake

Roles, Grants & Role Hierarchy in Snowflake

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

  1. How RBAC & DAC work in snowflake?
  2. How many default roles are there in snowflake?
  3. How to setup role hierarchy in snowflake?
  4. Most powreful role in snowflake?
  5. 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.

1
2
3
4
5
6
7
8
9
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.

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 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

1
2
3
4
show grants to user user02;
show grants on user user02;

Step-4: Creating Role Hierarchy With Example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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' );