ADA-C01 Question 29
Single answer1.3 Given a scenario, create and manage access control.A Snowflake administrator is asked to redesign access control for a finance data platform. The requirements are:
- Data engineers must manage warehouse usage and monitor query performance, but must not be able to read finance tables.
- Finance analysts must be able to query only curated finance schemas in the PROD_FIN database.
- Future tables created in those curated schemas must automatically be queryable by finance analysts.
- Object ownership should remain with platform administration roles, not with end-user roles.
Which approach best meets these requirements while following Snowflake access control best practices?
- A
Grant the SYSADMIN role to data engineers for warehouse management, and grant SELECT on the PROD_FIN database to finance analysts.
- B
Create a custom role for data engineers with USAGE and OPERATE on the required warehouses plus MONITOR/USAGE as needed, and a separate analyst role with USAGE on database and schemas and SELECT on all existing and future tables in the curated schemas; then grant those roles to the appropriate users.
- C
Grant OWNERSHIP on curated finance schemas to finance analysts so they can automatically access current and future tables, and grant data engineers SELECT on finance tables for troubleshooting.
- D
Use a single custom role for both data engineers and finance analysts, grant it USAGE on warehouses, USAGE on PROD_FIN, and SELECT on all schemas so administration is simplified.
Show answer and explanation
Correct answer: B
Explanation
The best answer is to use separate custom roles aligned to job functions and grant only the minimum privileges required. In Snowflake, best practice is to implement role-based access control (RBAC) using custom roles rather than assigning powerful system roles such as SYSADMIN to end users for convenience. To query objects, a role typically needs USAGE on the warehouse, database, and schema, plus object-level privileges such as SELECT on tables or views. To ensure access to newly created objects, administrators should grant privileges on future tables (and future views if needed) within the target schemas. OWNERSHIP should generally remain with controlled administrative roles because object owners can grant/revoke privileges and transfer ownership. This approach satisfies the requirements for least privilege, separation of duties, and scalable privilege administration. Relevant Snowflake documentation includes guidance on access control, role hierarchies, object privileges, future grants, and the principle of granting privileges to roles rather than directly to users.
- A. Incorrect.
Incorrect. Granting SYSADMIN to data engineers violates least-privilege because SYSADMIN is a powerful system-defined role with broad object management capabilities well beyond warehouse operation and monitoring. Also, granting SELECT on the database is not sufficient because SELECT is granted on tables/views, not at the database level in a way that allows querying all underlying table data. Analysts need USAGE on the database and schemas, plus SELECT on the relevant tables/views.
- B. Correct.
Correct. This design follows role-based access control and least-privilege principles. For data engineers, a custom role can be granted warehouse privileges such as USAGE and OPERATE, and monitoring-related privileges as appropriate, without granting access to finance tables. For finance analysts, a separate custom role can be granted USAGE on the PROD_FIN database and curated schemas, plus SELECT on all existing tables and future tables in those schemas so newly created objects are accessible automatically. Keeping OWNERSHIP with platform administration roles satisfies the requirement that end-user roles do not own objects.
- C. Incorrect.
Incorrect. Granting OWNERSHIP to finance analysts is contrary to the stated requirement that ownership remain with platform administration roles. OWNERSHIP is the highest privilege on an object and allows full control, including privilege management. Also, giving data engineers SELECT on finance tables directly conflicts with the requirement that they must not be able to read finance data.
- D. Incorrect.
Incorrect. Combining engineering and analyst access into one role violates separation of duties and least-privilege design. It increases the risk that data engineers gain unnecessary data access or analysts gain unnecessary warehouse operational privileges. Additionally, SELECT on all schemas is not the right model for restricting analysts to only curated schemas and would be overly broad.