This Db2 for z/OS News from the Lab blog entry was originally published on 2019-07-12. It is part of the Db2 12 GA feature series, which highlights new features and capabilities introduced by the Db2 12 for z/OS, at general availability (GA) in October 2016.

By Gayathiri Chandran.

There is a demand for a way to change the ownership of Db2 objects from one user to another. Since the owner of an object implicitly gets all privileges on the object, and those privileges cannot be explicitly revoked, there are times when an object’s owner must be changed. Although dropping the object would remove the implicit privileges so that you could recreate the object for a new owner, dropping and recreating an object under a new owner is not always possible.

In Db2 12, you can transfer the ownership of database and system objects online if you are the owner of the object or have SECADM authority. You can use an SQL TRANSFER OWNERSHIP statement to transfer ownership of an object to an authorization ID or a role without any application change. You cannot transfer ownership for catalog and directory objects.

Db2 objects that can be transferred:

• Database
• Table space
• Table / Materialized Query Table (MQT)
• Index
• View
• Stogroup

After a TRANSFER OWNERSHIP statement is processed, the current owner will no longer have any implicit privileges on the object and any cache dependencies are cleared.

Indexes, tables, materialized query tables and views may depend on other objects. When changing an object’s ownership, the new owner is required to have the necessary dependent privileges that the current owner needed to create the object. For example, to transfer ownership of a view, the new owner is required to have a minimum of the privileges that were required to define the view.

Example of a Transfer Ownership for a table:

The following example highlights the OWNER value in SYSIBM.SYSTABLES record and GRANTOR and GRANTEE values in SYSIBM.SYSTABAUTH implicit ownership record before and after the ownership transfer.

Transferring ownership of a table includes transferring the ownership of objects that are dependent on the table such as indexes, explicitly created LOB objects (when the owner is the same), XML objects, implicitly created LOB objects and the associated base table space. Hence, no additional steps are needed when transferring ownership of table objects. However, transferring the ownership of the database does not include objects in the database such as table space or tables.

When you issue a REVOKE statement, you can specify the NOT INCLUDING DEPENDENT PRIVILEGES clause so the privileges previously granted by the user are not revoked. However, if there are views or materialized query tables dependent on the privilege or authority being revoked, those views and materialized query tables will be dropped automatically as part of the revoke statement. Alternatively, to preserve the dependent objects, you can transfer the ownership of the dependent views and materialized query tables to a different ID or a role before issuing the REVOKE statement.

Transferring ownership can help you to follow compliance regulations, maintain better security, and control objects with sensitive data. For more information about authorizations and the SQL syntax, see the TRANSFER OWNERSHIP documentation.

Gayathiri Chandran is a Db2 for z/OS developer.