SnowPro Advanced: Security Engineer Question 13
Single answerDefine and manage custom roles and least-privilege role hierarchies:A Snowflake security engineer is redesigning access for a data platform used by three groups: ETL developers, analysts, and a small data governance team. The current model grants SYSADMIN-derived roles directly to many users, and several users can both create objects and read sensitive reporting tables in production. The company wants a least-privilege design that separates object ownership from object usage, minimizes future grant maintenance, and allows analysts to query only approved production schemas. Which approach best meets these requirements?
- A
Create functional access roles such as ETL_DEV_RW, ANALYST_RO, and GOV_AUDIT; grant object privileges to those roles; create separate functional owner roles for databases, schemas, and pipelines; then grant the access roles to user-facing roles assigned to people.
- B
Grant SYSADMIN to ETL developers and analysts, then use future grants on databases and schemas so users automatically inherit the required permissions without additional role design.
- C
Create one custom role per user, grant ownership of the required schemas directly to each role, and avoid role inheritance so every user's permissions remain independent.
- D
Use a single PROD_ACCESS role for all three groups, grant USAGE on the warehouse, database, and schemas plus SELECT, INSERT, UPDATE, DELETE on all production tables, and rely on masking policies alone to enforce least privilege.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to design a layered RBAC model using custom roles that separate ownership responsibilities from usage privileges. In Snowflake, roles are the primary mechanism for access control, and best practice is to grant privileges to roles rather than directly to users. A least-privilege hierarchy commonly includes: (1) object owner roles that hold OWNERSHIP and manage objects, (2) functional access roles that hold only required privileges such as USAGE or SELECT, and (3) user-facing roles that are assigned to users and inherit the needed functional roles. This approach supports separation of duties, reduces accidental privilege escalation, and simplifies long-term administration. Snowflake documentation on access control and role hierarchy emphasizes using custom roles, granting privileges to roles, and avoiding unnecessary assignment of powerful system roles such as SYSADMIN to end users.
- A. Correct.
Correct. This design aligns with Snowflake least-privilege and role hierarchy best practices. Separating owner roles from access roles reduces risk because users who need to query data do not automatically gain the ability to alter or transfer ownership of objects. Functional access roles let administrators grant only the privileges needed for a job function, while user-facing roles aggregate those functional roles for easier assignment. Using role hierarchies also simplifies ongoing administration and supports future growth.
- B. Incorrect.
Incorrect. Granting SYSADMIN broadly violates least-privilege principles because SYSADMIN is a powerful system role intended for managing objects across the account. Although future grants can reduce administrative effort, they do not compensate for overprivileged role assignment. Analysts and ETL developers should not receive broad administrative capabilities simply for convenience.
- C. Incorrect.
Incorrect. Creating one custom role per user is difficult to manage and does not scale. Granting ownership directly to user-specific roles also conflicts with the requirement to separate ownership from usage. Snowflake best practice is typically to use role-based access control with functional roles and inheritance, not highly fragmented per-user role models except in unusual edge cases.
- D. Incorrect.
Incorrect. A single shared role for all groups does not enforce separation of duties and grants excessive DML privileges to users who may only need read access. Masking policies protect sensitive data values in certain contexts, but they are not a substitute for proper privilege design. Least privilege requires limiting object privileges themselves, not granting broad rights and relying only on downstream controls.