Troubleshooting
Problem
A user with SECADM or ACCESSCTRL authority receives an error when granting the following privileges
- EXECUTE privilege on SYSIBMADM.UTL_DIR module
- CREATE_EXTERNAL_ROUTINE privilege on the database
- CREATE_NOT_FENCED_ROUTINE privilege on the database
db2 => grant create_external_routine on database to user dba
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0552N "BOSS" does not have the privilege to perform operation "GRANT".
SQLSTATE=42502
db2 => grant execute on module sysibmadm.utl_dir to user dba
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "BOSS". Operation: "GRANT". Object: "SYSIBMADM.UTL_DIR". SQLSTATE=42501
Cause
The change in behavior and resultant errors were intentionally made to resolve a security issue that was reported to IBM. A security bulletin for the issue contains instructions on how to obtain the fixes.
CVE: CVE-2021-29678
11.5 Fix: APAR IT37939 in 11.5.7.0
Other releases: Fixed in cumulative security special builds, details can be found in the security bulletin
A user who can create directories by using UTL_DIR in one database, can create a directory to read files from another database or other locations owned by the instance owner. As the directories are considered instance level resources, the authorization model is being changed so that an instance level authority is required to grant the EXECUTE privilege on the UTL_DIR module. A user with SYSADM authority can grant the EXECUTE privilege only to those users trusted to create directories applicable only to their database, or can create the directories directly themselves. To this end SYSADM is given implicit EXECUTE privilege on the UTL_DIR module.
Similarly, the privilege to create not fenced and external routines allows the user to create routines that run C code. This code has arbitrary access to the fenced mode process, and when run not-fenced, to the database server itself. These privileges are instance level capabilities, and the authorization model is being changed so that an instance authority is required to grant the privilege to create these routines. Specifically, SYSADM authority is required to grant CREATE_EXTERNAL_ROUTINE and CREATE_NOT_FENCED_ROUTINE privileges on the database, instead of SECADM or ACCESSCTRL.
Resolving The Problem
There are two primary methods to address the change in behavior:
- Issue the grants by a user with SYSADM authority
- Revert the change with the DB2_ALTERNATE_AUTHZ_BEHAVIOUR registry variable
The ability to issue the grant that was removed from SECADM or ACCESSCTRL authority was added to SYSADM authority. Issue the necessary grants as a user with SYSADM authority.
There are scenarios where this change to behavior is unacceptable as changes to the user issuing the grant cannot be made. There are also scenarios where this change is unnecessary as limited separation of duties is practiced and the same users typically hold both SYSADM and SECADM authorities. In these scenarios, the registry variable DB2_ALTERNATE_AUTHZ_BEHAVIOUR can be used to revert specific behavior.
Valid values for this registry variable are:
- UTL_DIR_DBAUTH
When this value is set, the GRANT statement of EXECUTE privilege on the module UTL_DIR allows SECADM or ACCESSCTRL in addition to SYSADM.
- EXTERNAL_ROUTINE_DBAUTH
When this value is set, the GRANT statement of CREATE_EXTERNAL_ROUTINE on the database allows SECADM or ACCESSCTRL in addition to SYSADM.
- NOT_FENCED_ROUTINE_DBAUTH
When this value is set, the GRANT statement of CREATE_NOT_FENCED_ROUTINE on the database allows SECADM or ACCESSCTRL in addition to SYSADM.
Multiple values can be specified with a comma between each value. For example, the following can be set to revert all changes:
db2set DB2_ALTERNATE_AUTHZ_BEHAVIOUR=UTL_DIR_DBAUTH,EXTERNAL_ROUTINE_DBAUTH,NOT_FENCED_ROUTINE_DBAUTH
In addition to changing who issues the grant, existing grants and directories should be examined to ensure they are correct.
The following SQL shows who has the CREATE_EXTERNAL_ROUTINE privilege:
db2 "select grantee, granteetype from syscat.dbauth where EXTERNALROUTINEAUTH = 'Y'"
The following SQL shows who has the CREATE_NOT_FENCED_ROUTINE privilege:
db2 "select grantee, granteetype from syscat.dbauth where NOFENCEAUTH = 'Y'"
The following SQL shows who has EXECUTE privilege on the UTL_DIR module:
db2 "select grantee, granteetype from syscat.moduleauth where moduleschema = 'SYSIBMADM' and modulename='UTL_DIR'"
The following SQL shows what directories were created. The table exists only if directories have been created.
db2 "select * from systools.directories"
Related Information
Document Location
Worldwide
Was this topic helpful?
Document Information
Modified date:
08 December 2021
UID
ibm16523848