This chapter covers the following DB212 security topics:

Up to DB2 11, only users with installation SYSADM authority can install new a DB2 subsystem or data sharing group or migrate DB2 to the new release. However, a system operator (with installation SYSOPR authority) usually is the person performing the installation or migration steps, which means he or she is granted SYSADM authority. SYSADM authority also includes access to all data. Certain government regulations and policies require that sensitive user data not be exposed to anyone, except the system administrator or data owner. Therefore, to help protect user data and comply with security regulations, DB2 12 provides a way to install or migrate a DB2 subsystem without having SYSADM authority.

With DB2 12 compatibility mode, a user with installation SYSOPR authority has the capability to install and migrate a subsystem with no access to user data. Enhancements are made so that SYSOPR authority can perform work necessary in the process such as these examples:

When executing the migration or installation jobs, system objects in the DB2 catalog might need to be created by the user with installation SYSOPR authority, but these objects will be owned by SYSINSTL. For this to work, the current SQLID must be set to 'SYSINSTL'. The runner with installation SYSOPR now has the authority to use CREATE, ALTER, and DROP on the following objects:

Additionally, SYSINSTL with installation SYSOPR authority now have the ability to CREATE, ALTER, and DROP several objects without any additional privileges such as alias, distinct type, sequence, global variable. SYSINSTL can also grant privileges on the following system objects and resources:

On the installation panel DSNTIPG shown in Figure 10-1, the ROUTINES CREATOR, SEC DEF CREATOR, and INSTALL SQL ID fields must be set to SYSINSTL. The INSTALL PKG OWNER field on this panel also must be set to an authorization ID (PKOWNER in this example) that has been granted system DBADM and DATAACCESS authorities. This is necessary because the BIND and REBIND commands issued during the installation and migration processes require an owner that has authorization to bind and execute all the SQL statements in the package.

Up to DB2 11, the SELECT privilege is the lowest required privilege to execute the UNLOAD utility. Even with the SELECT privilege, if the table has column masks or row permission defined, a user might not be able to retrieve certain rows and columns by using an application that issues the SELECT SQL statement. However, such a user with the SELECT privilege has the ability to read all data in the table using the UNLOAD utility. Furthermore, the SQL SELECT statement, static or dynamic, can be governed by the DB2 resource limit facility (RLF) to control how many rows an application with those SQL statements can retrieve. RLF limits, row permissions, and column masks do not apply to utilities. Executing an UNLOAD utility with only the SELECT privilege can be considered a security vulnerability because a user can unload and have access to large amounts of data beyond the user’s intended authorization. Also, when running the UNLOAD utility, a user with the SELECT privilege can leave a restricted state on the object in case the utility experiences an abend condition and is not terminated.

DB2 12 closes this security exposure by introducing the UNLOAD privilege, which is required when executing the UNLOAD utility. This DB12 UNLOAD privilege provides separation between the SQL SELECT and utility execution, thus the security administrator will have better control to grant the appropriate authorization to intended usage.

The UNLOAD privilege can be established for users starting in DB2 12 compatibility mode (or function level V12R1M100). However, this privilege is mandated for the UNLOAD utility only after new function mode is activated (function level V12R1M500 or greater).

The SELECT privilege can still be used for the UNLOAD utility access in DB2 12 after new function is activated, if AUTH_COMPATIBILITY DSNZPARM is set to the SELECT_FOR_UNLOAD option. The default value for this DSNZPARM is NULL. Prior to activating function level V12R1M500 or greater, the new serviceability trace record IFCID 404 may be enabled to collect the authorization IDs that use the SELECT privilege to execute an UNLOAD utility. This trace record is also retrofitted to DB2 11 so the auditing work can be done before migration to DB2 12 also. The preference is to activate IFCID 404 prior to migrating to DB2 12 with the PTF for APAR PI55706. Actions must be taken for those authorization IDs so the UNLOAD utility will work as intended in the DB2 12 new function mode.

The following SQL statement syntax diagrams show how to grant (Figure 10-2) the UNLOAD privilege to a user ID and revoke (Figure 10-3 on page 171) the privilege.

A new column UNLOADAUTH is added to the SYSIBM.SYSTABAUTH catalog table to record the successful result of the GRANT statement and the authorization ID having the UNLOAD privilege in the GRANTEE column (Example 10-1; the result is shown in Table 10-1).

If RACF is used for security management, then a new profile must be added for the UNLOAD privilege in the RACF class. Example 10-2 shows a couple of RACF profiles defined for the UNLOAD privilege (given to user USER001) and SELECT privilege (given to both users USERT001 and USER002).

If a RACF access control module is written, the module should be updated to handle the new ULOADAUTT constant value (297 or x'129') in the XAPLPRIV field of the RACF parameter list. The RACF access control exit should have logic to handle whether the user or the role associated with the user owns the table:

Another popular security item addressed in DB2 12 is the ability to change an object’s ownership online. Ownership of a database object can be determined by several rules such as whether the DDL statement that defines the object is a static or dynamic SQL, executed under a trusted context with ROLE AS OBJECT OWNER or not, the privilege set for the DDL statement, and so on. The ownership belongs to either an authorization ID or a role. To comply with government and company regulations, the ownership of sensitive data must be transferable from one authorization ID or role to another authorization ID or role. In DB2 11, such a task can be accomplished only with DROP and CREATE DDL statements followed by reloading data in the object as needed. These activities are disruptive in a production system where access to the object is constantly acquired.

To provide the support for changing ownership while keeping the object available, DB2 12 introduces the TRANSFER OWNERSHIP SQL statement with the appropriate authorization. This SQL statement can be either static or dynamic. The TRANSFER OWNERSHIP statement is allowed when the application compatibility is V12R1M500 or greater. The package containing the TRANSFER OWNERSHIP statement can be bound to that application compatibility level after activating new function.

Figure 10-4 shows the syntax diagram for the TRANSFER OWNERSHIP statement.

The CREATOR and CREATORTYPE columns in the following tables are updated with the new owner when the transfer is successful for the objects:

The OWNER and OWNERTYPE columns in the following tables are updated with the new owner when the transfer is successful for the objects:

The example in Figure 10-5 on page 174 shows a table that is created and owned by an authorization ID and later, its ownership is transferred to another authorization ID by the SECADM authority.

The privilege set of the application executing the TRANSFER OWNERSHIP statement must be the owner of the object or SECADM authority. Even with the SEPARATE_SECURITY DSNZPARM set to NO, installed SYSADM and SYSADM authority is not sufficient to perform the ownership transfer. By using this SQL statement, ownership of the following objects can be transferred:

Those objects must not be the system objects (owned by schemas that begin with SYS) and must exist at the current server where the statement is executing. Therefore, three-part name table or view, or alias created with the three-part name should have the location name resolved to the local server. When ownership of a database is transferred, the ownerships of other objects created in the same database such as tablespaces, tables, and views are not transferred. When ownership of a table is transferred, the ownership of the following dependent objects is also transferred:

The new owner can be an authorization ID, a role or the SESSION user (the primary authorization ID executing the application). The new owner's name and type are recorded in either the CREATOR/CREATORTYPE or OWNER/OWNERTYPE columns in the appropriate DB2 catalog table where the object's definition is kept. The new owner is automatically granted the same privileges that the old owner obtained when the object was created. The new owner must have the set of privileges on the base objects, as indicated by the objects dependencies, that are required to maintain the objects existence, unchanged.

For example, TAMMIED is the owner of table T1 and TAMMIED ID also has DBADM authority. After executing the TRANSFER OWNERSHIP OF T1 TO ACACIO statement, ACACIO ID will also have DBADM authority.

The example in Figure 10-6 shows that TAMMIED creates a view with the CREATE VIEW V1 AS SELECT MYUDF(C1) FROM T1 statement. This view is updatable so TAMMIED ID, as the owner, has SELECT, INSERT, UPDATE, DELETE privileges on the T1 based table. TAMMIED should also have EXECUTE privilege on the MYUDF user-defined function. When owner TAMMIED or SECADM executes the TRANSFER OWNERSHIP OF T1 TO MBERNAL statement, DB2 requires that MBERNAL also has SELECT, INSERT, UPDATE, and DELETE privileges on the T1 based table, and EXECUTE privilege on the MYUDF user-defined function.

In DB2 12, the REVOKE PRIVILEGES option on the TRANSFER OWNERSHIP statement must be specified to indicate that the current owner will not have any implicit privilege on the object after the transfer is completed (Example 10-3). The following objects are invalidated:

If any package exists that is dependent on the current owner's privilege on the object, the TRANSFER statement fails. This failure can be avoided by explicitly granting those privileges from another source (such as SECADM) or if the current owner has an administrative authority that allows access (such as DBADM on the database).

The query shown in Figure 10-7 can be used to identify those packages and the privileges associated with a table and schema.

For tables created prior to DB2 9, the query shown in Figure 10-8 can be used.

The grant actions performed by the current owner are not changed when the transfer completes. Those privileges can be revoked separately after by using REVOKE statement with the BY clause.

Also, that functionality does not apply to plan, packages, and security objects created in DB2. Ownership of an application plan and package can be transferred by using the REBIND subcommand with the existing OWNER option. The security objects, such as trusted contexts, roles, row permissions, column masks and their ownerships can be transferred with the DROP and CREATE DDL statements.