What you will be able to do
- Trace how privileges flow up a role hierarchy and say who does and does not inherit them
- Apply Snowflake's best practices for ACCOUNTADMIN, SECURITYADMIN, USERADMIN and SYSADMIN
- Design access roles and functional roles and attach the hierarchy to SYSADMIN
- Choose between account roles, database roles and secondary roles for a given access requirement
- Map PCI, PII and PHI requirements to the Snowflake edition, the classification category and the protection feature that address them
Key concept
Role hierarchy and privilege inheritance — When you grant one role to another, the parent role gains every privilege the child role holds. Building chains of these grants is how Snowflake designs least-privilege access that is still easy to manage.
1.RBAC, ownership and privilege inheritance
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), you grant privileges to roles and then grant the roles to users. In user-based access control (UBAC), you grant privileges straight to users. UBAC only counts in a session when USE SECONDARY ROLES is set to ALL. In practice, most designs use RBAC. A securable object stays inaccessible unless a grant allows access, and each object is owned by exactly one role. By default, that is the role that created it.
Granting a role to another role creates a hierarchy, and privileges move upward through it. In the documented example, Role 3 is granted to Role 2, Role 2 to Role 1, and Role 1 to User 1. User 1 ends up with all three privileges. Inheritance happens only through these grants. Owning a role (holding OWNERSHIP on the role object) is a separate relationship and gives you none of that role's privileges. No role sits above the authorization checks. Even ACCOUNTADMIN can only see and manage objects when it, or a role below it in the hierarchy, holds the needed privileges.
Checkpoint 1 of 7· Check yourself
Role ADMIN_X holds the OWNERSHIP privilege on role ANALYST_R. ANALYST_R has SELECT on a sales table. ADMIN_X has no other privileges on that table. Can ADMIN_X query the table?
Ownership of a role is a separate relationship from inheritance. Privileges move up only when one role is granted to another.
“A role owner (the role that has the OWNERSHIP privilege on the role) does not inherit the privileges of the owned role.”Source: docs.snowflake.com
Sources1
2.System-defined roles and their best practices
Every account comes with a few system-defined roles. You cannot drop them, and you cannot revoke the privileges Snowflake gives them. They form a fixed hierarchy with ACCOUNTADMIN at the top. ORGADMIN is not part of this hierarchy. It manages operations at the organization level.
| Role | Responsibility | Position |
|---|---|---|
| ACCOUNTADMIN | Account-level parameters, billing and credit data, stopping running SQL statements | Top of the system hierarchy |
| SECURITYADMIN | Holds the global MANAGE GRANTS privilege, so it can grant or revoke privileges on any object | Child of ACCOUNTADMIN; parent of USERADMIN |
| USERADMIN | Creates and manages users and roles | Child of SECURITYADMIN |
| SYSADMIN | Creates warehouses, databases and all database objects | Child of ACCOUNTADMIN; recommended parent of custom role hierarchies |
Best practices for ACCOUNTADMIN: give it to only a few people, require MFA for all of them, and assign it to at least two users so they can reset each other's passwords. Snowflake's own reset procedure for this role can take up to two business days. Don't make ACCOUNTADMIN anyone's default role, and don't use it to create objects or to run automated scripts. You can add privileges to system roles, but Snowflake advises against it. Grant the extra privileges to a custom role and then grant that custom role to the system role. Above all, put your custom roles under SYSADMIN. A custom role starts out isolated. If it never reaches SYSADMIN through the hierarchy, system administrators cannot manage the objects it owns. Only roles with MANAGE GRANTS can see those objects and change their grants. If you want to stop object owners from making grant decisions themselves, use managed access schemas. In a managed access schema, only the schema owner or a role with MANAGE GRANTS can grant privileges on the schema's objects.
Checkpoint 2 of 7· Check yourself
A data team creates custom role ETL_OWNER and uses it to create dozens of tables. ETL_OWNER was never granted to any other role. What happens when a SYSADMIN user tries to alter those tables?
Privileges reach SYSADMIN only through the hierarchy. An isolated custom role keeps its objects out of the system administrators' reach.
“if a custom role is not assigned to SYSADMIN through a role hierarchy, the system administrators cannot manage the objects owned by the role.”Source: docs.snowflake.com
3.Functional roles compared to access roles
The recommended pattern has two layers. Access roles hold privileges on database objects or account objects such as warehouses. Functional roles match business functions like accountant or analyst. You don't grant object privileges to functional roles directly. Instead, you grant them the access roles they need, and you grant the top-level functional roles to SYSADMIN. Snowflake itself treats the two kinds of role identically. The difference is only in how you use them to bundle permissions for groups of users. In the documented example, the fin database holds payroll data and the hr database holds employee data. Accountants need read-write access to fin and read-only access to hr. Analysts need read-only access to both.
GRANT USAGE ON DATABASE fin TO ROLE db_fin_rw; GRANT USAGE ON ALL SCHEMAS IN DATABASE fin TO ROLE db_fin_rw; GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN DATABASE fin TO ROLE db_fin_rw;The access role needs USAGE on the database and the schemas, not just SELECT on the tables. Every table sits inside a schema inside a database, so querying a table requires USAGE on both containers plus SELECT on the table. Next, the access roles are granted to the functional roles, and the functional roles are granted to SYSADMIN. Users only ever receive functional roles.
GRANT ROLE db_fin_rw TO ROLE accountant; GRANT ROLE db_hr_r TO ROLE analyst; GRANT ROLE db_fin_r TO ROLE analyst;Checkpoint 3 of 7· Fill the gap
This statement completes the example hierarchy so that administrators can manage everything below the functional roles. Which role goes in the blank?
GRANT ROLE accountant,analyst TO ROLE ? ;The best practice is to grant the highest-level functional roles to SYSADMIN, which can then manage the objects throughout the hierarchy.
Source: docs.snowflake.comSources2
4.Database roles: access scoped to one database
A database role works like a custom account role, except its scope is limited to one database. You can only grant it privileges on objects in that same database, including OWNERSHIP. The exception is OWNERSHIP on the database itself, which only an account role can hold. This lets database owners manage access inside their own databases independently. They can build hierarchies of database roles and grant the top one to an account role. That grant automatically gives the account role USAGE on the database. Database roles also matter for Secure Data Sharing. Without them, a consumer grants IMPORTED PRIVILEGES and gets all-or-nothing access to the share. With several database roles in a shared database, the provider can split the shared objects into subsets, and the consumer can map each subset to different account roles.
Database roles have strict limits. You cannot activate one in a session as either a primary or a secondary role. Its privileges become usable only after it is granted to an account role. You cannot grant an account role to a database role. If a database role is granted to a share, you cannot grant other database roles to it.
Checkpoint 4 of 7· Check yourself
An architect wants account role REPORTING to sit underneath database role SALES_DB.READER so the database role inherits REPORTING's warehouse privileges. What happens?
Grants go one way only: database roles can be granted to account roles, but not the other way around.
“Account roles cannot be granted to database roles in a role hierarchy.”Source: docs.snowflake.com
Sources2
5.Primary and secondary roles in a session
Every session has exactly one primary role. Snowflake picks it in this order: the role named in the connection (if the user has been granted it), otherwise the user's default role, otherwise PUBLIC. On top of that, a session can activate any number of secondary roles with USE SECONDARY ROLES. The user can then act with the combined privileges of the primary role and all secondary roles. This is useful when there are many fine-grained roles, for example for a cross-database join that would otherwise need a new parent role over two database-specific roles. There is one asymmetry to know. CREATE statements are authorized by the primary role alone, and the new object is owned by the primary role. Every other action can use privileges from any active role, including DDL on an object that a secondary role owns.
Checkpoint 5 of 7· Check yourself
A user's primary role is ANALYST. Their active secondary roles include LOADER, which has CREATE TABLE on schema STAGE. ANALYST does not. What happens when the user runs CREATE TABLE in STAGE?
Secondary roles can authorize queries, DML and DDL on objects they own, but they never authorize CREATE <object>.
“Authorization to execute CREATE <object> statements comes from the primary role only.”Source: docs.snowflake.com
Checkpoint 6 of 7· Exam question
A design team grants SELECT on a schema to role ANALYST_ACCESS, grants ANALYST_ACCESS to role ANALYST_FUNCTIONAL, and grants ANALYST_FUNCTIONAL to role BI_TEAM. A user whose only granted role is BI_TEAM runs a query against the schema. What determines whether the query succeeds?
Correct answer: A — BI_TEAM inherits the SELECT privilege through the chain of granted roles, so the query succeeds without further grants because privileges flow upward through every hop in the hierarchy.
- A. Snowflake privilege inheritance flows upward through the entire chain of granted roles, so any role that has been granted a role, directly or transitively, inherits every privilege held by that role. Since BI_TEAM sits above ANALYST_FUNCTIONAL and ANALYST_ACCESS in the chain, the SELECT privilege reaches it without extra grants.
- B. This is incorrect because role grants are not scoped to which role is currently active as primary; a granted role contributes its privileges to every role above it in the hierarchy regardless of session state.
- C. This is incorrect because Snowflake's role hierarchy propagates privileges through unlimited hops, not just one level, so an extra direct grant is unnecessary when the roles are already chained.
- D. This is incorrect because primary-role inheritance already covers the full upward chain of granted roles; secondary roles add privileges from unrelated role trees, not from grandparents of the primary role.
Sources1
6.Compliance: PCI, PII and PHI
Role design is only one part of meeting a compliance requirement. The edition of the account matters too. Support for PHI data, in line with HIPAA and HITRUST CSF, requires Business Critical Edition or higher. Support for the Payment Card Industry Data Security Standard (PCI DSS) appears in the editions matrix beside it, among the features beyond Enterprise. So an account that must hold cardholder or health data should be chosen on edition first. Snowflake also guards the boundary when sharing. By default it blocks sharing from a Business Critical (or higher) account to a non-Business Critical account, and from a HIPAA-compliant account to a non-HIPAA one. A role holding the OVERRIDE SHARE RESTRICTIONS privilege can lift that block for a specific share. For HIPAA there is a contractual side as well. Snowflake does not ensure that HIPAA accounts that share data with each other have a signed Business Associate Agreement (BAA). That is left to the sharing accounts, and a missing BAA may affect the HIPAA compliance of both accounts, particularly the provider.
Next, find the sensitive columns. Sensitive data classification maps native semantic categories to compliance categories. The Payment Card Industry (PCI) category contains one semantic category, PAYMENT_CARD. The personally identifiable information (PII) category covers DATE_OF_BIRTH, DRIVERS_LICENSE, EMAIL, NAME, NATIONAL_IDENTIFIER, PHONE_NUMBER, STREET_ADDRESS and TAX_IDENTIFIER. The HIPAA category, which is how PHI is represented, adds categories such as MEDICAL_DATA and MEDICAL_SPECIALTY. Classification is an aid, not a certification: you are responsible for deciding which regulations apply to your data.
Last, protect what classification finds, and match the feature to the scenario. Column-level security hides values such as payment card numbers, social security numbers or diagnoses from roles that do not need them. With Dynamic Data Masking, a policy such as employee_ssn_mask shows the real value only to a role like PAYROLL and a fixed masked value to everyone else. With External Tokenization, data is tokenized before it is loaded, so users never see the real value unless a policy detokenizes it for an appropriate role. Row access policies limit which records a role can see. Tags can carry a classification label so that a tag-based masking policy protects every column that has the tag. Secure views are another way to expose only part of a table. Snowflake notes that they bring management challenges from the large number of views and derived dashboards, which masking policies avoid. Masking policies also separate policy administrators from object owners.
Checkpoint 7 of 7· Check yourself
Sensitive data classification finds a column of credit card numbers. Which compliance category does Snowflake map its semantic category to?
The PCI compliance category contains the PAYMENT_CARD semantic category. Card numbers are classified there, though PAYMENT_CARD also appears under GDPR and GLBA.
“Payment Card Industry (PCI) | PAYMENT_CARD | n/a”Source: docs.snowflake.com
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Activating a secondary role that has CREATE privileges lets you create objects in that schema.Why is that wrong?
Only the primary role authorizes CREATE statements and becomes the owner. Secondary roles help with every other action.
Covered in Primary and secondary roles in a session
2.A user can switch straight to a database role with USE ROLE or USE SECONDARY ROLES.Why is that wrong?
You cannot activate a database role in a session at all. Grant it to an account role and activate that account role instead.
3.ACCOUNTADMIN is a superuser that can see and modify every object in the account.Why is that wrong?
ACCOUNTADMIN needs privileges like any other role. It reaches objects only when it, or a role below it in the hierarchy, holds the privileges.
4.Any edition can hold PHI as long as masking policies and RBAC are in place.Why is that wrong?
PHI support in line with HIPAA and HITRUST is an edition requirement, not something role design or policies can substitute for.
Covered in Compliance: PCI, PII and PHI
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“Access control considers privileges assigned directly to users only when USE SECONDARY ROLE is set to ALL.”
↩︎ RBAC, ownership and privilege inheritance“As a best practice, it is not recommended to mix account-management privileges and entity-specific privileges in the same role.”
↩︎ System-defined roles and their best practices“Secondary roles are particularly useful for SQL operations such as cross-database joins”
↩︎ Primary and secondary roles in a session“If no role was specified and a default role has not been set for the connecting user, the system role PUBLIC is used.”
↩︎ Primary and secondary roles in a session“The privileges associated with a role are inherited by any roles above that role in the hierarchy.”
↩︎ Key concept“Authorization to execute CREATE <object> statements comes from the primary role only.”
↩︎ Exam trap 1“A database role can be neither a primary nor a secondary role.”
↩︎ Exam trap 2“Role 1 inherits Privileges B and C.”
↩︎ Prediction“A role owner (the role that has the OWNERSHIP privilege on the role) does not inherit the privileges of the owned role.”
↩︎ Checkpoint“if a custom role is not assigned to SYSADMIN through a role hierarchy, the system administrators cannot manage the objects owned by the role.”
↩︎ Checkpoint“Account roles cannot be granted to database roles in a role hierarchy.”
↩︎ Checkpoint“Authorization to execute CREATE <object> statements comes from the primary role only.”
↩︎ Checkpoint - 2.
“do not make ACCOUNTADMIN the default role for any users in the system”
↩︎ System-defined roles and their best practices“In a managed access schema, object owners lose the ability to make grant decisions.”
↩︎ System-defined roles and their best practices“There is no technical difference between an object access role and a functional role in Snowflake.”
↩︎ Functional roles compared to access roles“Grant permissions on database objects or account objects (such as warehouses) to access roles.”
↩︎ Functional roles compared to access roles“granting a database role to an account role implicitly grants the USAGE privilege on the database that contains the database role”
↩︎ Database roles: access scoped to one database“Note that only an account role can hold the OWNERSHIP privilege on the database itself.”
↩︎ Database roles: access scoped to one database“Note that ACCOUNTADMIN is not a superuser role.”
↩︎ Exam trap 3 - 3.
“Payment Card Industry (PCI) | PAYMENT_CARD | n/a”
↩︎ Compliance: PCI, PII and PHI“You are solely responsible for determining which regulations or laws apply to your data and ensuring your compliance with the applicable regulations or laws.”
↩︎ Compliance: PCI, PII and PHI - 4.
“Support for PHI data (in compliance with HIPAA and HITRUST CSF regulations) — requires Business Critical Edition (or higher).”
↩︎ Compliance: PCI, PII and PHI“Support for PHI data (in compliance with HIPAA and HITRUST CSF regulations) — requires Business Critical Edition (or higher).”
↩︎ Exam trap 4 - 5.
“Support for PCI DSS.”
↩︎ Compliance: PCI, PII and PHI - 6.
“By default, Snowflake prevents sharing data from a Business Critical (or higher) account to a non-Business Critical account”
↩︎ Compliance: PCI, PII and PHI“Snowflake is not responsible for ensuring that HIPAA (and HITRUST) accounts who engage in data sharing have a signed BAA with each other”
↩︎ Compliance: PCI, PII and PHI - 7.
“secure views present management challenges due to large numbers of views and derived business intelligence (BI) dashboards from each view”
↩︎ Compliance: PCI, PII and PHI - 8.
“Tokenizing data before loading into Snowflake ensures that sensitive data is never exposed unnecessarily.”
↩︎ Compliance: PCI, PII and PHI