IBM Support

IT31396: UTL_FILE BUILT-IN ROUTINES MIGHT BE INVALIDATED WHEN "REVOKE EXECUTE ON MODULE SYSIBMADM.UTL_FILE FROM USER" IS EXECUTED

Subscribe to this APAR

By subscribing, you receive periodic emails alerting you to the status of the APAR, along with a link to the fix after it becomes available. You can track this item individually or track all items by product.

Notify me when this APAR changes.

Notify me when an APAR for this component changes.

 

APAR status

  • Closed as program error.

Error description

  • 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
    <snip>
    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 <database name> -a
    - db2 "CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS()"
    

Local fix

  • 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 <database name> -a
    - db2 "CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS()"
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to Db2 11.1 Mod 4 Fixpack 6 or higher                *
    ****************************************************************
    

Problem conclusion

  • First fixed in Db2 11.1 Mod 4 Fixpack 6
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT31396

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2019-12-30

  • Closed date

    2021-03-12

  • Last modified date

    2021-03-12

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1"}]

Document Information

Modified date:
13 March 2021