ADA-C01 Question 118
Single answer1.7 Set up and manage security administration and authorization.A Snowflake administrator is implementing a least-privilege access model for a finance analytics team. The team should be able to query existing tables in the FINANCE_DB database and create temporary working tables only in the FINANCE_SANDBOX schema. The team must not be able to grant access to other roles, create permanent objects outside the sandbox, or manage users. Which role design best meets these requirements?
- A
Grant the team role USAGE on FINANCE_DB and all schemas, SELECT on all existing and future tables in the required reporting schemas, and CREATE TEMPORARY TABLE on the FINANCE_SANDBOX schema; do not grant MANAGE GRANTS or any user administration privileges.
- B
Grant the team role OWNERSHIP on FINANCE_DB and FINANCE_SANDBOX so team members can query data and create temporary tables while relying on object ownership to prevent sharing access with others.
- C
Grant the team role SECURITYADMIN so it can manage grants for FINANCE_DB objects, and separately grant CREATE TABLE on FINANCE_SANDBOX to allow temporary table creation.
- D
Grant the team role imported privileges on FINANCE_DB, then grant CREATE TEMPORARY TABLE at the database level so the team can create temporary working tables only in FINANCE_SANDBOX.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to create a custom functional role with only the object privileges required for the use case. In Snowflake, querying data typically requires USAGE on the database and schema plus SELECT on the tables or views. To restrict object creation to a single schema, grant the appropriate create privilege at the schema level. For temporary tables, Snowflake supports CREATE TEMPORARY TABLE as a schema privilege, which is preferable to broader object-creation rights. Avoid granting powerful administrative roles such as SECURITYADMIN or high-level object privileges such as OWNERSHIP when a read/query role is sufficient. This aligns with Snowflake best practices for role-based access control, separation of duties, and least privilege as described in Snowflake documentation on access control, privileges, and system roles.
- A. Correct.
Correct. This design follows Snowflake RBAC and least-privilege practices. To query objects, the role needs USAGE on the database and relevant schemas, plus SELECT on the tables or views to be queried. To create temporary tables in only one schema, the role should receive CREATE TEMPORARY TABLE on that specific schema, not broader create privileges. By not granting MANAGE GRANTS, OWNERSHIP, SECURITYADMIN, or user management capabilities, the role cannot delegate privileges or administer users.
- B. Incorrect.
Incorrect. OWNERSHIP is the highest privilege on an object and allows extensive control, including the ability to grant privileges on owned objects. Granting OWNERSHIP on the database or schema is far broader than required and violates least-privilege principles. It would also enable permanent object management capabilities beyond temporary working tables.
- C. Incorrect.
Incorrect. SECURITYADMIN is a powerful system role intended for managing roles and grants, not for routine analyst access. Granting SECURITYADMIN would allow the team to manage access control across the account, which directly conflicts with the requirement that they must not grant access to other roles or manage users. Also, CREATE TABLE is broader than needed because the requirement is limited to temporary tables in a specific schema.
- D. Incorrect.
Incorrect. Imported privileges are used primarily for objects shared through Snowflake Secure Data Sharing and do not apply as a general mechanism for standard privilege delegation within a local database. In addition, granting CREATE TEMPORARY TABLE at the database level would not enforce creation only within FINANCE_SANDBOX; schema-level control is the appropriate way to constrain where tables can be created.