With function level 509 in Db2 13 for z/OS, security administrators can satisfy their business security mandates without causing application outages by using new SQL syntax for application objects in the TRANSFER OWNERSHIP statement. The new SQL syntax keywords support the online transfer of stored procedures, functions, and sequences, and the new keywords are available when the application issuing the TRANSFER OWNERSHIP statement runs at APPLCOMPAT level V13R1M509 or higher.
Before this enhancement, changing the ownership of these objects often required disruptions including invalidation of packages that use the object, and sometimes cascaded drops of dependent objects, frequently leading to application outages. The new capability enables seamless ownership transfers while maintaining application availability.
Examples of transferring application objects
For example, a security administrator can transfer ownership of sequence INVOICE.ORDER_SEQ01 to application owner PAOWNER by issuing the following statement:
TRANSFER OWNERSHIP OF SEQUENCE INVOICE.ORDER_SEQ01
TO USER PAOWNER
REVOKE PRIVILEGES;
Similarly, a security administrator can transfer ownership of an SQL PL procedure named EMPLOYEE.UPDATE_SALARY to the SP_UPDS_OWNER role:
TRANSFER OWNERSHIP OF PROCEDURE EMPLOYEE.UPDATE_SALARY
TO ROLE SP_UPDS_OWNER
REVOKE PRIVILEGES;
Rules for transferring application objects
Ownership transfer is straightforward, but there are important rules to follow. When ownership is transferred, the new owner must already hold all privileges required by the object's dependencies. For example, if a stored procedure references a user-defined type, the new owner must have USAGE on that type. If a function references a table, the new owner must have SELECT privilege on that table. If a sequence uses a distinct type, the new owner must have USAGE on that type. If required privileges are missing, the TRANSFER OWNERSHIP statement fails.
When ownership changes, the previous owner's implicit privileges are revoked, and if dependent packages rely on those implicit privileges, the transfer fails unless explicit privileges are granted first. Security administrators should grant required privileges from another source before running the transfer.
For SQL PL routines, ownership of the procedure or function is transferred, but ownership of the associated package is not transferred. If needed, package ownership can be changed separately using an ALTER statement.
Transfer ownership isn't allowed for certain objects, and attempting to transfer ownership for these objects results in SQLCODE -607 or -20142. These restrictions apply to the following objects:
· External SQL procedures.
· System-defined routines owned by SYSINSTL.
· Cast functions generated by CREATE TYPE statements.
· System-generated sequences such as identity or XML DOCID sequences.
A practical transfer scenario
Consider a security administrator transferring ownership of an SQL PL procedure to a role. The administrator would first create the role SP_UPDS_OWNER, then identify dependencies for the procedure. Next, they would grant required privileges, such as USAGE on referenced UDTs, and finally issue the TRANSFER OWNERSHIP statement with REVOKE PRIVILEGES. The result is a clean ownership transition with no application outage.
Conclusion
This enhancement removes a long-standing tradeoff between security and availability. You can now align ownership with enterprise security policies, move ownership from individuals to roles, and meet audit requirements without disrupting production workloads. For security teams and Db2 administrators, function level 509 delivers a safer, more practical way to manage application object ownership.
For more information, see the following topics: