GRANT OWNERSHIP¶

Syntax¶

For object types that are not an instance of a class:

GRANT OWNERSHIP
  { ON {
            <object_type> <object_name>
          | ALL <object_type_plural> IN { DATABASE <database_name> | SCHEMA <schema_name> }
       }
    | ON FUTURE <object_type_plural> IN { DATABASE <database_name> | SCHEMA <schema_name> }
  }
  TO { ROLE <role_name> | DATABASE ROLE <database_role_name> }
  [ { REVOKE | COPY } CURRENT GRANTS ]

For an instance of a class:

GRANT OWNERSHIP
  ON  <class_name> <instance_name>
  TO { ROLE <role_name> | DATABASE ROLE <database_role_name> }
  [ { REVOKE | COPY } CURRENT GRANTS ]

Required parameters¶

object_name

Specifies the identifier for the object on which you are transferring ownership.

object_type

Specifies the type of object.

One of the following:

• AGGREGATION POLICY

• ALERT

• AUTHENTICATION POLICY

• COMPUTE POOL

• DATA METRIC FUNCTION

• DATABASE

• DATABASE ROLE

• DYNAMIC TABLE

• EVENT TABLE

• EXTERNAL TABLE

• EXTERNAL VOLUME

• FAILOVER GROUP

• FILE FORMAT

• FUNCTION

• GIT REPOSITORY

• HYBRID TABLE

• ICEBERG TABLE

• IMAGE REPOSITORY

• INTEGRATION

• MATERIALIZED VIEW

• NETWORK POLICY

• NETWORK RULE

• PACKAGES POLICY

• PIPE

• PROCEDURE

• MASKING POLICY

• PASSWORD POLICY

• PRIVACY POLICY

• PROJECTION POLICY

• REPLICATION GROUP

• ROLE

• ROW ACCESS POLICY

• SCHEMA

• SESSION POLICY

• SECRET

• SEQUENCE

• SNAPSHOT

• STAGE

• STREAM

• TABLE

• TAG

• TASK

• USER

• VIEW

• WAREHOUSE

object_type_plural

Plural form of object_type (e.g. TABLES, VIEWS).

Note that bulk grants on pipes are not allowed.

role_name

The identifier for the role to which the object ownership is transferred.

database_role_name

The identifier for the database role to which the object ownership is transferred. If the identifier is not fully qualified (in the form of db_name.database_role_name, the command looks for the database role in the current database for the session.

Ownership is limited to objects in the database that contains the database role.

Optional parameters¶

[ REVOKE | COPY ] CURRENT GRANTS

Specifies whether to remove or transfer all existing outbound privileges on the object when ownership is transferred to a new role:

Note

Outbound privileges refer to any privileges granted on the individual object whose ownership is changing.

When transferring ownership of a role, current grants refers to any roles that were granted to the current role (to create a role hierarchy). If ownership of a role is transferred with the current grants copied, then the output of the SHOW GRANTS command shows the new owner as the grantor of any child roles to the current role.

REVOKE

Enforces RESTRICT semantics, which require removing all outbound privileges on an object before transferring ownership to a new role. This is intended to protect the new owning role from unknowingly inheriting the object with privileges already granted on it.

After transferring ownership, the privileges for the object must be explicitly re-granted on the role.

Note that the REVOKE keyword does not work when granting ownership of future objects of a specified type in a database or schema to a role (using GRANT OWNERSHIP ON FUTURE <object_type>).

COPY

Transfers ownership of an object along with a copy of any existing outbound privileges on the object. After the transfer, the new owner is identified in the system as the grantor of the copied outbound privileges (that is, in the SHOW GRANTS output for the object, the new owner is listed in the GRANTED_BY column for all privileges). As a result, any privileges that were subsequently re-granted before the change in ownership are no longer dependent on the original grantor role.

Revoking a privilege using REVOKE <privileges> with the CASCADE option does not recursively revoke these formerly dependent grants. The grants must be explicitly revoked.

This parameter requires that the role that executes the GRANT OWNERSHIP command have the MANAGE GRANTS privilege on the account.

Default: None. Neither operation is performed on any existing outbound privileges.

Note

A GRANT OWNERSHIP statement fails if existing outbound privileges on the object are neither revoked nor copied.

Usage notes¶

• You cannot transfer the OWNERSHIP privilege for the following objects:

  • APPLICATION ROLE

  • CONNECTION

    Only the ACCOUNTADMIN role can have the OWNERSHIP privilege on a connection object.

  • Instances of a class.

  • SERVICE

  • SHARE

• A role that has the MANAGE GRANTS privilege can transfer ownership of an object to any role; in contrast, a role that does not have the MANAGE GRANTS privilege can only transfer ownership from itself to a child role within the role hierarchy.

• The GRANT OWNERSHIP statement is blocked if outbound (that is, dependent) privileges exist on the object. The object owner (or a higher role) can explicitly copy all current privileges to the new owning role (using the COPY CURRENT GRANTS option) or revoke all outbound privileges on the object before transferring ownership (using the REVOKE CURRENT GRANTS option).

  For role objects, if you do not specify these clauses, the GRANT OWNERSHIP statement is not blocked when transferring a role to a new owner role. The new owner role is updated. However, a SHOW GRANTS OF ROLE transferred_role command shows two rows for the transferred role being granted to the same user:

  • In the granted_by column, the value in one row is for the grant by the original owner role.

  • In the granted_by column, the value in the other row is for the grant by the new owner role.

  Snowflake prevents the GRANT OWNERSHIP … REVOKE CURRENT GRANTS command on a shared database. For details, see the Shared database example in this topic.

• The transfer of ownership only affects existing objects at the time the command is issued. Any objects created after the command is issued are owned by the role in use when the object is created.

• Transferring ownership of objects of the following types is blocked unless additional conditions are met:

  Pipes:

  The pipe must be paused.

  Tasks:

  You must suspend the scheduled task. Snowflake suspends all tasks in the container automatically if all tasks in a specified database or schema are transferred to another role. Tasks transferred to the same role using the COPY CURRENT GRANTS option are also suspended automatically. For more information, see Task security.

• When future grants on the same object type are defined at both the database and schema level, the schema-level grants take precedence over the database-level grants, and the database level grants are ignored.

• To grant ownership on a materialized view, use GRANT OWNERSHIP ON VIEW.... There is no separate GRANT OWNERSHIP ON MATERIALIZED VIEW statement.

• You cannot transfer the OWNERSHIP privilege on a share, nor can you transfer the OWNERSHIP privilege on a connection. Only the ACCOUNTADMIN role can own the connection.

• For granting the OWNERSHIP privilege on dynamic tables, ensure the receiving role has the USAGE privilege on the database and schema that contains the dynamic table, and on the warehouse used to refresh the table. Otherwise, subsequent scheduled refreshes fail.

• For granting the OWNERSHIP privilege on future dynamic tables:

  • If the dynamic table is set to initialize on creation (that is, INITIALIZE = ON_CREATE), ensure the new role has sufficient privileges on referenced objects. Otherwise, the initial refresh fails and results in an error stating that the object cannot be found.

  • If the dynamic table is set to initialize on schedule (that is, INITIALIZE = ON_SCHEDULE), ensure the new role has sufficient privileges on referenced objects. Otherwise, the subsequent scheduled refreshes fail.

• When you transfer ownership of an Apache Iceberg™ table to a different role, Snowflake doesn’t transfer the OWNERSHIP privilege on the external volume (and catalog integration if the table is externally managed) associated with the table.

  To give the target role full control over the table and its related objects, you must grant the OWNERSHIP privilege on the external volume and catalog integration to the role.

• Database roles:

  Ownership can only be transferred on objects in the same database as the database role.

• Transferring ownership on an external table or its parent database blocks automatic refreshes of the table metadata by setting the AUTO_REFRESH property to FALSE. To reset the property after you transfer ownership, use the ALTER EXTERNAL TABLE command.

Examples¶