What you will be able to do
- Explain how DAC, RBAC and UBAC combine in Snowflake, and how a managed access schema changes who can grant privileges
- Name what each system-defined role is for, and why custom privileges belong on custom roles instead of system roles
- Pick between account roles, database roles, SNOWFLAKE database roles and application roles for a given access need
- Design a hierarchy of access roles and functional roles that rolls up to SYSADMIN
Key concept
Role hierarchy and privilege inheritance — When you grant one role to another, the parent role gets every privilege the child role holds. Least-privilege design in Snowflake comes down to deciding which narrow roles to create and where each one sits in that tree.
1.Three access models working together
Snowflake's access control mixes three models. In discretionary access control (DAC), every object has an owner, and that owner can grant access to it. In role-based access control (RBAC), privileges go to roles and roles go to users. In user-based access control (UBAC), privileges go straight to a user. UBAC only counts when the user's secondary roles are set to ALL. Most of the time you manage access through RBAC. Anything that hasn't been granted is denied.
Ownership is where DAC comes in. Each securable object has exactly one owning role, and by default that is the role that created it. Anyone who holds the owning role effectively shares control of the object, and GRANT OWNERSHIP moves ownership to another role, including a database role. In a regular schema, the owner can grant and revoke privileges on its own objects. A managed access schema takes that ability away from object owners and centralises grant decisions.
| Schema type | Object owner can grant? | Who makes grant decisions |
|---|---|---|
| Regular schema | Yes, the owner role has all privileges, including granting and revoking | The object owner role |
| Managed access schema | No, object owners lose the ability to make grant decisions | The schema owner, or a role with the MANAGE GRANTS privilege |
Checkpoint 1 of 5· Check yourself
A table in a managed access schema is owned by role ETL_OWNER. A developer who uses ETL_OWNER tries to grant SELECT on the table to ANALYST. What happens?
Managed access schemas take grant decisions away from object owners and give them to the schema owner and to roles that hold MANAGE GRANTS.
“or a role with the MANAGE GRANTS privilege can grant privileges on objects in the schema.”Source: docs.snowflake.com
Sources1
2.System-defined roles and what each one is for
Every account comes with a few system-defined roles. You can't drop them, and you can't revoke the privileges Snowflake gave them. Each one has a narrow administrative job, and they are designed to nest. ACCOUNTADMIN contains SYSADMIN and SECURITYADMIN, and USERADMIN is granted to SECURITYADMIN.
| Role | Purpose |
|---|---|
| ACCOUNTADMIN | Top-level role. Contains SYSADMIN and SECURITYADMIN. Grant it only to a limited, controlled number of users |
| SECURITYADMIN | Holds MANAGE GRANTS, so it can modify or revoke any grant. Inherits USERADMIN |
| USERADMIN | Holds CREATE USER and CREATE ROLE. Manages users and roles that it owns |
| SYSADMIN | Creates warehouses, databases and other objects. Can grant privileges on them to roles in its hierarchy |
| PUBLIC | Pseudo-role granted automatically to every user and every role. Anything it owns is available to everyone |
Two details show up repeatedly in questions. First, ACCOUNTADMIN is not a superuser. It can only see and manage objects when it, or a role below it in the hierarchy, has privileges on them. Second, MANAGE GRANTS only lets you grant and revoke. It doesn't let SECURITYADMIN create objects. To create a database role, for example, SECURITYADMIN also needs CREATE DATABASE ROLE.
You can add privileges to system roles, but Snowflake advises against it, because it mixes account-management privileges with privileges on specific objects. Grant the extra privileges to a custom role and grant that custom role to the system role instead. For ACCOUNTADMIN specifically: give it to at least two people and to a small number overall, require MFA for them, and don't make it anyone's default role. Automated scripts should run under a different role.
Checkpoint 2 of 5· Match them up
Match each system-defined role to its defining capability
Tap a term, then the definition that fits it.
Each system role has one administrative job. USERADMIN creates identities, SECURITYADMIN controls grants, SYSADMIN owns objects, and ACCOUNTADMIN sits above all of them.
“Is granted the CREATE USER and CREATE ROLE security privileges.”Source: docs.snowflake.com
3.Account roles, database roles, SNOWFLAKE database roles and application roles
Roles also differ by scope. An account role can carry privileges on any object in the account, and it is the only kind you can activate in a session. A database role is limited to one database and the objects inside it. You can't activate a database role directly, so you grant it to an account role. Custom account roles are created by USERADMIN or any role with CREATE ROLE. Custom database roles are created by the owner of the database.
| Role type | Scope | How users get it |
|---|---|---|
| Account role | Any object in the account | Granted to users and activated with USE ROLE |
| Database role | One database and its objects | Granted to an account role, because it can't be activated directly |
| Application role | Objects in a Snowflake Native App, created by the provider in the setup script | The consumer grants it to an account role |
| System application role | One Snowflake feature, such as Budgets or data metric functions | Granted to account roles at your discretion |
The shared SNOWFLAKE database is a ready-made example of database roles. Snowflake controls access to its ACCOUNT_USAGE views through four of them. To give someone part of that metadata without granting the whole database, grant the right database role to a custom account role and then grant that role to the user.
| Database role | Visibility | Example views |
|---|---|---|
| OBJECT_VIEWER | Object metadata | TABLES, COLUMNS, DATABASES |
| USAGE_VIEWER | Historical usage | METERING_HISTORY, WAREHOUSE_METERING_HISTORY |
| GOVERNANCE_VIEWER | Data governance | ACCESS_HISTORY, QUERY_HISTORY, MASKING_POLICIES |
| SECURITY_VIEWER | Security information | GRANTS_TO_ROLES, GRANTS_TO_USERS, LOGIN_HISTORY, USERS |
GRANT DATABASE ROLE OBJECT_VIEWER TO ROLE CAN_VIEWMD;Application roles follow the same pattern from the consumer's side. The Native App provider defines them, and the consumer grants an application role to one of its own account roles. Granting different application roles to different account roles gives each group of users a different level of access to the app.
GRANT APPLICATION ROLE hello_snowflake_app.app_public TO ROLE data_manager;Checkpoint 3 of 5· Check yourself
An auditor needs to query GRANTS_TO_ROLES in ACCOUNT_USAGE, and nothing else in the SNOWFLAKE database. What is the least-privilege approach?
GRANTS_TO_ROLES maps to SECURITY_VIEWER. Database roles can't be activated in a session, so they go through an account role.
“GRANTS_TO_ROLES view | SECURITY_VIEWER”Source: docs.snowflake.com
4.Functional roles, access roles and rolling up to SYSADMIN
A new custom role starts out in isolation. It isn't granted to any user or to any other role. How you connect it to other roles is what makes a design least-privilege or not. Snowflake recommends two layers. Access roles hold privileges on database objects or account objects such as warehouses. Functional roles match business functions and collect the access roles each function needs. Lower functional roles can be granted to higher ones, and the highest functional roles are granted to SYSADMIN.
Here is an example. Accountants need read-write access to the fin database and read-only access to hr. Analysts need read-only access to both. You would build fin read-write, fin read-only and hr read-only access roles. Then grant fin read-write and hr read-only to an accountant functional role, and both read-only roles to an analyst functional role. Snowflake treats the two layers identically. Calling one an access role and the other a functional role is purely a design convention.
The fix is a single grant. Once the custom role is granted to SYSADMIN, SYSADMIN inherits its privileges and indirectly owns whatever it owns. Owning a role is not the same thing. The role with OWNERSHIP on a role doesn't inherit that role's privileges, because inheritance only travels through grants in the hierarchy.
GRANT ROLE r1 TO ROLE sysadmin;Checkpoint 4 of 5· Exam question
A data platform team supports 40 analyst groups that need read access to the same curated schemas, and every quarter the groups' needs change. Security wants to avoid re-issuing object-level grants each time a group changes. Which role design best follows Snowflake's recommended separation of functional and access roles?
Correct answer: C — Grant SELECT and USAGE on the schemas to a few access roles, then grant those access roles to functional roles mirroring job functions and assign users to them.
- A. SYSADMIN owns objects and can create databases and warehouses, which is far beyond read access. Handing it to analysts violates least privilege and mixes administration with consumption.
- B. Granting object privileges straight to job-function roles couples privileges to people-facing roles. Every change then means re-issuing object grants for each group, which is the churn the team wants to avoid.
- C. This is the recommended pattern: access roles carry object privileges and functional roles aggregate them. When a group's needs change, only role-to-role grants change and object grants stay untouched.
- D. PUBLIC is granted to every user and role, so this exposes the schemas to everyone in the account. Row access policies filter rows but do not replace role design for object-level access.
Checkpoint 5 of 5· Check yourself
Which statement about access roles and functional roles in Snowflake is correct?
Snowflake has no separate object type for either. The split is a design pattern for grouping privileges.
“There is no technical difference between an object access role and a functional role in Snowflake.”Source: docs.snowflake.com
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.SECURITYADMIN holds MANAGE GRANTS, so it can create any object it needs.Why is that wrong?
MANAGE GRANTS only covers granting and revoking. To create objects, SECURITYADMIN needs the specific CREATE privilege, for example CREATE DATABASE ROLE.
2.ACCOUNTADMIN is a superuser that can manage anything, including objects owned by isolated custom roles.Why is that wrong?
ACCOUNTADMIN only manages objects that it, or a role below it, has privileges on. Grant the custom role into the hierarchy, ideally under SYSADMIN.
Covered in Functional roles, access roles and rolling up to SYSADMIN
3.The role that owns another role inherits the owned role's privileges.Why is that wrong?
Owning a role passes on no privileges. Inheritance only happens when the role is granted to a parent role.
Covered in Functional roles, access roles and rolling up to SYSADMIN
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“Role-based Access Control (RBAC): Access privileges are assigned to roles, which are in turn assigned to users.”
↩︎ Three access models working together“Access control considers privileges assigned directly to users only when USE SECONDARY ROLE is set to ALL.”
↩︎ Three access models working together“in a managed access schema, object owners lose the ability to make grant decisions.”
↩︎ Three access models working together“Each securable object is owned by a single role, which by default is the role used to create the object.”
↩︎ Three access models working together“System-defined roles cannot be dropped. In addition, the privileges granted to these roles by Snowflake cannot be revoked.”
↩︎ System-defined roles and what each one is for“Snowflake recommends granting the additional privileges to a custom role and assigning the custom role to the system-defined role.”
↩︎ System-defined roles and what each one is for“Note that database roles cannot be activated directly in a session.”
↩︎ Account roles, database roles, SNOWFLAKE database roles and application roles“Custom database roles can be created by the database owner”
↩︎ Account roles, database roles, SNOWFLAKE database roles and application roles“the provider creates the application role and grants privileges to the application role in the set up script.”
↩︎ Account roles, database roles, SNOWFLAKE database roles and application roles“You can grant the system application roles to account roles at your discretion.”
↩︎ Account roles, database roles, SNOWFLAKE database roles and application roles“By default, a newly-created role is not assigned to any user, nor granted to any other role.”
↩︎ Functional roles, access roles and rolling up to SYSADMIN“The privileges associated with a role are inherited by any roles above that role in the hierarchy.”
↩︎ Key concept“It does not give the SECURITYADMIN the ability to perform other actions such as creating objects.”
↩︎ Exam trap 1“does not inherit the privileges of the owned role. Privilege inheritance is only possible within a role hierarchy.”
↩︎ Exam trap 3“or a role with the MANAGE GRANTS privilege can grant privileges on objects in the schema.”
↩︎ Checkpoint“Is granted the CREATE USER and CREATE ROLE security privileges.”
↩︎ Checkpoint - 2.
“do not make ACCOUNTADMIN the default role for any users in the system”
↩︎ System-defined roles and what each one is for“Snowflake recommends using a role other than ACCOUNTADMIN for automated scripts.”
↩︎ System-defined roles and what each one is for“Grant permissions on database objects or account objects (such as warehouses) to access roles.”
↩︎ Functional roles, access roles and rolling up to SYSADMIN“grant the highest-level functional roles in a role hierarchy to the system administrator (SYSADMIN) role.”
↩︎ Functional roles, access roles and rolling up to SYSADMIN“Note that ACCOUNTADMIN is not a superuser role.”
↩︎ Exam trap 2“By default, not even the ACCOUNTADMIN role can modify or drop objects created by a custom role.”
↩︎ Prediction“There is no technical difference between an object access role and a functional role in Snowflake.”
↩︎ Checkpoint - 3.
“Access to schema objects in the SNOWFLAKE database is controlled by different database roles.”
↩︎ Account roles, database roles, SNOWFLAKE database roles and application roles“use the GRANT DATABASE ROLE to assign a SNOWFLAKE database role to another role”
↩︎ Account roles, database roles, SNOWFLAKE database roles and application roles“GRANTS_TO_ROLES view | SECURITY_VIEWER”
↩︎ Checkpoint