SnowPro Associate: Platform Question 135
Single answerPrivilegesA Snowflake administrator is onboarding a new analyst role named ANALYST_RPT. Users with this role must be able to query existing tables and views in database SALES, schema MART, and they must also be able to query any future tables and views created in that schema. The company wants to follow least-privilege principles and avoid granting unnecessary write access. Which set of grants best meets these requirements?
- A
Grant USAGE on database SALES and schema MART to ANALYST_RPT, then grant SELECT on all tables and all views in schema SALES.MART, and grant SELECT on future tables and future views in schema SALES.MART.
- B
Grant OWNERSHIP on schema SALES.MART to ANALYST_RPT, then grant SELECT on future tables and future views in the schema.
- C
Grant USAGE on database SALES and schema MART to ANALYST_RPT, then grant REFERENCES on all tables and views in schema SALES.MART.
- D
Grant SELECT on database SALES and schema MART to ANALYST_RPT, then grant SELECT on all future objects in schema SALES.MART.
Show answer and explanation
Correct answer: A
Explanation
In Snowflake, querying a table or view requires both object-level and container-level privileges. Specifically, a role needs USAGE on the database and schema that contain the object, and SELECT on the table or view itself. To support access to objects created later, Snowflake provides future grants, which must be defined by object type, such as TABLES and VIEWS within a schema. A practical least-privilege approach is therefore to grant USAGE on SALES and SALES.MART, SELECT on all existing tables and views in SALES.MART, and SELECT on future tables and future views in that schema. This aligns with Snowflake access control best practices and the documented privilege model for database objects and future grants.
- A. Correct.
Correct. To query objects in Snowflake, the role needs USAGE on the parent database and schema, plus SELECT on the tables and views themselves. To cover both existing and future objects, the administrator should grant SELECT on all current tables and views and also grant SELECT on future tables and future views in the schema. This satisfies least privilege because it enables read-only access without granting object management or write privileges.
- B. Incorrect.
Incorrect. OWNERSHIP is the highest privilege on an object and is far broader than required for read-only querying. Granting OWNERSHIP on the schema would violate least-privilege principles because the role could manage and transfer privileges on the schema and potentially affect contained objects. The requirement is only to query current and future tables and views.
- C. Incorrect.
Incorrect. REFERENCES does not allow querying data. It is used for operations such as creating foreign key relationships and certain constraint-related references, not for reading table or view contents. A user with REFERENCES but without SELECT still cannot run queries against the data.
- D. Incorrect.
Incorrect. SELECT is not a valid privilege on a database or schema; database and schema access requires USAGE. Also, Snowflake future grants are granted by object type, such as future TABLES or future VIEWS, not as a generic 'all future objects' privilege in this context. This option reflects a common misunderstanding about privilege hierarchy and future grant syntax.