The Person Responsible - Part 2

Please, do not move ahead if you have not read my introductory blog on roles in action.

Coming back to our blog,

let’s see what we will cover in this blog.

1. Snowflake’s default armoury
2. Create your own custom role and use it
3. Role hierarchy
4. Enforcement model

Learning privileges is very important and we will cover it in the next article

Snowflake’s default armoury

On account creation, either trial or with enterprise subscription, you get the following roles:

1. ORGADMIN
2. ACCOUNTADMIN
3. SECURITYADMIN
4. USERADMIN
5. SYSADMIN
6. PUBLIC

I will write a separate article specifically on these default roles. Let's do some hands-on now

Create your own custom role

Change your role to ACCOUNTADMIN from top right corner

Go to Account > Roles > Create Role

Or you can also fire below query

CREATE ROLE "READ_ONLY_ROLE" COMMENT = 'Role for read only users';

Example: GRANT ROLE "READ_ONLY_ROLE" TO ROLE "SYSADMIN";

That’s your role.

Now, Assign it while

1) Creating an user

And assign the role to this user while you create

2) Assigning to an existing user

• Through UI

Go to Accounts>Roles>Choose the role>Edit grants on right side panel>Select the user

• Through query -

GRANT ROLE <role name> TO <user name>;

Example: GRANT ROLE READ_ONLY_ROLE TO OUTSIDE_USER;

You can get the roles on a user with the below query-

show grants to user outside_user;

Role Hierarchy

By now, you have a good hands-on with creating and managing roles, let’s see Role Hierarchy in simple steps-

We already have a role READ_ONLY_ROLE.

1. Let’s create a role WRITE_ONLY_ROLE
2. Let’s create another role READ_WRITE_ROLE
3. Now, to create a hierarchy where the privileges of READ_ONLY_ROLE and WRITE_ONLY_ROLE will be inherited to READ_WRITE_ROLE, we need to fire the below queries-

GRANT ROLE READ_ONLY_ROLE TO ROLE READ_WRITE_ROLE;

GRANT ROLE WRITE_ONLY_ROLE TO ROLE READ_WRITE_ROLE;

The same can be done from UI, by accessing

Accounts>Roles>Select READ_ONLY_ROLE>click Edit grants on right panel>Grant this role to READ_WRITE_ROLE

Same can be done for WRITE_ONLY_ROLE.

To get the hierarchy created, you can fire the below query-

with initial_table as (

select

name as node,

grantee_name as parent

from snowflake.account_usage.grants_to_roles

where deleted_on is null and granted_on = 'ROLE'

union all

select 'ACCOUNTADMIN' as node, '' as parent

)

select

node,

parent,

sys_connect_by_path(node, ' ==> ') as path

from initial_table

start with node = 'ACCOUNTADMIN'

connect by parent = prior node;

[Caution] Careful read - Enforcement model

This is comparatively newer functionality, i.e. to add and use secondary role. Question, what is secondary role?.

Well, the current role is the primary role which can be determined in the following ways-

1. Role mentioned in the connection string used to connect Snowflake and query
2. Default role given to user while connecting or logging in
3. If no role is specified for the user, PUBLIC (system-defined role) becomes the current role

But, consider a scenario where you have a user who wants to access different tables. To access table A, user must have Role A but to access table B, user must have Role B.

With secondary roles, user need not to keep remembering the roles they need to change to access different tables.

Please note, for creating an object, secondary roles can’t be used but all other privilege is possible.

**CREATE privilege is with primary role only.

To use secondary role in the session, you can fire below query

use SECONDARY ROLES <role name>;

Next article will be on

1. All about default roles from Snowflake, their usages and points of attention
2. Learning privileges

See ya

Note: If you are a hackathon enthusiast, check out the Great AppSec Hackathon and participate! Open for all students worldwide.