What you will be able to do
- Apply Snowflake's precautions for ACCOUNTADMIN and add more account administrators safely
- Build custom roles from access roles and functional roles that map to business functions, rolled up to SYSADMIN
- Choose between account roles and database roles, and explain how primary and secondary roles authorize a session
- Delegate account-level privileges without handing out ACCOUNTADMIN
Key concept
Role hierarchy as the unit of control — In Snowflake, privileges go to roles, and roles are granted to users or to other roles. A parent role inherits everything granted to the roles below it. Fine-tuning access means deciding where in the hierarchy each privilege lives and who can activate which role.
1.Securing ACCOUNTADMIN and adding administrators
ACCOUNTADMIN is the most powerful role in an account. It includes SYSADMIN and SECURITYADMIN, it is the only role that configures account-level parameters, and it can see billing data. Even so, it is not a superuser. It can only work with objects that it, or a role below it in the hierarchy, has privileges on. That is the reason Snowflake recommends granting every custom role hierarchy to SYSADMIN, which ACCOUNTADMIN manages.
The guidance for protecting ACCOUNTADMIN fits on one list: - Assign it to only a few people. - Require MFA for every user who holds it. - Assign it to at least two users. Snowflake's reset procedure for a lost ACCOUNTADMIN password can take up to two business days, and two holders can reset each other's passwords. - Never make it anyone's default role. - Don't use it for automated scripts.
Snowflake also says ACCOUNTADMIN is meant for setup and account-level management, not for creating objects. If ACCOUNTADMIN creates an object, you have to grant privileges on it explicitly before other users can reach it.
To create additional administrators, grant ACCOUNTADMIN to a new or existing user. Set a lower role such as SYSADMIN as their default, and give each one an email address, which MFA requires. With this setup, an administrator has to switch to ACCOUNTADMIN on purpose every time they need it.
GRANT ROLE ACCOUNTADMIN, SYSADMIN TO USER user2; ALTER USER user2 SET EMAIL='user2@domain.com', DEFAULT_ROLE=SYSADMIN;Checkpoint 1 of 6· Check yourself
You are adding a second account administrator. Which configuration follows Snowflake's recommendations?
Snowflake recommends at least two ACCOUNTADMIN users, each required to use MFA, with a lower role as the default so that ACCOUNTADMIN is never active by accident.
“do not make ACCOUNTADMIN the default role for any users in the system”Source: docs.snowflake.com
2.Custom roles aligned with business functions
To follow least privilege, create custom roles that match business functions. The workflow is: create the role, grant privileges to it, grant it to users, and grant it to another role so it joins a hierarchy. USERADMIN or a higher role can create account roles, and so can any role that holds CREATE ROLE. A new role starts out isolated, so if you skip the last step, the role and everything it creates are cut off from the administrators above it.
To query a table, a role needs USAGE on a warehouse, USAGE on the database and schema that contain the table, and SELECT on the table itself.
GRANT USAGE ON WAREHOUSE w1 TO ROLE r1; GRANT USAGE ON DATABASE d1 TO ROLE r1; GRANT USAGE ON SCHEMA d1.s1 TO ROLE r1; GRANT SELECT ON TABLE d1.s1.t1 TO ROLE r1;Snowflake recommends building roles in two layers. Access roles hold the privileges on objects, such as read-only on the hr database or read-write on fin. Functional roles, such as ACCOUNTANT or ANALYST, are granted the access roles their job needs. Parent functional roles can include child functional roles, and the highest functional roles are granted to SYSADMIN. Snowflake treats both layers the same way technically. The difference is only in how you use them. In the docs' example, accountants get read-write on fin and read-only on hr, while analysts get read-only on both, so each group gets its own set of access roles.
Checkpoint 2 of 6· Put it in order
Put the steps of the custom-role workflow in the order Snowflake gives them.
- 1.Grant a set of privileges to the role
- 2.Grant the role to another role to build the hierarchy
- 3.Create a custom role
- 4.Grant the role to one or more users who need it
The configuration guide lists these four steps in this order. The final step, joining a hierarchy, is optional but highly recommended.
“Grant the role to another role to create or add to a role hierarchy. While not required, this step is highly recommended.”Source: docs.snowflake.com
Checkpoint 3 of 6· Exam question
A security audit of a production Snowflake account finds one person holding ACCOUNTADMIN, who also uses it as the default role for daily development work. Which remediation best secures the ACCOUNTADMIN role?
Correct answer: A — Grant ACCOUNTADMIN to at least two named users, require MFA and a verified email for each, and set a lower role such as SYSADMIN as their default.
- A. Correct. Having at least two MFA-protected, email-verified users avoids a single point of failure, and a non-ACCOUNTADMIN default role keeps the powerful role to deliberate, short sessions.
- B. Incorrect. A single holder is a recovery risk, and ACCOUNTADMIN should never run automated pipelines or be bound to service users; scheduled work belongs on lower-privilege custom roles.
- C. Incorrect. Objects should not be owned by ACCOUNTADMIN; create them with SYSADMIN or custom roles so ownership follows the role hierarchy. A network policy does not fix this misuse.
- D. Incorrect. Shared logins destroy accountability, and making ACCOUNTADMIN the default role exposes the highest privilege on every session instead of limiting it to when it is needed.
3.Database roles: privileges scoped to one database
An account role can hold privileges on any object in the account. A database role is narrower: it limits SQL actions to a single database and the objects inside it. You create custom database roles with the role that owns the database. You can also transfer ownership of objects to a database role with GRANT OWNERSHIP.
The constraint that matters most is that a database role can't be the active role in a session. To make its privileges usable, grant it to an account role, and users then activate that account role. That makes database roles a good fit for access roles that are tied to one database, such as a read-only role packaged with that database.
| Role type | Scope | Activated directly in a session? |
|---|---|---|
| Account role | Any object in the account | Yes |
| Database role | A single database and its objects | No. Grant it to an account role |
| Instance role | An instance of a class | No. Grant it to an account role |
| Application role | Objects in a Snowflake Native App, created by the provider | Granted by the provider's setup script |
Checkpoint 4 of 6· Check yourself
A team creates database role SALES_DB.READER and grants SELECT on the sales tables to it. Users still can't run USE ROLE SALES_DB.READER. What is the correct fix?
A database role can't be activated on its own. Its privileges reach users through an account role that it has been granted to.
“Note that database roles cannot be activated directly in a session.”Source: docs.snowflake.com
Sources3
4.Primary and secondary roles
A session can have more than one active role. The primary role, plus any secondary roles, together authorize what the user does. When a session starts, the user's default role becomes the primary role and their default secondary roles become the secondary roles. The client connection properties can override either one. During the session, USE ROLE changes the primary role and USE SECONDARY ROLES changes the secondary roles.
Secondary roles also affect privileges granted directly to a user, which Snowflake calls user-based access control (UBAC). Those direct grants count only when secondary roles are set to ALL. A user who holds a direct grant but has secondary roles turned off can't use it.
Checkpoint 5 of 6· Check yourself
SELECT on a table was granted directly to user U1, not to a role. When does Snowflake use that grant to authorize U1's queries?
Snowflake considers privileges granted directly to a user only when secondary roles are set to ALL.
“Access control considers privileges assigned directly to users only when USE SECONDARY ROLE is set to ALL.”Source: docs.snowflake.com
Sources3
5.Managing account-level permissions
Global privileges, also called account privileges, apply to the whole account. Examples are CREATE ROLE, CREATE USER, CREATE DATABASE, MANAGE GRANTS and MONITOR USAGE. Many of them, including CREATE DATABASE, CREATE WAREHOUSE, CREATE INTEGRATION and MANAGE WAREHOUSES, must be granted by ACCOUNTADMIN. MANAGE GRANTS lets a role grant or revoke privileges on objects it doesn't own. SECURITYADMIN holds it at the account level by default. It can also be granted on a single database or schema, which limits it to that container.
You can grant extra privileges to the system-defined roles, but Snowflake recommends against it. Those roles hold account-management privileges, and mixing those with privileges on specific objects in one role is discouraged. Instead, put the extra privileges in a custom role and grant that custom role to the system role.
Checkpoint 6 of 6· Check yourself
SYSADMIN also needs to apply masking policies. Following Snowflake's recommendation, how should you grant APPLY MASKING POLICY?
Snowflake advises against adding privileges directly to system-defined roles. Put the extra privilege in a custom role and grant that role to the system role.
“Snowflake recommends granting the additional privileges to a custom role and assigning the custom role to the system-defined role.”Source: docs.snowflake.com
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.ACCOUNTADMIN is a superuser, so it can always modify or drop any object in the account.Why is that wrong?
ACCOUNTADMIN reaches only objects that it, or a role below it, has privileges on. That is why custom roles must be rolled up to SYSADMIN.
2.You can activate a database role with USE ROLE, just like an account role.Why is that wrong?
Database roles can't be activated in a session. You grant them to an account role, and users activate the account role.
Covered in Database roles: privileges scoped to one database
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“Assign this role to at least two users.”
↩︎ Securing ACCOUNTADMIN and adding administrators“Snowflake recommends using a role other than ACCOUNTADMIN for automated scripts.”
↩︎ Securing ACCOUNTADMIN and adding administrators“There is no technical difference between an object access role and a functional role in Snowflake.”
↩︎ Custom roles aligned with business functions“This role alone is responsible for configuring parameters at the account level.”
↩︎ Managing account-level permissions“Note that ACCOUNTADMIN is not a superuser role.”
↩︎ Exam trap 1“By default, not even the ACCOUNTADMIN role can modify or drop objects created by a custom role.”
↩︎ Prediction“do not make ACCOUNTADMIN the default role for any users in the system”
↩︎ Checkpoint - 2.
“Ensure an email address is specified for each user (required for multi-factor authentication).”
↩︎ Securing ACCOUNTADMIN and adding administrators“creating custom roles that align with the business functions in your organization”
↩︎ Custom roles aligned with business functions“Grant the role to another role to create or add to a role hierarchy. While not required, this step is highly recommended.”
↩︎ Checkpoint - 3.
“Custom database roles can be created by the database owner (that is, the role that has the OWNERSHIP privilege on the database).”
↩︎ Database roles: privileges scoped to one database“Both the primary role and any secondary roles can be activated in a user session.”
↩︎ Primary and secondary roles“Executing a USE ROLE or USE SECONDARY ROLES statement activates a different primary role or secondary roles, respectively.”
↩︎ Primary and secondary roles“The privileges associated with a role are inherited by any roles above that role in the hierarchy.”
↩︎ Key concept“Grant database roles to account roles, which can be activated in a session.”
↩︎ Exam trap 2“Note that database roles cannot be activated directly in a session.”
↩︎ Checkpoint“Access control considers privileges assigned directly to users only when USE SECONDARY ROLE is set to ALL.”
↩︎ Checkpoint“Snowflake recommends granting the additional privileges to a custom role and assigning the custom role to the system-defined role.”
↩︎ Checkpoint - 4.
“Must be granted by the ACCOUNTADMIN role.”
↩︎ Managing account-level permissions