ADA-C01 Question 27
Select 2Grant access to specific objects within a database that require privilege inheritanceA Snowflake administrator needs to let the ANALYST role query only the SALES_DB.PUBLIC.ORDERS table. The company follows least-privilege practices and does not want ANALYST to have access to other schemas or future objects in the database. A junior administrator already ran GRANT SELECT ON TABLE SALES_DB.PUBLIC.ORDERS TO ROLE ANALYST, but users with ANALYST still cannot query the table. Which additional grants are required to make the access work while keeping privileges as narrow as possible?
- A
GRANT USAGE ON DATABASE SALES_DB TO ROLE ANALYST
- B
GRANT USAGE ON SCHEMA SALES_DB.PUBLIC TO ROLE ANALYST
- C
GRANT SELECT ON ALL TABLES IN SCHEMA SALES_DB.PUBLIC TO ROLE ANALYST
- D
GRANT IMPORTED PRIVILEGES ON DATABASE SALES_DB TO ROLE ANALYST
- E
GRANT OWNERSHIP ON TABLE SALES_DB.PUBLIC.ORDERS TO ROLE ANALYST
Show answer and explanation
Correct answers: A, B
Explanation
In Snowflake, granting access to a specific object inside a database requires more than the object-level privilege itself. The role must also have the ability to traverse the object hierarchy. For a table, this means the role needs USAGE on the database, USAGE on the schema, and the appropriate privilege on the table such as SELECT. Without the parent container privileges, the object grant alone is insufficient.
This is a common administration scenario because Snowflake privileges are not automatically usable unless the role can access each level in the namespace. To keep access narrow, administrators should avoid granting broad privileges such as SELECT ON ALL TABLES or schema-wide future grants when the requirement is limited to one object. Likewise, IMPORTED PRIVILEGES is only relevant for shared databases, and OWNERSHIP would be far too permissive.
This aligns with Snowflake access control best practices and documentation for object privileges and schema/database USAGE requirements: object-level privileges allow actions on the object, while USAGE on parent database and schema enables access to the object path.
- A. Correct.
Correct. To access an object inside a database, the role must have USAGE on the parent database. In Snowflake, object access depends on privilege inheritance through the container hierarchy. Even if SELECT on the table is granted, the role cannot resolve or use the object without USAGE on the database.
- B. Correct.
Correct. The role also needs USAGE on the parent schema. Snowflake requires the role to have the necessary privileges on each container in the path to the object. For querying a specific table with least privilege, USAGE on the database and schema plus SELECT on the table is the minimal practical combination.
- C. Incorrect.
Incorrect. This would grant SELECT on every table in the PUBLIC schema, which violates the requirement to limit access to only the ORDERS table. A common mistake is assuming broader schema-level table grants are required for inheritance, but only USAGE on the database and schema is needed in addition to the object privilege.
- D. Incorrect.
Incorrect. IMPORTED PRIVILEGES applies to shared databases and is used to consume privileges from a data share. It is not used for standard access control within a regular database owned in the account. This distractor targets confusion between direct object grants and privileges on consumer databases created from shares.
- E. Incorrect.
Incorrect. OWNERSHIP is the highest privilege on an object and would transfer control of the table to ANALYST, which is unnecessary and excessive. Querying a table requires SELECT, not OWNERSHIP. This option conflicts with least-privilege principles.