ADA-C01 Question 32
Single answerIdentify and apply different privileges available for each object typeA Snowflake administrator needs to let a reporting role, ANALYST_R, query only the existing tables in database FINANCE and schema MART without allowing the role to create objects or query future tables created later. The administrator wants to follow least-privilege principles and avoid granting unnecessary broad access. Which grant combination should the administrator use?
- A
Grant USAGE on database FINANCE, USAGE on schema FINANCE.MART, and SELECT on all tables in schema FINANCE.MART to ANALYST_R
- B
Grant OWNERSHIP on schema FINANCE.MART and SELECT on all tables in schema FINANCE.MART to ANALYST_R
- C
Grant USAGE on database FINANCE, READ on schema FINANCE.MART, and SELECT on all views in schema FINANCE.MART to ANALYST_R
- D
Grant IMPORTED PRIVILEGES on database FINANCE and SELECT on all tables in schema FINANCE.MART to ANALYST_R
Show answer and explanation
Correct answer: A
Explanation
In Snowflake, privileges must be granted at each required level of the object hierarchy. For a role to query a table, it generally needs USAGE on the database, USAGE on the schema, and SELECT on the table itself. When the requirement is to query existing tables only, the correct pattern is to grant SELECT ON ALL TABLES IN SCHEMA rather than SELECT ON FUTURE TABLES. This is a common administrative scenario that tests understanding of how object-type privileges differ across databases, schemas, and tables. Best practice is to avoid elevated privileges such as OWNERSHIP unless administrative control is required. Snowflake documentation on access control and GRANT syntax describes these privilege dependencies and the distinction between grants on existing objects versus future grants.
- A. Correct.
Correct. To query tables in Snowflake, a role needs USAGE on the parent database, USAGE on the parent schema, and the object-level privilege such as SELECT on the tables. Granting SELECT on all tables in the schema covers existing tables only; it does not grant access to future tables unless a separate future grant is used. This satisfies the requirement for current access only and aligns with least privilege.
- B. Incorrect.
Incorrect. OWNERSHIP is the highest privilege on an object and transfers full control, including the ability to manage grants and modify or drop the schema. That is far broader than required for a reporting role that only needs query access. Although the role could access objects, this violates least-privilege design.
- C. Incorrect.
Incorrect. READ is not a valid schema privilege for standard database schemas in this context. To access objects in a schema, the role needs USAGE on the schema. Also, granting SELECT only on views would not allow querying tables, which the scenario explicitly requires.
- D. Incorrect.
Incorrect. IMPORTED PRIVILEGES applies to shared databases and certain imported objects, not to a regular local database like FINANCE in this scenario. Even if SELECT on all tables were granted, without proper USAGE on the database and schema the role would still not have the required access path.