Bookmark and Share
RSS

Recent Posts

A Welcome Alternative to Object Ownership Change

May 02, 2017

Most DBAs dread changing the ownership of an object. You have to take the database offline, sometimes for an extensive period, to unload, drop, recreate and reload the data, run runstats and-- finally--rebind all the application packages. I’m sure many of you have at least a horror story or two about this experience.  

Fortunately, DB2 12 features a far-simpler alternative: Ownership of objects can now be transferred using the TRANSFER OWNERSHIP SQL statement. With this capability, there's no longer a need to perform the time-consuming task of dropping and recreating objects.

Here's the syntax:

    TRANSFER OWNERSHIP OF object TO new-owner REVOKE PRIVLEDGES

This capability can be applied to various objects: DATABASE, INDEX, STOGROUP, TABLE, TABLESPACE, and VIEW, while the new-owner can be either a ROLE, USER or SESSION_USER:
  • ROLE specifies the role to which ownership of an object is transferred. The new role must exists on the current server.
  • USER is an authorization-id to which the ownership of the object is transferred.
  • SESSION_USER is a special register which contains a value that will be used as the authorization-id to which ownership of an object is transferred.
If any packages are dependent on an implicit privilege that the current owner has on the object, TRANSFER OWNERSHIP with REVOKE PRIVILEGES fails. For the current owner to maintain access to those dependent packages, authorization must be explicitly granted to the current owner from another source before TRANSFER OWNERSHIP is executed. For example, if a package requires that the current owner has the SELECT privilege on a table, the current owner must be explicitly granted the required SELECT privilege. The failed transfer will return SQLCODE -20342:

-20342   AUTHORIZATION ID auth-ID DOES NOT HAVE THE REQUIRED PRIVILEGE privilege-name ON OBJECT object-name OF TYPE type-name FOR OWNERSHIP TRANSFER.

You can avoid this by using an auth-id that has SECADM privilege assigned. As a test, I successfully issued the TRANSFER statement on a table with a package dependency using a primary auth-id with SECADM privilege. The transfer executed without any issues or any change to the package dependent on the tables.  Using an auth-id with SECADM will avoid having to issue explicit privileges on any dependent object to the auth-id performing the transfer.

If you're on DB2 12 and have used this command, please share your impressions in comments.

Posted May 02, 2017 | Permalink

comments powered by Disqus