News and Tips on Db2 v12 migration : Episode 7

10 Februar 2020

SQL statement : TRANSFER OWNERSHIP

From DB2 v12, you can transfer the ownership of DB2 objects. An object CREATOR or qualifier is synonymous to schema. The OWNER is the current VALUE of the CURRENT SQLID special register.  The OWNER is authorized to maintain and grant privileges on the given object.

Specifications:

  • New owner: may be a userid or a role
  • Target objects:
    • Stogroup
    • Database
    • Tablespace
    • Table
    • Index
    • View
  • Security: can only be done by a SECADM or by the owner himself

TRANSFER OWNERSHIP of TABLE - Impacts

A TRANSFER OWNERSHIP at TABLE level also impacts:
  • Implicitly created TS
  • XML objects
  • Implicitly and explicitly (same owner) created LOB objects
  • Indexes (if the owners are equals)

Example : we have created a table TEST.CUSTOMER like DSN81210.CUSTOMER with a unique index on this table.  This table also contains XML columns.

Table Definition Before TRANSFER
 
Here are the existing privileges on this table :

Table Authorizations Before TRANSFER
 
Then we transfer the ownership to DBATEAM via the statement :
TRANSFER OWNERSHIP OF TABLE TEST.CUSTOMER TO USER DBATEAM REVOKE PRIVILEGES ;

See how OWNER has been changed on the table and all its dependent objects :

Table Definitions After TRANSFER

Table Authorizations After TRANSFER
 

TRANSFER OWNERSHIP of DATABASE  - Impacts

A TRANSFER OWNERSHIP at DATABASE level doesn’t propagate to dependent objects; you have to manage the transfer yourself. 
Content of SYSDBAUTH (before TRANSFER) :
 
Database Authorizations BeforeTRANSFER
 
TRANSFER OWNERSHIP OF DATABASE DSN8D12A TO USER ALAINPA REVOKE PRIVILEGES ;
Content of SYSDBAUTH (after TRANSFER) :
 
Database Authorizations After TRANSFER


TRANSFER OWNERSHIP - Other impacts

Also pay attention to the fact that a package may be invalidated after a TRANSFER OWNERSHIP on an object. In this case, an additional action is required to use the new owner: you will have to REBIND  packages with the OWNER option.

Summary

Transferring of ownership can be useful either when some user no longer work in your company, or to standardize your configuration.  It works at object level, not always propagates to dependent objects and may have impacts on packages.
 
If you are interested, we can send you queries to simplify your management of transferring ownership:
  • To generate all TRANSFER statement for a DATABASE including all dependent objects
  • To generate all TRANSFER statement for a USER (remove this user from the catalog and transfer to a role or another user)
  • To discover all the impacted packages
Send us an email to info@dbizners.com, we will be happy to give you feedback …
 
Next episode : SQL statement CONCENTRATE