Categories:
User & Security DDL (Access Control)

GRANT OWNERSHIP

Transfers ownership of an object (or all objects of a specified type in a schema) from one role to another role. OWNERSHIP is a special type of privilege that can only be granted from one role to another role; it cannot be revoked. For more details, see Access Control in Snowflake.

This command is a variation of GRANT <privileges> … TO ROLE.

See also:
REVOKE <privileges> … FROM ROLE

Syntax

GRANT OWNERSHIP
   ON { objectType <object_name> | ALL schemaObjectsType IN SCHEMA <schema_name> }
   TO ROLE <name>
   [ { REVOKE | COPY } CURRENT GRANTS ]

Where:

objectType ::=
  { ROLE | USER | WAREHOUSE | DATABASE | SCHEMA | TABLE | VIEW | STAGE | FILE FORMAT | PIPE | FUNCTION | PROCEDURE | SEQUENCE }
schemaObjectsType ::=
  { TABLES | VIEWS | STAGES | FILE FORMATS | FUNCTIONS | PROCEDURES | SEQUENCES }

Required Parameters

objectType object_name or . ALL schemaObjectsType IN SCHEMA schema_name

Specifies whether to transfer ownership for a single object (of the specified type) or all schema objects of the specified type (in the specified schema):

  • For single objects, all object types are supported:

    ROLE | USER | WAREHOUSE | DATABASE | SCHEMA | TABLE | VIEW | STAGE | FILE FORMAT | PIPE | FUNCTION | PROCEDURE | SEQUENCE

  • For schema objects, only the following object types are supported:

    TABLES | VIEWS | STAGES | FILE FORMATS | PIPES | FUNCTIONS | PROCEDURES | SEQUENCES

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

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:

REVOKE

Enforces RESTRICT semantics, which require removing all outbound privileges on an object before transferring ownership to a new role. This can be accomplished by the owning role (or a higher role) for the object. 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.

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 (i.e. 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> … FROM ROLE with the CASCADE option does not recursively revoke these formerly dependent grants. The grants must be explicitly revoked.

Default: REVOKE CURRENT GRANTS

Note

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.

Usage Notes

  • 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 was created.

  • Unless you explicitly choose to copy all current privileges to the new owning role (using the COPY CURRENT GRANTS option), you must perform the following tasks to transfer ownership:

    1. Execute REVOKE <privileges> … FROM ROLE on the object(s).
    2. Execute GRANT OWNERSHIP on the object(s).
    3. Use GRANT <privileges> … TO ROLE to re-grant the privileges for the object(s), if appropriate/desired, to the new owning role.

    This is the default (and recommended) process to follow to explicitly control access privileges when transferring ownership.

Examples

Revoke all outbound privileges on the mydb database, currently owned by the manager role, before transferring ownership to the analyst role:

REVOKE ALL PRIVILEGES ON DATABASE mydb FROM ROLE manager;

GRANT OWNERSHIP ON DATABASE mydb TO ROLE analyst;

GRANT ALL PRIVILEGES ON DATABASE mydb TO ROLE analyst;

Note that this example illustrates the default (and recommended) multi-step process for transferring ownership.

In a single step, revoke all privileges on the existing tables in the mydb.public schema and transfer ownership of the tables (along with a copy of their current privileges) to the analyst role:

GRANT OWNERSHIP ON ALL TABLES IN SCHEMA mydb.public TO ROLE analyst COPY CURRENT GRANTS;

Grant ownership on the mydb.public.mytable table to the analyst role along with a copy of all current outbound privileges on the table:

GRANT OWNERSHIP ON TABLE mydb.public.mytable TO ROLE analyst COPY CURRENT GRANTS;