SnowPro Associate: Platform Question 163
Single answer● Transfer ownershipA Snowflake administrator is decommissioning the role APP_DEV and needs to transfer ownership of the schema APP_DB.APP_SCHEMA to a new role, DATA_ENG_LEAD, without disrupting existing analysts who currently use privileges granted on objects in that schema. Which command should the administrator use?
- A
GRANT OWNERSHIP ON SCHEMA APP_DB.APP_SCHEMA TO ROLE DATA_ENG_LEAD COPY CURRENT GRANTS
- B
GRANT OWNERSHIP ON SCHEMA APP_DB.APP_SCHEMA TO ROLE DATA_ENG_LEAD REVOKE CURRENT GRANTS
- C
ALTER SCHEMA APP_DB.APP_SCHEMA SET OWNER = DATA_ENG_LEAD
- D
GRANT ROLE DATA_ENG_LEAD TO ROLE APP_DEV
Show answer and explanation
Correct answer: A
Explanation
In Snowflake, OWNERSHIP is a special privilege and can be held by only one role at a time for a given object. To transfer ownership of a schema, database, table, warehouse, or other securable object, administrators use GRANT OWNERSHIP ... TO ROLE. A key consideration during transfer is what happens to existing outbound privileges on the object. COPY CURRENT GRANTS preserves those grants, which is the best choice when continuity of access is required. REVOKE CURRENT GRANTS removes them, which can cause unexpected loss of access for users and roles. This scenario reflects a common administrative task during role cleanup or team transitions. Snowflake documentation on access control and GRANT OWNERSHIP explains that ownership transfer is explicit and should be planned carefully because the owner controls further grant management.
- A. Correct.
Correct. In Snowflake, ownership is transferred with the GRANT OWNERSHIP command. Using COPY CURRENT GRANTS preserves the existing outbound privileges that have been granted on the object, which helps avoid disrupting users or roles that currently depend on access to the schema. This is the appropriate choice when the goal is to change the owner while keeping current access in place.
- B. Incorrect.
Incorrect. REVOKE CURRENT GRANTS transfers ownership but removes existing outbound privileges on the object during the transfer. In this scenario, the administrator explicitly wants to avoid disrupting analysts who rely on current access, so revoking current grants would not meet the requirement.
- C. Incorrect.
Incorrect. Snowflake does not use an ALTER ... SET OWNER syntax to transfer object ownership. Ownership changes are performed through GRANT OWNERSHIP. This option reflects a common assumption based on patterns from other database platforms, but it is not valid Snowflake syntax.
- D. Incorrect.
Incorrect. Granting a role to another role changes role hierarchy and inheritance, not ownership of a securable object. APP_DEV would still own the schema unless ownership is explicitly transferred with GRANT OWNERSHIP. This option confuses role inheritance with object ownership.