SnowPro Advanced: Security Engineer Question 26
Single answerManage privilege grants in SnowflakeA security engineer is asked to standardize access for a new analytics team in Snowflake. Team members must be able to query all current tables and views in schema PROD_DB.SALES, and they must automatically retain read access to any future tables and views created in that same schema. The engineer also wants to follow least-privilege practices and avoid granting unnecessary administrative capabilities. Which approach should the engineer take?
- A
Grant USAGE on database PROD_DB, USAGE on schema PROD_DB.SALES, SELECT on all existing tables and views in the schema, and SELECT on future tables and views in the schema to a custom role assigned to the team.
- B
Grant OWNERSHIP on schema PROD_DB.SALES to the analytics team role so the team can inherit access to all current and future objects in the schema.
- C
Grant imported privileges on database PROD_DB to the analytics team role, then grant SELECT on future tables in the SALES schema.
- D
Grant USAGE on warehouse, database, and schema to the analytics team role, and grant SELECT on the SALES schema so the role can query all current and future objects.
Show answer and explanation
Correct answer: A
Explanation
In Snowflake, object access is controlled through RBAC using privileges granted to roles. For users to query tables and views, they need USAGE on the parent database and schema and SELECT on the relevant tables and views. To ensure access persists for newly created objects, the engineer should use future grants at the schema level, such as granting SELECT on future tables and future views in PROD_DB.SALES to the team role. This is the recommended way to manage ongoing access without repeatedly granting privileges as new objects are created. Granting OWNERSHIP would be excessive and contrary to least-privilege design. Also, schema-level USAGE does not replace object-level SELECT, and imported privileges are not intended for normal databases. This aligns with Snowflake documentation on access control, future grants, and least-privilege role design.
- A. Correct.
Correct. To query objects in Snowflake, a role needs the appropriate container privileges in addition to object-level privileges. In this scenario, the role needs USAGE on the database and schema, plus SELECT on existing tables and views and SELECT on future tables and views in the schema. This meets the requirement for both current and future read access while following least privilege. A custom role is also aligned with Snowflake best practice for role-based access control.
- B. Incorrect.
Incorrect. OWNERSHIP is the highest privilege on an object and transfers full control of the schema, including the ability to manage grants and objects. This violates least-privilege principles because the team only needs read access. While schema ownership gives powerful control, it is not the appropriate mechanism for providing query access to current and future tables and views.
- C. Incorrect.
Incorrect. Imported privileges are used for specific shared databases and certain system-defined access patterns, not as a general mechanism for granting access to a standard user-created database like PROD_DB. Even if future grants were added later, the use of imported privileges here reflects a misunderstanding of their purpose.
- D. Incorrect.
Incorrect. Granting USAGE on a warehouse, database, and schema is necessary for query execution and object resolution, but SELECT cannot be granted on a schema to read tables and views inside it. SELECT must be granted on the tables and views themselves, either for existing objects and future objects through future grants. This option reflects a common misconception that schema-level SELECT cascades to contained objects.