ADA-C01 Question 3
Single answerDomain 1.0: Snowflake Security, Role-Based Access Control (RBAC), and User Administration (31%)A Snowflake administrator is onboarding a new finance analytics team. Team members must be able to query data in the FINANCE_DB database and create temporary and permanent tables in the FINANCE_SANDBOX schema for analysis. They must not be able to grant access to other users, modify existing production schemas, or administer users and roles. The company wants to follow least-privilege and avoid assigning high-level system roles directly to end users. Which approach best meets these requirements?
- A
Grant the SYSADMIN role directly to each finance analyst, because SYSADMIN can manage database objects without requiring SECURITYADMIN.
- B
Create a custom role, grant USAGE on the FINANCE_DB database, USAGE on the target schemas, SELECT on required tables/views, and CREATE TABLE on the FINANCE_SANDBOX schema; then grant the custom role to the analysts.
- C
Grant the SECURITYADMIN role to the team so they can manage access to the FINANCE_DB objects they use, but instruct them not to create or alter roles.
- D
Create a custom role with OWNERSHIP on the FINANCE_DB database and grant that role to the analysts so they can create objects where needed without additional grants.
Show answer and explanation
Correct answer: B
Explanation
The best answer is to create a custom role with only the privileges required for the finance analytics workload. In Snowflake RBAC, best practice is to assign system-defined administrative roles sparingly and grant end users custom roles that match business functions. For querying data, users typically need USAGE on the database and schema along with object privileges such as SELECT on tables or views. For creating tables, the role needs CREATE TABLE on the target schema; restricting that privilege to FINANCE_SANDBOX prevents changes in production schemas. This design satisfies least-privilege, avoids direct assignment of high-level administrative roles, and prevents analysts from administering roles or granting access. These principles align with Snowflake documentation on access control, role hierarchy, object privileges, and best practices for separating administrative duties from functional data access.
- A. Incorrect.
Incorrect. SYSADMIN is a powerful system-defined role intended for managing objects across the account hierarchy. Granting it directly to analysts violates least-privilege and gives far more access than required, including the ability to create and manage many objects beyond the finance sandbox. Although SYSADMIN does not manage users and roles like SECURITYADMIN, it is still too broad for this use case.
- B. Correct.
Correct. This is the recommended RBAC design in Snowflake: create a custom functional role aligned to the team's job duties and grant only the minimum required privileges. To query objects, users need appropriate USAGE privileges on the database and schema, plus SELECT on the tables or views. To create permanent tables in a schema, CREATE TABLE on that schema is required; temporary tables also require the ability to create tables in the schema. This approach avoids granting role-management capabilities and limits object creation to the designated sandbox schema.
- C. Incorrect.
Incorrect. SECURITYADMIN is intended for managing grants, users, and roles. Giving it to analysts would let them administer access and potentially escalate privileges, which directly conflicts with the requirement that they must not administer users and roles or grant access to others. This is a common misconception because people often associate data access setup with SECURITYADMIN, but that role should be tightly controlled.
- D. Incorrect.
Incorrect. OWNERSHIP is the highest privilege on an object and effectively gives full control, including the ability to transfer ownership and manage grants on that object depending on the situation. Granting OWNERSHIP on the entire database is far broader than necessary and would allow modification of production structures, which the scenario explicitly prohibits. Least-privilege calls for schema-level creation privileges in the sandbox rather than database-level ownership.