SnowPro Advanced: Security Engineer Question 14
Single answerDefine and manage custom roles and least-privilege role hierarchies:A Snowflake security engineer is redesigning access for a new analytics program. The company wants to enforce least privilege and simplify administration. There are three groups of users:
- Data engineers who load raw files into the RAW database and need to create stages, file formats, and tables only in the RAW.INGEST schema.
- BI developers who need to create and maintain views only in the MART.PRESENTATION schema.
- Analysts who must query curated views in MART.PRESENTATION but must not have direct access to underlying RAW tables.
The current model uses broad grants to SYSADMIN-created roles, and users often receive multiple unrelated roles directly. The security engineer wants a role hierarchy that separates object ownership from functional access and minimizes future grant sprawl.
Which approach BEST meets these requirements?
- A
Create one custom role per team (ENGINEER_ROLE, BI_ROLE, ANALYST_ROLE), grant each role OWNERSHIP on its required schemas, and assign those roles directly to users so each team can manage its own objects without additional admin effort.
- B
Create access roles for job functions (RAW_INGEST_RW, PRESENTATION_VIEW_DEV, PRESENTATION_READ), grant only the minimum required privileges on specific schemas and objects, create higher-level functional roles if needed to aggregate access, and assign users only the functional roles they need.
- C
Grant analysts SELECT on the MART.PRESENTATION views and also grant REFERENCES on RAW tables so queries against the views can resolve underlying dependencies without exposing table data directly.
- D
Use a hierarchy where ANALYST_ROLE is granted to BI_ROLE and BI_ROLE is granted to ENGINEER_ROLE, then assign only ENGINEER_ROLE to all technical users to simplify administration and ensure inheritance covers future needs.
Show answer and explanation
Correct answer: B
Explanation
The best answer is to design a layered custom-role model based on job function and minimum required access, rather than broad team roles with excessive power. In Snowflake, role hierarchies are inherited upward: if role A is granted to role B, then role B inherits role A's privileges. That means roles should be composed carefully so higher-level roles aggregate only the privileges truly required. A common best practice is to separate access roles from functional roles: access roles hold object privileges, and functional roles are granted one or more access roles and then assigned to users. This reduces direct grants to users, limits privilege creep, and simplifies administration.
For this scenario, the engineer should grant schema-level and object-level privileges narrowly:
- Data engineers: privileges only in RAW.INGEST required to load data and create needed objects there.
- BI developers: privileges to create and maintain views in MART.PRESENTATION, not broad ownership of unrelated schemas.
- Analysts: usage on the relevant database and schema plus SELECT on curated views only, without direct privileges on RAW tables.
This aligns with Snowflake guidance on access control, least privilege, and role hierarchy design: create custom roles, grant privileges to roles rather than users where possible, avoid unnecessary use of OWNERSHIP, and keep role inheritance intentional and minimal.
- A. Incorrect.
Incorrect. Granting OWNERSHIP broadly to team roles violates least-privilege principles and mixes object ownership with day-to-day usage. OWNERSHIP is the highest level of control on an object in Snowflake and should be assigned carefully, typically through controlled administrative patterns rather than broad end-user functional roles. Assigning these powerful roles directly to users also increases risk and complicates future governance.
- B. Correct.
Correct. This is the strongest least-privilege design. Snowflake best practice is to use custom roles aligned to business functions and object access patterns, granting only required privileges at the lowest practical scope. Creating narrower access roles such as RAW_INGEST_RW, PRESENTATION_VIEW_DEV, and PRESENTATION_READ helps isolate permissions. If desired, these can be aggregated into higher-level functional roles for simplified user assignment. This approach also keeps object ownership separate from routine access, reducing grant sprawl and making auditing easier.
- C. Incorrect.
Incorrect. Analysts generally do not need privileges on the underlying RAW tables if they only query secure or standard views in MART.PRESENTATION and already have the necessary privileges on the view itself plus database/schema usage. Granting REFERENCES on RAW tables is not the correct mechanism for enabling normal view querying and would expand access beyond the stated requirement. This option reflects a misunderstanding of dependency resolution versus direct privilege assignment.
- D. Incorrect.
Incorrect. This hierarchy is overly permissive because higher roles inherit lower-role privileges, so granting BI_ROLE to ENGINEER_ROLE would cause engineers to inherit analyst and BI access whether needed or not. Assigning only a broad ENGINEER_ROLE to all technical users contradicts least privilege and recreates the same privilege accumulation problem the company is trying to solve.