Required authorization for installation or migration

You are not required to use the SYSADM or installation SYSADM authority to install or migrate a Db2 subsystem. Instead, you can use the installation SYSOPR authority to perform all installation or migration tasks while preventing unintended access to user data.

In Db2 11 and earlier releases, you needed the SYSADM authority to perform Db2 installation or migration tasks. In addition, you were required to have the installation SYSADM authority to run the DSNTIJTC job and the CATMAINT utility. However, the SYSADM and installation SYSADM authorities provide unrestricted access to all user objects in the subsystem, which in some cases was unintended and introduced unnecessary security risks.

Using the installation SYSOPR authority can help reduce those risks. The installation SYSOPR authority provides all the privileges that are required for installing or migrating Db2. For example, with installation SYSOPR, you can access catalog and directory objects, create, drop, or alter system objects, execute the -ACTIVATE command, specify the owner for the BIND and REBIND commands, and run the CATMAINT utility. However, you are not allowed any access to non-system objects or user data in the subsystem.

The overall process of installing or migrating Db2 with the installation SYSOPR authority is the same as that with the SYSADM authority. You can use the installation SYSOPR authority to perform all installation or migration tasks if you meet the following additional requirements:

  • The primary or secondary authorization ID that performs installation or migration tasks must have the installation SYSOPR authority.
  • The current SQLID that processes data definition statements must be set to SYSINSTL which owns all the system objects created by the installation SYSOPR authority.
  • The OWNER option for the BIND and REBIND commands must be set to an ID that is authorized to bind and execute SQL statements in a package. To obtain the required authorization for migration to Db2 13, consider granting system DBADM authority with DATAACCESS to an ID that is authorized to bind and execute SQL statements. Revoke DBADM authority with DATAACCESS from that ID after migration is complete.

    For installing Db2 13, specify an installation system administrator ID.

  • The DSNTIAIN plan must be used with the DSNTIAD program to process all dynamic data definition statements for Db2 installation or migration. The DSNTIAIN plan is specifically provided for dynamically executing SQL statements when the primary or secondary authorization ID is granted the installation SYSOPR authority and the current SQLID is set to SYSINSTL. The DSNTIAIN plan is bound by job DSNTIJTM when it is configured by the CLIST for installation or migration.
  • An explicit qualifier must not be specified for the CREATE TABLE or CREATE INDEX statement. The qualifier can be implicitly specified through a leading SET CURRENT SCHEMA statement so that SYSINSTL can own the table or index.

You can configure installation or migration jobs to use the installation SYSOPR authority by running the CLIST and specifying SYSINSTL for the ROUTINES CREATOR, SEC DEF CREATOR, and INSTALL SQL ID fields on panel DSNTIPG. When INSTALL SQL ID is set to SYSINSTL, the authorization ID with the installation SYSOPR authority can create, alter, or drop the following objects:

  • Auxiliary table
  • Created global temporary table
  • Database
  • Function
  • Index
  • Procedure
  • Procedure and function in schema, SYSTOOLS and SYSFUN
  • Storage group
  • Table
  • Table space
  • Trigger

You must explicitly specify the owner of a procedure or function package. If a stored procedures is created with the SECURITY DEFINER clause, you must define SYSINSTL in RACF®. When the current SQLID is set to SYSINSTL, the authorization ID with the installation SYSOPR authority can grant certain privileges on some system objects and resources, as follows:

  • All database, table space, and table privileges on objects in database DSNDB04 and system databases DSNRGFDB, DSNRLST, DSNOPTDB, DSNMDCDB, DSNADMDB, DSNATPDB, DSN5JSDB, DSNMQDB, SYSIBMTA, SYSIBMTS, and DSNXSR
  • The USE privilege on buffer pools and storage groups
  • All privileges on plans that begin with 'DSN'
  • All privileges on packages where the collection-ID and package-name begin with 'DSN'
  • The EXECUTE privilege on system-defined routines.

In addition, the same authorization ID can create, alter, and drop the following objects without any additional privileges:

  • Alias
  • Distinct type
  • Distinct type in schema, SYSTOOLS and SYSFUN
  • Sequence
  • Variable.

With additional privileges, the authorization ID can create and alter the following objects:

  • A function that has any of the following characteristics: it uses a table as a parameter, it is sourced on another function, or it is defined with LANGUAGE JAVA and the EXTERNAL NAME clause specifies the name of a JAR file.
  • An index that uses an expression.
  • A procedure that is defined with LANGUAGE is JAVA and the EXTERNAL NAME clause specifies the name of a JAR file.
  • A trigger when additional privileges are granted for executing SQL in the trigger definition.
  • A view when the SELECT privilege on the base table or view is granted.