ADA-C01 Question 47
Single answerAlign usage of object access with business functionsA company is restructuring Snowflake access to better align object usage with business functions. The Finance team needs read-only access to curated tables in the FINANCE_DB database. Data Engineers need full control of schemas and tables in the same database to build and maintain pipelines, but they should not automatically gain access to raw HR data stored in a separate HR_DB database. The company also wants to simplify future onboarding so new Finance users inherit the correct permissions automatically. Which approach best meets these requirements while following Snowflake access control best practices?
- A
Grant SELECT on all tables in FINANCE_DB directly to each Finance user, and grant OWNERSHIP on FINANCE_DB to the Data Engineers role so it can manage all objects centrally.
- B
Create functional roles such as FINANCE_ANALYST and DATA_ENGINEER, grant FINANCE_ANALYST the required USAGE and SELECT privileges on FINANCE_DB objects, grant DATA_ENGINEER the necessary schema and object privileges only in FINANCE_DB, and assign users to these roles.
- C
Grant the SYSADMIN role to Data Engineers for object management and grant the PUBLIC role SELECT access to curated Finance tables so all Finance users can query them without additional role assignments.
- D
Create a single custom role with both Finance read access and Data Engineer management privileges, then assign that role to all users in both teams to reduce administration overhead.
Show answer and explanation
Correct answer: B
Explanation
The key principle is to align Snowflake object access with business functions using role-based access control rather than direct user grants or overly broad system roles. In this scenario, separate custom roles should be created for Finance consumers and Data Engineers. Finance users need read-only access, which typically requires USAGE on the database and schema plus SELECT on the relevant tables or views. Data Engineers need object management capabilities in FINANCE_DB, but those privileges should be scoped only to the database and schemas they manage, not granted globally or through powerful account-level roles like SYSADMIN unless that breadth is truly required. This design supports least privilege, separation of duties, easier onboarding, and clearer auditing. Snowflake documentation and best practices consistently recommend granting privileges to roles, assigning roles to users, and structuring roles around job functions rather than individuals.
- A. Incorrect.
This approach does not align access with business functions or least privilege. Granting privileges directly to users makes access harder to manage and audit, especially for onboarding and role changes. Granting OWNERSHIP on the database is also excessive because OWNERSHIP is the highest privilege and transfers control, which is broader than needed for pipeline maintenance.
- B. Correct.
This is the best answer. Snowflake best practice is to use role-based access control and grant privileges to roles aligned to job functions, then assign users to those roles. FINANCE_ANALYST can be granted USAGE on the database and schemas plus SELECT on the required tables or views. DATA_ENGINEER can be granted only the privileges needed in FINANCE_DB, such as USAGE on the database, USAGE and CREATE privileges on schemas, and appropriate privileges on tables, stages, or tasks as required. Because the roles are scoped by business function and database, Data Engineers do not automatically gain access to HR_DB. New Finance users can be onboarded simply by granting them the FINANCE_ANALYST role.
- C. Incorrect.
This violates least privilege and proper role design. SYSADMIN is a powerful system-defined role intended for managing objects broadly across the account, not for routine delegation to a specific functional team when a custom role would suffice. Granting SELECT to PUBLIC is also risky because PUBLIC is granted to every user and role in Snowflake, which would expose Finance data more broadly than intended.
- D. Incorrect.
Combining unrelated business functions into one role is contrary to access design best practices. It creates privilege creep, gives Finance users unnecessary administrative capabilities, and gives Data Engineers unnecessary read access intended for Finance consumers. Although it reduces the number of roles, it weakens segregation of duties and makes audits and future changes more difficult.