Author: Kanchana Velaga
Role-Based Access Control is one of the important parts of Snowflake's Access Control Framework.
RBAC allows privileges to be granted by object owners to roles, and these roles can be granted to users.
Roles can be divided into two categories. They are:
1) System-defined Roles
2) Custom Roles.
It is recommended to grant the additional privileges to a custom role and assign the custom role to the system-defined role.
System-defined Roles:
The roles that can be given by the snowflake are system-defined roles. These roles can’t be dropped.
The system-defined roles include the following:
ACCOUNT ADMIN: It is the top-level role in the account which inherits all other roles.
ORGADMIN: Role that can manage all the operations at the organizational level.
SECURITY ADMIN: Role that can manage any object grant globally. It can also create, monitor, and manage users and roles.
USER ADMIN: Role that is dedicated to user and role management.
SYSADMIN: Role that has privileges to create warehouses, databases, and other objects in an account.
PUBLIC: It is the default role that is automatically assigned to every user and every role in the account.
Custom Roles:
Roles other than the system-defined roles come under custom roles.
Custom roles can be created by the USERADMIN role or any other higher role. These can also be created by any role to which the CREATE ROLE privilege has been granted.
Privileges:
For each object, there is a set of privileges that can be granted to it.
Any privilege can be granted or revoked using the following commands:
GRANT <privileges> TO ROLE rolename
REVOKE <privileges> FROM ROLE rolename.
Future Grants:
To simplify grant management, FUTURE GRANTS are introduced.
Future grants allow roles to have access to objects that would be created in the future.
Example: If a role is to be granted read-only access to future tables in a schema. The below command can be used.
GRANT SELECT ON FUTURE TABLES IN SCHEMA schemaname TO ROLE rolename;
Requirement:
To implement the below RBAC in snowflake.
Two identical databases: RAW_DB, ANALYTICAL_DB
Schemas in each database: LANDING, TRANSFORMATION, REPORTING
RAW_DB: For development and testing
ANALYTICAL_DB: To deploy the finalized data when all the tests are done.
System defined Roles : ACCOUNT ADMIN,SYSADMIN,SECURITYADMIN,
ORGADMIN,USERADMIN
Custom Roles : RAW_DB_RW,RAW_DB_RO,
ANALYTICAL_DB_RW,ANALYTICAL_DB_RW,
DATA ENGINEER,ANALYST
The roles with read and write access to both databases(RAW_DB_RW and ANALYTICAL_DB_RW) are assigned to the role of DATA ENGINEER.
The roles with read-only access(RAW_DB_RO and ANALYTICAL_DB_RO) are assigned to the role ANALYST.
Here, the roles have been segregated into access roles and functional roles for better understanding.
Access roles are the roles that have access to the objects, and the access roles are kept under the functional roles.
The functional roles are the ones that actually perform operations on the objects.
Prerequisite Objects:
Databases:
Warehouses:
Implementation:
Grants:
Sample Output:
Conclusion:
After the successful creation of users and roles, the respective privileges will be granted.