DB2 FOR LUW / DB2FORLUW / A10 - DB2

UTL_FILE built-in routines might be invalidated when "REVOKE
EXECUTE ON MODULE SYSIBMADM.UTL_FILE FROM USER" is executed as
in the following ( assuming the user name is "db2inst1" ) :

1) grant necessary authority/privilege to a user : db2inst1
including "grant execute on module sysibmadm.utl_file to user
db2inst1"

db2 "GRANT CONNECT ON DATABASE TO USER db2inst1"
db2 "GRANT BINDADD ON DATABASE TO USER db2inst1"
db2 "GRANT CREATETAB ON DATABASE TO USER db2inst1"
db2 "GRANT CREATE_EXTERNAL_ROUTINE ON DATABASE TO USER db2inst1"
db2 "GRANT LOAD ON DATABASE TO USER db2inst1"
db2 "GRANT USE OF TABLESPACE SYSTOOLSPACE TO USER db2inst1"
db2 "GRANT EXECUTE ON MODULE SYSIBMADM.UTL_FILE TO USER
db2inst1"
db2 "GRANT EXECUTE ON MODULE SYSIBMADM.UTL_DIR TO USER db2inst1"

2) create a pl/sql procedure calling a built-in routines in
UTL_FILE module by the user db2inst1 without specifying explicit
procedure schema name

3) revoke execute on module sysibmadm.utl_file from user
db2inst1

Then UTL_FILE built-in routines might get invalidated :

db2 "select substr(objectschema,1,18) as objectschema,
substr(objectmodulename,1,18) as objectmodulename,
substr(objectname,1,24) as objectname, substr(routinename,1,18)
as routinename, objecttype from syscat.invalidobjects"

OBJECTSCHEMA       OBJECTMODULENAME   OBJECTNAME
ROUTINENAME        OBJECTTYPE
------------------ ------------------ ------------------------
------------------ ----------
DB2INST1           TEST_PACKAGE       SQL191003120531150
TEST_PROC          F
SYSIBMADM          UTL_FILE           UTL_FILE_FCLOSE
FCLOSE             F
SYSIBMADM          UTL_FILE           UTL_FILE_FCOPY
FCOPY              F
SYSIBMADM          UTL_FILE           UTL_FILE_FCOPY_2
FCOPY              F
SYSIBMADM          UTL_FILE           UTL_FILE_FCOPY_3
FCOPY              F
SYSIBMADM          UTL_FILE           UTL_FILE_FFLUSH
FFLUSH             F

SYSIBMADM          UTL_FILE           UTL_FILE_PUTF_5
PUTF               F
SYSIBMADM          UTL_FILE           UTL_FILE_PUTF_6
PUTF               F
SYSIBMADM          UTL_FILE           UTL_FILE_PUT_LINE
PUT_LINE           F

  30 record(s) selected.


note:
a) If pl/sql procedure calls UTL_DIR built-in procedure and
"REVOKE EXECUTE ON MODULE SYSIBMADM.UTL_DIR FROM USER" is
executed, UTL_DIR built-in procedures might be invalidated.

b) Please run the following statements/commands in order to
revalidate the built-in procedures which are invalidated:
- db2 "CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS()"
  (Return Status = 0 and SQL0361W will be returned)
- db2updv111 -d  -a
- db2 "CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS()"

****************************************************************
* USERS AFFECTED:                                              *
* Db2                                                          *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* See Error Description                                        *
****************************************************************
* RECOMMENDATION:                                              *
* Upgrade to Db2 11.1 FP6 or later.                            *
****************************************************************

Please run the following statements/commands in order to
revalidate the built-in procedures which are invalidated:
- db2 "CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS()"
  (Return Status = 0 and SQL0361W will be returned)
- db2updv111 -d  -a
- db2 "CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS()"

****************************************************************
* USERS AFFECTED:                                              *
* Db2                                                          *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* See Error Description                                        *
****************************************************************
* RECOMMENDATION:                                              *
* Upgrade to Db2 11.1 FP6 or later.                            *
****************************************************************

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

15.12.2019
30.12.2019
30.12.2019