COF-C03 Question 143
Single answerDiscretionary access control (DAC)A Snowflake administrator creates a custom role named ANALYST_RW and grants it SELECT and INSERT on table FINANCE_DB.REPORTING.MONTHLY_RESULTS. Later, a team lead using the SECURITYADMIN role grants ANALYST_RW to user JLEE. JLEE can query and insert into the table, but when JLEE tries to grant SELECT on the same table to another analyst, the grant fails with an insufficient privileges error. Which action should the administrator take to allow JLEE to delegate that table access to other users while following Snowflake's discretionary access control model?
- A
Grant OWNERSHIP on FINANCE_DB.REPORTING.MONTHLY_RESULTS to JLEE
- B
Grant the MANAGE GRANTS global privilege to JLEE
- C
Grant SELECT on FINANCE_DB.REPORTING.MONTHLY_RESULTS to ANALYST_RW WITH GRANT OPTION
- D
Grant USAGE on schema FINANCE_DB.REPORTING to JLEE WITH GRANT OPTION
Show answer and explanation
Correct answer: C
Explanation
Snowflake uses discretionary access control (DAC) for object privileges, meaning the object owner can decide who receives access and whether recipients can further delegate that access. To let a role delegate a privilege such as SELECT on a table, the privilege must be granted WITH GRANT OPTION. Alternatively, a role with OWNERSHIP can manage grants, but that is typically excessive for this scenario. The MANAGE GRANTS global privilege is also broader than necessary and is not the best fit when the requirement is limited delegation on one object. Following least privilege and Snowflake best practice, the administrator should grant only the needed table privilege WITH GRANT OPTION to the appropriate role. See Snowflake documentation on access control, GRANT
- A. Incorrect.
Incorrect. OWNERSHIP would allow JLEE to fully control the object, including transferring ownership and managing all grants, but this is far broader than needed and violates least-privilege practices. In Snowflake, OWNERSHIP is the highest privilege on an object and is usually reserved for administrative or object-owner roles, not end users who only need to delegate a specific privilege.
- B. Incorrect.
Incorrect. MANAGE GRANTS is a powerful global privilege that allows a role to manage grants across objects, effectively bypassing normal object-owner delegation patterns. While it could enable grant management, it is not aligned with discretionary access control for a specific object and grants much more authority than necessary.
- C. Correct.
Correct. In Snowflake's discretionary access control model, a role can pass along an object privilege only if it received that privilege WITH GRANT OPTION, or if it holds OWNERSHIP on the object. Granting SELECT on the table to ANALYST_RW WITH GRANT OPTION allows any user operating through that role, including JLEE, to grant SELECT on that table to other roles or users, assuming the necessary role context is active.
- D. Incorrect.
Incorrect. USAGE on the schema is required to resolve and access objects within the schema, but granting USAGE WITH GRANT OPTION on the schema does not allow JLEE to delegate SELECT on a table. Object privileges such as SELECT must themselves be granted WITH GRANT OPTION if delegation of that specific privilege is required.