ADA-C01 Question 12
Select 21.2 Given a set of business requirements, design access control frameworkA company is redesigning its Snowflake access model after several audit findings. The security team wants a framework that meets these business requirements:
- Data engineers must create and manage schemas, tables, and tasks only within the ETL database.
- Analysts must be able to query curated reporting schemas but must not be able to modify objects.
- The platform team wants centralized user and role administration separated from object ownership.
- Future grants should automatically apply when new tables are created in reporting schemas. Which TWO actions should the Snowflake administrator take to design the most appropriate access control framework?
- A
Use DAC only by assigning all custom roles to SYSADMIN, and let object owners grant privileges directly to end users as needed.
- B
Create functional custom roles such as ETL_ENGINEER and REPORTING_ANALYST, grant object privileges to those roles, and assign users to the roles through a separate user administration role.
- C
Use managed access schemas for the reporting schemas so object owners cannot independently grant privileges, and let the schema owner manage grants including future grants.
- D
Grant OWNERSHIP on the ETL database to all data engineers so they can fully manage any object they create without additional role design.
- E
Grant SELECT on individual reporting tables directly to analyst users, because direct grants are the simplest way to ensure read-only access.
Show answer and explanation
Correct answers: B, C
Explanation
The best design uses Snowflake role-based access control with custom functional roles and centralized privilege management. Requirement mapping:
- Data engineers need controlled create/manage capabilities in the ETL database: this is best handled by granting appropriate privileges to a custom ETL role rather than broad ownership.
- Analysts need read-only access to curated reporting schemas: grant SELECT to a reporting analyst role.
- User and role administration should be separated from object ownership: this is achieved by assigning users through administrative roles while keeping object privileges on functional roles.
- Future grants should automatically apply to new tables: future grants at the schema level support this requirement.
Managed access schemas are especially important when the organization wants schema-level governance over grants. In a managed access schema, object owners do not decide access; the schema owner or a role with MANAGE GRANTS does. This supports stronger control and cleaner separation of duties.
Relevant Snowflake best practices and documentation areas include Role-Based Access Control (RBAC), managed access schemas, future grants on schemas, and separation of duties between SECURITYADMIN-style user/role administration and SYSADMIN/custom object administration.
- A. Incorrect.
Incorrect. This approach conflicts with the requirement for centralized administration and a scalable framework. Granting privileges directly to end users leads to role sprawl, inconsistent access, and poor auditability. While Snowflake supports discretionary access control through object owners, best practice is to use role-based access control with custom roles granted to users, not direct user grants as the primary model.
- B. Correct.
Correct. This aligns with Snowflake RBAC best practices: create business or functional roles, grant object privileges to those roles, and then grant roles to users. Separating user/role administration from object ownership also supports the requirement that the platform team centrally administer users and roles without embedding object privileges into user accounts. This design is scalable and auditable.
- C. Correct.
Correct. Managed access schemas are specifically designed to centralize privilege management within the schema owner or a role with MANAGE GRANTS, rather than allowing individual object owners to grant access. This is especially appropriate for curated reporting schemas where governance and consistent future grants are required. Future grants on schemas can automatically grant SELECT on newly created tables to analyst roles.
- D. Incorrect.
Incorrect. Granting OWNERSHIP broadly to all data engineers violates least privilege and weakens governance. OWNERSHIP is the highest privilege on an object and transfers control, including the ability to grant access. Data engineers can be given the specific schema- and object-level privileges they need within the ETL database without making all engineers owners of the database itself.
- E. Incorrect.
Incorrect. Direct grants to users do provide access, but they do not satisfy the requirement for a well-designed access control framework. This creates administrative overhead and does not scale as analysts join, leave, or change responsibilities. Snowflake best practice is to grant privileges to roles and then assign roles to users.