SnowPro Advanced: Security Engineer Question 4
Single answer1.1 Design and implement access control strategies.A financial services company is redesigning access in Snowflake after an audit found that developers can see production customer data through inherited privileges. The company wants to enforce least privilege while still allowing developers to build and test objects using production schemas as references. The requirements are:
- Developers must be able to query only approved non-sensitive objects in the PROD database.
- Access to sensitive tables must be controlled centrally and consistently across multiple roles.
- Future grants should minimize ongoing administration as new objects are created.
- Object owners should not have to grant privileges directly to individual users.
Which approach BEST meets these requirements?
- A
Grant USAGE on the PROD database and schemas to a developer role, then grant SELECT on all current and future tables in the schemas to that same developer role.
- B
Create a database role in PROD for read-only access to approved objects, grant object privileges including future grants to that database role, and grant the database role to the appropriate account roles assigned to developers.
- C
Grant OWNERSHIP on the approved schemas to a shared developer role so developers can manage grants on objects they need, and use role hierarchy to inherit access to non-sensitive data.
- D
Create one custom account role per developer, grant privileges directly on approved tables to each role, and avoid role inheritance so access remains explicit.
Show answer and explanation
Correct answer: B
Explanation
The best design is to use database roles to encapsulate object privileges inside the PROD database, then grant those database roles to account roles used by developer groups. This supports least privilege, centralizes access control for approved objects, and reduces administrative effort. Future grants can be applied carefully to the database role for the appropriate object types and scopes, helping ensure new approved objects are automatically accessible without repeated manual grants. Snowflake recommends a role-based access control model in which privileges are granted to roles rather than directly to users. Database roles are particularly useful for managing access within a database and then exposing that access through account roles. In contrast, broad grants on all future tables can overexpose data, OWNERSHIP is too powerful for this use case, and per-user role management is operationally inefficient.
- A. Incorrect.
This does not best meet the requirements because granting SELECT on all current and future tables in a schema to a developer role is too broad if some tables are sensitive. It also does not provide a centralized abstraction for managing access within the database. While future grants reduce administration, this option risks exposing newly created sensitive tables unless schemas are split perfectly by sensitivity, which the scenario does not guarantee.
- B. Correct.
This is correct. Database roles are designed to manage object privileges within a database and can be granted to account roles. Using a database role for approved read-only access allows centralized control over privileges to specific non-sensitive objects, including use of future grants where appropriate. Assigning the database role to account roles used by developers supports scalable role-based access control and avoids direct grants to users. This aligns with Snowflake best practices for least privilege and manageable privilege design.
- C. Incorrect.
This is incorrect because granting OWNERSHIP to a shared developer role is excessive and conflicts with least-privilege principles. OWNERSHIP is the highest level of control over an object and enables privilege management and object changes. Allowing developers to own production schemas would increase risk rather than centralize and restrict access.
- D. Incorrect.
This is incorrect because creating a separate account role per developer and granting privileges individually does not scale and increases administrative overhead. It also works against the requirement to control access centrally and consistently across multiple roles. Snowflake best practice is to grant privileges to roles, then assign roles to users, rather than managing object access per user-equivalent role.