TRANSFER OWNERSHIP statement

Invocation for TRANSFER OWNERSHIP

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES RUN behavior is in effect. For more information, see Authorization IDs and dynamic SQL.

Authorization for TRANSFER OWNERSHIP

The privilege set must include at least one of the following privileges:

• Ownership of the object
• SECADM authority

Explicit SECADM authority is always required regardless of the SEPARATE_SECURITY subsystem parameter setting. For more information, see Separating the SYSADM authority. If the Access Control Authorization exit is active, Db2 calls the exit to check for the SECADM authority. Only the SECADM authority is checked regardless of the SEPARATE_SECURITY system parameter value.

Description for TRANSFER OWNERSHIP

DATABASE database-name

Identifies the database for ownership transfer. The database must exist on the current server. The name of the database must not identify DSNDB01, DSNDB04, DSNDB06, or any implicitly created database owned by SYSIBM. When the ownership of the database is transferred, the CREATOR and CREATORTYPE values for the database in SYSIBM.SYSDATABASE are updated with the authorization ID or role and the type of the new owner. Transferring the ownership of a database does not transfer the ownership of any other object in the database that was created by the current owner.

INDEX index-name

Identifies the index for ownership transfer. The index must exist on the current server. The index must not be defined on a catalog table, a directory table or a declared temporary table. When the ownership of the index is transferred, the OWNER and OWNERTYPE values for the index in SYSIBM.SYSINDEXES are updated with the authorization ID or role and the type of the new owner.

STOGROUP stogroup-name

Identifies the storage group for ownership transfer. The storage group must exist the current server. When the ownership of the storage group is transferred, the CREATOR and CREATORTYPE values for the group in SYSIBM.SYSSTOGROUP are updated with the authorization ID or role and the type of the new owner.

TABLE table-name

Identifies the table for ownership transfer. The table must exist on the current server, and it must not be a view, a catalog table, a directory table, a table with the SECURITY LABEL column, or a table that is implicitly created for an XML column. If the table is referenced by a qualified name, the name can be a two-part or three-part name. If a three-part name is used, the first part must match the value of the field Db2 LOCATION NAME on installation panel DSNTIPR at the current server. If the current server is not the local Db2, this name is not necessarily the name in the CURRENT SERVER special register.

If table-name is an alias for a table and alias-name is specified, ownership of the table is transferred to the new owner and the alias is unchanged.

When the ownership of a table is transferred, the ownership of any implicitly created object, such as a table space, auxiliary table, auxiliary table space, XML table, XML table space, or index, is also transferred if the table and the implicitly created object have the same owner. In addition, the ownership of an explicitly created auxiliary table or auxiliary table space is also transferred if the table and the explicitly created object have the same owner.

When the ownership of the table is transferred, the OWNER and OWNERTYPE values for the table in SYSIBM.SYSTABLES are updated with the authorization ID or role and the type of the new owner.

TABLESPACE tablespace-name

Identifies the table space for ownership transfer. The table space must exist on the current server, but it must not have been implicitly created for an XML column. The database must not be DSNDB01, DSNDB06, or DSNDB04. Omitting a database name implicitly specifies DSNDB04. When the ownership of the table space is transferred, the CREATOR and CREATORTYPE values for the table space in SYSIBM.SYSTABLESPACE are updated with the authorization ID or role and the type of the new owner.

VIEW view-name

Identifies the view for ownership transfer. The view must exist on the current server. If the view is referenced by a qualified name, the name can be a two-part or three-part name. If a three-part name is used, the first part must match the value of the field Db2 LOCATION NAME on installation panel DSNTIPR at the current server. If the current server is not the local Db2, this name is not necessarily the name in the CURRENT SERVER special register.

If view-name is an alias for a view and alias-name is specified, ownership of the view is transferred to the new owner and the alias is unchanged.

When the ownership of the view is transferred, the OWNER and OWNERTYPE values for the view in SYSIBM.SYSTABLES and SYSIBM.SYSVIEWS are updated with the authorization ID or role and the type of the new owner.

TO

Specifies the authorization ID or the role to which the ownership of an object is transferred.

ROLE role-name

Specifies the role to which the ownership of an object is transferred. The new role must exist on the current server.

USER authorization-name

Specifies the authorization ID to which the ownership of the object is transferred.

SESSION_USER

Specifies that the value of the SESSION_USER special register is used as the authorization ID to which the ownership of an object is transferred.

REVOKE PRIVILEGES

Specifies that the current owner no longer has any implicit privileges on the object after the transfer is complete. The corresponding authorization cache entries for the current owner are cleared.

If any packages or objects are dependent on an implicit privilege that the current owner has on the object of the statement, a TRANSFER OWNERSHIP statement fails because existing privileges are revoked. For the current owner to maintain access to those dependent packages, authorization must be explicitly granted to the current owner from another source before the TRANSFER OWNERSHIP statement is executed. For example, assume that a user created a package that references one of their tables. The user implicitly has the SELECT privilege on tables they created. To transfer that table to another owner and still be able to use the package, the user must be explicitly granted the SELECT privilege on the table before the TRANSFER OWNERSHIP statement is executed.

Notes for TRANSFER OWNERSHIP

• The TRANSFER OWNERSHIP statement does not change the schema of the transferred object.
• The ownership of any system object whose OWNER is SYSIBM or whose schema begins with SYS cannot be transferred.
• The new owner is automatically granted the same privileges that the current owner holds on the object at the time of the object's creation. For example, if the current owner has the DBADM authority on the database when the database was created, the new owner is automatically granted the DBADM authority on the database.
• An authorization ID with the SECADM authority cannot transfer the ownership of an object to itself.
• Db2 issues a warning when the current owner of an object attempts to transfer the ownership to itself.
• If the current owner of an object is a role, the dependency record for that role in SYSIBM.SYSOBJROLEDEP is deleted when the object ownership is transferred. If the new owner is a role, the dependency record for that role is added to SYSIBM.SYSOBJROLEDEP.
• A SECADM or ACCESSCTRL authority can use the REVOKE statement with the BY clause to revoke any privilege that is granted on the object by the current owner.
• When the ownership of an object is transferred, the new owner must have the same set of privileges on the object as specified in the dependency record of the object. The privileges are required for the object to continue to exist. The new owner does not need additional privileges that are required for creating the object. Consider the following examples.
  • If a view has SELECT and INSERT dependencies on an underlying table and when the ownership is transferred, the new owner of the view must be granted the same SELECT and INSERT privileges. If the dependencies are SELECT WITH GRANT OPTION and INSERT WITH GRANT OPTION, the new owner must be granted the same SELECT WITH GRANT OPTION and INSERT WITH GRANT OPTION privileges.
  • If a view has a dependency on a routine and when the ownership of the view is transferred, the new owner must be granted the EXECUTE privilege on the dependent routine.
  • If a table has a distinct type column and when the ownership of the table is transferred, the new owner must be granted the USAGE privilege on the distinct type.