GRANT OWNERSHIP¶
Transfers ownership of an object (or all objects of a specified type in a schema) from one role to another role. Role refers to either a role or a database 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 Overview of Access Control.
This command is a variation of GRANT <privileges>.
- See also:
Syntax¶
-- Role
GRANT OWNERSHIP
{ ON { <object_type> <object_name> | ALL <object_type_plural> IN { DATABASE <db_name> | SCHEMA <schema_name> } }
| ON FUTURE <object_type_plural> IN { DATABASE <db_name> | SCHEMA <schema_name> }
}
TO ROLE <role_name>
[ { REVOKE | COPY } CURRENT GRANTS ]
-- Database role
GRANT OWNERSHIP
{ ON { <object_type> <object_name> | ALL <object_type_plural> IN { DATABASE <db_name> | SCHEMA <schema_name> } }
| ON FUTURE <object_type_plural> IN { DATABASE <db_name> | SCHEMA <schema_name> }
}
TO DATABASE ROLE <database_role_name>
[ { REVOKE | COPY } CURRENT GRANTS ]
Where:
objectType ::= { ALERT | DATABASE | FAILOVER GROUP | FILE FORMAT | FUNCTION | INTEGRATION | MASKING POLICY | NETWORK POLICY | PASSWORD POLICY | PIPE | PROCEDURE | REPLICATION GROUP | ROW ACCESS POLICY | ROLE | SCHEMA | SECRET | SEQUENCE | SESSION POLICY | STAGE | STREAM | TABLE | TAG | TASK | USER | VIEW | WAREHOUSE }
Required Parameters¶
object_name
Specifies the identifier for the object on which you are transferring ownership.
object_type
Specifies the type of object (for schema objects):
ALERT | EVENT TABLE | EXTERNAL TABLE | FILE FORMAT | FUNCTION | MASKING POLICY | MATERIALIZED VIEW | PASSWORD POLICY | PIPE | PROCEDURE | ROW ACCESS POLICY | SECRET | SESSION POLICY | SEQUENCE | STAGE | STREAM | TABLE | TASK | VIEW
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 (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> 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¶
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 (i.e. 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 theREVOKE CURRENT GRANTS
option).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
The scheduled task (i.e. the standalone task, or the root task in a tree) must be suspended. Note that all tasks in the container are suspended automatically if all tasks in a specified database or schema are transferred to another role.
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 separateGRANT OWNERSHIP ON MATERIALIZED VIEW
statement.Database roles:
Ownership can only be transferred on objects in the same database as the database role.
Examples¶
Roles¶
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;
Database Roles¶
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 mydb.dr1
database role:
GRANT OWNERSHIP ON ALL TABLES IN SCHEMA mydb.public TO DATABASE ROLE mydb.dr1 COPY CURRENT GRANTS;
Grant ownership on the mydb.public.mytable
table to the mydb.dr1
database role along with a copy of all current outbound
privileges on the table:
GRANT OWNERSHIP ON TABLE mydb.public.mytable TO ROLE mydb.dr1 COPY CURRENT GRANTS;