Start of change

TRANSFER OWNERSHIP

The TRANSFER OWNERSHIP statement transfers ownership of a database object.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

To transfer ownership for a table, view, or index, the authorization ID of the statement must be the owner of the object and the privileges held must include at least one of the following:

  • The following system authorities:
    • The system authorities of *OBJOPR and *OBJEXIST on the object to be transferred
    • The system authority *EXECUTE on the library that contains the object to be transferred
  • Start of changeDatabase administrator authorityEnd of change
  • Security administrator authority

For information about the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Table or View.

Syntax

Read syntax diagramSkip visual syntax diagramTRANSFER OWNERSHIP OFobjectTO new-owner REVOKE PRIVILEGESPRESERVE PRIVILEGES
object
Read syntax diagramSkip visual syntax diagramINDEXindex-nameTABLEtable-nameVIEWview-name
new-owner
Read syntax diagramSkip visual syntax diagramUSERauthorization-nameCURRENT USERCURRENT_USERSESSION_USERUSERSYSTEM_USER

Description

INDEX index-name
Identifies the index that is to have its ownership transferred. The index-name must identify an index that exists at the current server.
TABLE table-name
Identifies the table that is to have its ownership transferred. The table-name must identify a base table that exists at the current server, but must not identify a declared global temporary table or a catalog table.
VIEW view-name
Identifies the view that is to have its ownership transferred. The view-name must identify a view that exists at the current server, but must not identify a catalog view.
USER authorization-name
Specifies the authorization ID to which ownership of the object is being transferred.
CURRENT USER or CURRENT_USER
Specifies that the value of the CURRENT USER special register is to be used as the authorization ID to which ownership of the object is being transferred.
SESSION_USER or USER
Specifies that the value of the SESSION_USER special register is to be used as the authorization ID to which ownership of the object is being transferred.
SYSTEM_USER
Specifies that the value of the SYSTEM_USER special register is to be used as the authorization ID to which ownership of the object is being transferred.
REVOKE PRIVILEGES or PRESERVE PRIVILEGES
Specifies the privileges of the current owner of the object after the ownership is transferred.
REVOKE PRIVILEGES
Specifies that the current owner will not have any explicit privileges to the object after the transfer is complete.
PRESERVE PRIVILEGES
Specifies that the current owner of an object that is to have its ownership transferred will continue to hold any existing privileges on the object after the transfer. For example, any privileges that were granted to the creator of a view continue to be held by the original owner even after ownership has been transferred to another user.

Rules

Ownership of most system-defined objects cannot be transferred.

Ownership of objects in a schema whose name starts with 'SYS' or 'Q' cannot be transferred.

Start of changeAn authorization ID that has security administrator authority cannot transfer ownership of an object to itself, if it is not already the owner of the object.End of change

Notes

  • All privileges that the current owner has on the object are transferred to the new owner.
  • When the ownership of a database object is transferred, the new owner will not necessarily have privileges for the object's dependencies.
  • If an attempt is made to transfer ownership of an object to its owner, a warning is returned.

Examples

Example 1: Transfer ownership of table T1 to PAUL. 

TRANSFER OWNERSHIP OF TABLE WALID.T1
     TO USER PAUL PRESERVE PRIVILEGES
Paul becomes the owner of table WALID.T1 and is granted all the privileges that the previous owner of the table had. The prior owner retains all privileges as well.

Example 2: Transfer ownership of view V1 to HENRY and remove privileges from the previous owner

   TRANSFER OWNERSHIP OF VIEW V1
     TO USER HENRY
Henry becomes the owner of view V1 and is granted all the privileges that the previous owner of the view had. The prior owner no longer has any explicit privileges on the view.
End of change