SnowPro Associate: Platform Question 161
Single answer● Transfer ownershipA Snowflake account uses role-based access control. A data engineering team created a schema named ANALYTICS.RAW under the role ETL_ADMIN. The ETL_ADMIN role is being retired, and ownership of the schema must be transferred to the DATA_PLATFORM_ADMIN role so that this role can fully manage the schema going forward. The security administrator wants to transfer ownership without leaving ETL_ADMIN with any remaining privileges on the schema after the change. Which command should be used?
- A
GRANT OWNERSHIP ON SCHEMA ANALYTICS.RAW TO ROLE DATA_PLATFORM_ADMIN
- B
GRANT OWNERSHIP ON SCHEMA ANALYTICS.RAW TO ROLE DATA_PLATFORM_ADMIN REVOKE CURRENT GRANTS
- C
TRANSFER OWNERSHIP OF SCHEMA ANALYTICS.RAW TO ROLE DATA_PLATFORM_ADMIN
- D
ALTER SCHEMA ANALYTICS.RAW SET OWNER = DATA_PLATFORM_ADMIN
Show answer and explanation
Correct answer: B
Explanation
In Snowflake, OWNERSHIP is a special privilege that can be held by only one role at a time, and transferring it is done with GRANT OWNERSHIP. For securable objects such as schemas, a typical transfer statement is GRANT OWNERSHIP ON SCHEMA
- A. Incorrect.
This is incomplete for the stated requirement. In Snowflake, ownership is transferred with the GRANT OWNERSHIP command, but when dependent grants exist, the command requires handling current outbound privileges explicitly. To ensure the previous owner does not retain privileges through existing grants and to perform a clean transfer aligned with the scenario, the REVOKE CURRENT GRANTS clause should be included.
- B. Correct.
This is correct. Snowflake transfers ownership by using GRANT OWNERSHIP on the object to another role. The REVOKE CURRENT GRANTS clause is used to revoke existing outbound privileges on the object as part of the transfer, which aligns with the requirement to avoid leaving the old owning role with remaining privileges on that schema after the ownership change. This is the standard approach when transferring ownership cleanly.
- C. Incorrect.
This is incorrect because TRANSFER OWNERSHIP is not valid Snowflake SQL syntax for object ownership changes. A candidate might choose this because it sounds intuitive, but Snowflake uses GRANT OWNERSHIP rather than a TRANSFER OWNERSHIP command.
- D. Incorrect.
This is incorrect because Snowflake does not change object ownership with ALTER ... SET OWNER syntax. Ownership is a privilege in Snowflake and is transferred through GRANT OWNERSHIP. This distractor reflects a common assumption based on syntax from other database platforms.