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.

function-designator

FL 509 Identifies the function for the ownership transfer. The function must exist at the current server and must not be a cast function that was generated by a CREATE TYPE statement. The function must not be a system-defined routine that is owned by SYSINSTL.

The function can be specified by its name, function signature that uniquely identifies the function, or specific name. The function designator identifies the SQL function using one of these methods. For more information, see Function designators in Db2 for z/OS.

When the ownership of the function is transferred, the OWNER and OWNERTYPE column values for the function in SYSIBM.SYSROUTINES are updated with the authorization ID or role and the type of the new 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.

PROCEDURE procedure-name

FL 509 Identifies the procedure for the ownership transfer. The procedure must exist at the current server and must not be an external SQL procedure. The procedure must not be a system-defined routine that is owned by SYSINSTL.

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

SEQUENCE sequence-name

FL 509 Identifies the sequence for the ownership transfer. The sequence must exist at the current server and must be a user-defined sequence or alias for a user-defined sequence. It must not be a catalog, identity or DB2_GENERATED_DOCID_FOR_XML sequence.

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

When the ownership of the sequence is transferred, the OWNER and OWNERTYPE column values for the sequence in SYSIBM.SYSSEQUENCES 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, 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 or DSNDB06. 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, 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.
  • FL 509 If a sequence is a distinct type and when ownership of the sequence is transferred, the new owner must have the USAGE privilege on that distinct type.
  • FL 509 When the ownership of a stored procedure is transferred, and the stored procedure references any of the following objects, the new owner must have the following corresponding privileges:
    • User-defined type – USAGE privilege on the user-defined type
    • If a JAR name is specified – USAGE on the JAR
    
  • FL 509 When the ownership of a function is transferred and the function references any of the following objects, the new owner must have the corresponding privileges:
    • User-defined type – USAGE privilege on the user-defined type
    • Global variable – READ privilege on the variable
    • Function or based on SOURCE function – EXECUTE on the function
    • Sequence – USAGE privilege on the sequence
    • Table or view – SELECT privilege on the table or view
    • If a JAR name is specified – USAGE on the JAR
    
• FL 509 When the ownership of an SQL PL procedure or function is transferred, ownership of the associated package is not transferred. All versions of a SQL PL procedure or function will be transferred.