Regrant EXECUTE Privilege After Upgrading a Database

When you upgrade a database, there is a set of privileged system procedures for which you must regrant the EXECUTE privilege if the upgraded database continues to use the SYSTEM PROCEDURE DEFINER security model.

The system procedures for which you must regrant the privilege are:

sa_audit_string
sa_checkpoint_execute
sa_disable_auditing_type
sa_disk_free_space
sa_enable_auditing_type
sa_external_library_unload
sa_flush_cache
sa_list_external_library
sa_server_option
sa_procedure_profile
sa_procedure_profile_summary
sa_table_page_usage
sa_validate

To determine the security model of your upgraded database, execute:

select IF ((HEXTOINT(substring(db_property('Capabilities'),1,length(db_property('Capabilities'))-20)) & 8) = 8)
THEN 1
ELSE 0
END IF

A value of 1 indicates the database is using the SYSTEM PROCEDURE INVOKER model. A value of 0 indicates that the database is using the SYSTEM PROCEDURE DEFINER.

The MANAGE ANY OBJECT PRIVILEGE system privilege is required to grant the EXECUTE object-level privilege.

To grant the privilege, execute:

GRANT EXECUTE
  ON [ owner.] {procedure-name | user-defined-function-name }
  TO userID [,...]