SnowPro Advanced: Security Engineer Question 25
Single answerManage privilege grants in SnowflakeA security engineer is asked to give the ANALYST role read access to all current and future tables in the SALES_DB.REPORTING schema. Company policy requires that analysts must be able to query existing and newly created tables without receiving broader privileges than necessary. The engineer also wants to avoid having to regrant access every time a table is created. Which action should the engineer take?
- A
Grant USAGE on database SALES_DB and schema REPORTING to ANALYST, then grant SELECT on all tables in SALES_DB.REPORTING and grant SELECT on future tables in SALES_DB.REPORTING to ANALYST.
- B
Grant OWNERSHIP on schema SALES_DB.REPORTING to ANALYST, then grant SELECT on future tables in the schema to ANALYST.
- C
Grant USAGE on database SALES_DB to ANALYST and grant SELECT on schema SALES_DB.REPORTING to ANALYST.
- D
Grant IMPORTED PRIVILEGES on database SALES_DB to ANALYST, then grant SELECT on future tables in SALES_DB.REPORTING to ANALYST.
Show answer and explanation
Correct answer: A
Explanation
In Snowflake, access to table data requires both container-level and object-level privileges. For a role to query a table, it must have USAGE on the database, USAGE on the schema, and SELECT on the table. When a requirement includes access to both existing and newly created tables in a schema, best practice is to combine GRANT SELECT ON ALL TABLES IN SCHEMA with GRANT SELECT ON FUTURE TABLES IN SCHEMA. This is a common least-privilege pattern for managing privilege grants at scale. Snowflake documentation on GRANT privileges and future grants describes this behavior, including that future grants apply only to objects created after the grant is defined, so separate grants on existing objects are still needed.
- A. Correct.
Correct. To query tables in Snowflake, a role needs USAGE on the parent database and schema, plus the object privilege itself, such as SELECT on tables. Because the requirement includes both current and future tables, the correct implementation is to grant SELECT on ALL TABLES IN SCHEMA and SELECT on FUTURE TABLES IN SCHEMA. This provides the least privilege necessary while avoiding ongoing manual grants for newly created tables.
- B. Incorrect.
Incorrect. OWNERSHIP is the highest privilege on an object and would violate the requirement to avoid broader privileges than necessary. A role does not need to own a schema to read tables in it. In addition, transferring OWNERSHIP has significant side effects and is not appropriate for simple read-only access.
- C. Incorrect.
Incorrect. Snowflake does not support granting SELECT on a schema to allow reading all tables inside it. SELECT is granted on table-like objects, not on schemas for this purpose. USAGE on the database alone is also insufficient; the role would additionally need USAGE on the schema and SELECT on the tables.
- D. Incorrect.
Incorrect. IMPORTED PRIVILEGES is used in limited scenarios such as working with shared databases, not for standard privilege management on regular databases and schemas in an account. It does not replace the need to grant USAGE and SELECT appropriately for local objects.