SnowPro Associate: Platform Question 133
Single answerPrivilegesA data engineering team wants analysts to query all current and future tables in the PROD_DB.SALES schema without allowing them to modify data or grant access to others. The security administrator has already created a role named ANALYST_RL. Which set of grants should the administrator use to meet the requirement with the least privilege?
- A
Grant USAGE on database PROD_DB, USAGE on schema PROD_DB.SALES, and SELECT on all and future tables in schema PROD_DB.SALES to ANALYST_RL.
- B
Grant OWNERSHIP on schema PROD_DB.SALES and SELECT on future tables in schema PROD_DB.SALES to ANALYST_RL.
- C
Grant USAGE on database PROD_DB, SELECT on schema PROD_DB.SALES, and SELECT on all tables in database PROD_DB to ANALYST_RL.
- D
Grant READ on database PROD_DB, USAGE on schema PROD_DB.SALES, and SELECT on future tables in schema PROD_DB.SALES to ANALYST_RL.
Show answer and explanation
Correct answer: A
Explanation
In Snowflake, querying a table requires privileges at multiple levels: USAGE on the database, USAGE on the schema, and SELECT on the table itself. When a requirement includes both existing and newly created tables, administrators commonly use both GRANT SELECT ON ALL TABLES IN SCHEMA and GRANT SELECT ON FUTURE TABLES IN SCHEMA. This aligns with least-privilege best practices by giving only the minimum read access required. Granting OWNERSHIP would be excessive, and broad grants at the database level would exceed the stated scope. These behaviors are consistent with Snowflake access control documentation covering object privileges, schema/database USAGE requirements, and future grants.
- A. Correct.
Correct. To query tables in Snowflake, a role needs USAGE on the parent database, USAGE on the schema, and SELECT on the tables. Because the requirement includes both current and future tables, the administrator should grant SELECT on all existing tables in the schema and SELECT on future tables in the schema. This satisfies access needs without giving write privileges or delegation capability.
- B. Incorrect.
Incorrect. OWNERSHIP is the highest privilege on an object and would far exceed least privilege. It also enables control over the schema object and is not appropriate for read-only analysts. Although SELECT on future tables helps with new objects, granting OWNERSHIP violates the requirement to avoid excessive privileges.
- C. Incorrect.
Incorrect. SELECT is not a valid privilege on a schema for this purpose; roles need USAGE on the schema to resolve objects within it. Also, granting SELECT on all tables in the entire database is broader than required because the request is limited to the SALES schema. This option over-grants access and includes an invalid privilege combination.
- D. Incorrect.
Incorrect. READ is not the standard privilege used to allow querying regular tables in Snowflake databases and schemas. For tables, the relevant privilege is SELECT, and for accessing containers the role needs USAGE on the database and schema. In addition, this option grants SELECT only on future tables, so existing tables would not be queryable.