COF-C03 Question 142
Single answerDiscretionary access control (DAC)A Snowflake administrator is implementing discretionary access control (DAC) for a finance data mart. The role FINANCE_ANALYST needs to query the table FIN_DB.REPORTING.EXPENSES. A custom role named DATA_OWNER currently owns the schema REPORTING and the table EXPENSES. The administrator wants DATA_OWNER to be able to grant SELECT on EXPENSES directly to FINANCE_ANALYST without transferring ownership of the object. Which action should the administrator take?
- A
Grant the global MANAGE GRANTS privilege to the DATA_OWNER role.
- B
Grant the WITH GRANT OPTION on SELECT for FIN_DB.REPORTING.EXPENSES to the DATA_OWNER role.
- C
Grant OWNERSHIP on FIN_DB.REPORTING.EXPENSES to the FINANCE_ANALYST role, then let FINANCE_ANALYST grant SELECT to itself.
- D
Grant USAGE on database FIN_DB and schema REPORTING to FINANCE_ANALYST; SELECT on the table will be inherited automatically.
Show answer and explanation
Correct answer: B
Explanation
This question tests how Snowflake implements discretionary access control through delegated privileges. In DAC, access decisions can be made by object owners or by roles that have been delegated the ability to grant privileges. In Snowflake, this delegation is commonly achieved by using WITH GRANT OPTION on a specific privilege, such as SELECT on a table. That allows a role to pass the privilege to other roles without requiring a transfer of OWNERSHIP. By contrast, MANAGE GRANTS is a broader administrative capability and is more aligned with centralized grant management than narrow DAC delegation. Also, Snowflake does not automatically inherit table privileges from database or schema USAGE; those privileges are complementary, not substitutive. Snowflake documentation on access control and GRANT syntax describes the distinction between OWNERSHIP, standard object privileges, and the use of WITH GRANT OPTION for delegated grant authority.
- A. Incorrect.
Incorrect. MANAGE GRANTS is a powerful global privilege that allows a role to manage grants on objects it does not necessarily own, and it is typically associated with centralized access administration. While it could enable grant management, it is broader than required for this scenario and is not the standard DAC approach for allowing an object owner or delegate to pass along a specific privilege. The question asks how DATA_OWNER can grant SELECT directly without transferring ownership, and DAC in Snowflake supports this through grant delegation using WITH GRANT OPTION.
- B. Correct.
Correct. In Snowflake, discretionary access control can be implemented by granting a privilege with WITH GRANT OPTION, which allows the receiving role to grant that privilege to other roles. If DATA_OWNER has SELECT on the table with grant option, it can grant SELECT on FIN_DB.REPORTING.EXPENSES to FINANCE_ANALYST without changing object ownership. This is the least-privilege DAC mechanism for delegated privilege administration.
- C. Incorrect.
Incorrect. Transferring OWNERSHIP changes control of the object and is unnecessary for this requirement. OWNERSHIP is a special privilege in Snowflake and effectively gives full control over the object, which is far more access than needed. In addition, letting a consumer role own the table just so it can access the data is poor design and violates least-privilege principles.
- D. Incorrect.
Incorrect. USAGE on the database and schema is necessary for querying an object, but it does not cause table-level SELECT privileges to be inherited automatically. In Snowflake, object access requires all applicable privileges: for a table query, the role typically needs USAGE on the database, USAGE on the schema, and SELECT on the table itself.