SnowPro Associate: Platform Question 162
Single answer● Transfer ownershipA Snowflake administrator is decommissioning the role ETL_ADMIN and needs to transfer ownership of the table SALES.RAW.ORDERS to the role DATA_ENG without disrupting access for existing users who query the table through other roles. Which action should the administrator take?
- A
Execute GRANT OWNERSHIP ON TABLE SALES.RAW.ORDERS TO ROLE DATA_ENG COPY CURRENT GRANTS;
- B
Execute GRANT OWNERSHIP ON TABLE SALES.RAW.ORDERS TO ROLE DATA_ENG REVOKE CURRENT GRANTS;
- C
Execute TRANSFER OWNERSHIP ON TABLE SALES.RAW.ORDERS TO ROLE DATA_ENG PRESERVE GRANTS;
- D
Grant the MODIFY privilege on SALES.RAW.ORDERS to DATA_ENG, then revoke OWNERSHIP from ETL_ADMIN.
Show answer and explanation
Correct answer: A
Explanation
The correct approach is to use Snowflake's GRANT OWNERSHIP command to transfer the OWNERSHIP privilege from one role to another. When the goal is to avoid interrupting existing access, the COPY CURRENT GRANTS clause is important because it retains the object's existing privileges granted to other roles. Without that clause, a transfer can revoke those grants and break downstream access. This reflects a common administrative task in role lifecycle management and follows Snowflake's documented behavior for transferring ownership of securable objects.
- A. Correct.
Correct. In Snowflake, ownership is transferred using the GRANT OWNERSHIP command. Adding COPY CURRENT GRANTS preserves the object's existing outbound privileges so roles that currently have access to the table keep that access after the ownership transfer. This is the appropriate choice when changing the owning role while minimizing disruption.
- B. Incorrect.
Incorrect. REVOKE CURRENT GRANTS transfers ownership but removes existing outbound grants on the object. That would likely disrupt access for users and roles that currently query the table, which conflicts with the scenario requirement.
- C. Incorrect.
Incorrect. Snowflake does not use a TRANSFER OWNERSHIP command or a PRESERVE GRANTS clause for this operation. This option is plausible because it sounds intuitive, but it is not valid Snowflake syntax.
- D. Incorrect.
Incorrect. OWNERSHIP is a special privilege in Snowflake and cannot be handled by simply granting MODIFY and revoking ownership separately. Ownership transfer must be done explicitly with GRANT OWNERSHIP. Also, MODIFY does not make a role the owner of the object.