ADA-C01 Question 28
Single answerGrant access to specific objects within a database that require privilege inheritanceA Snowflake administrator needs to let the ANALYST role query only the SALES_DB.REPORTING.MONTHLY_SUMMARY table. The company follows least-privilege practices and does not want ANALYST to have broad access to other schemas or objects. The table already exists. Which set of grants is required for ANALYST to successfully run SELECT statements against that table?
- A
GRANT USAGE ON DATABASE SALES_DB TO ROLE ANALYST; GRANT USAGE ON SCHEMA SALES_DB.REPORTING TO ROLE ANALYST; GRANT SELECT ON TABLE SALES_DB.REPORTING.MONTHLY_SUMMARY TO ROLE ANALYST;
- B
GRANT SELECT ON TABLE SALES_DB.REPORTING.MONTHLY_SUMMARY TO ROLE ANALYST; GRANT REFERENCES ON SCHEMA SALES_DB.REPORTING TO ROLE ANALYST;
- C
GRANT USAGE ON DATABASE SALES_DB TO ROLE ANALYST; GRANT SELECT ON ALL TABLES IN SCHEMA SALES_DB.REPORTING TO ROLE ANALYST;
- D
GRANT OWNERSHIP ON TABLE SALES_DB.REPORTING.MONTHLY_SUMMARY TO ROLE ANALYST; GRANT USAGE ON DATABASE SALES_DB TO ROLE ANALYST;
Show answer and explanation
Correct answer: A
Explanation
In Snowflake, access to a specific object requires both the object privilege and the ability to traverse its parent containers. For a table, that means the role needs USAGE on the database, USAGE on the schema, and SELECT on the table. This is a core example of privilege inheritance and container-level access requirements in Snowflake RBAC. Granting only the table privilege is insufficient because the role cannot access the object without database and schema visibility. Conversely, granting privileges on all tables in a schema or assigning OWNERSHIP would exceed least-privilege requirements. Snowflake documentation on access control and object privileges consistently emphasizes that privileges on child objects do not eliminate the need for USAGE on parent database and schema objects.
- A. Correct.
Correct. To query a specific table in Snowflake, the role must have privilege inheritance through the container hierarchy: USAGE on the database, USAGE on the schema, and the object privilege itself such as SELECT on the table. Without USAGE on both parent containers, the role cannot resolve and access the table even if SELECT is granted directly on it. This is the minimum required set for the stated requirement.
- B. Incorrect.
Incorrect. SELECT on the table alone is not sufficient because Snowflake requires USAGE on the parent database and schema for object access. REFERENCES on a schema does not replace USAGE and is not the required privilege for running SELECT statements. This option reflects a common misunderstanding that object-level privileges alone are enough.
- C. Incorrect.
Incorrect. This grants broader access than required because SELECT ON ALL TABLES IN SCHEMA gives access to every table in the REPORTING schema, not just MONTHLY_SUMMARY. Also, it is missing an explicit USAGE grant on the schema, which is still required. This violates the least-privilege goal in the scenario.
- D. Incorrect.
Incorrect. OWNERSHIP is the highest privilege on the object and is far more permissive than necessary for simply querying a table. In addition, this option omits USAGE on the schema, so access would still not be properly inherited. Choosing this option would both overprivilege the role and fail to meet the access path requirements.