Oracle package dependencies

Applies to: On premises

To function correctly, the IBM OpenPages® Oracle packages must have access to some standard Oracle objects.

In a standard Oracle deployment, database users can access the objects that are listed in the following tables. Some customer environments might restrict the default Oracle access model and remove public access from some of these objects. To use the OpenPages application, users require access to all objects in the tables.

The following tables list the standard Oracle objects to which the OpenPages application requires access. The tables show the base object, the object name, and the public synonym for the Oracle database objects.

Table 1. Base objects for the package object type
Base Object Object Name Public Synonym
SYS.DBMS_LOB DBMS_LOB PUBLIC.DBMS_LOB
SYS.DBMS_LOCK DBMS_LOCK PUBLIC.DBMS_LOCK
SYS.DBMS_JOB DBMS_JOB PUBLIC.DBMS_JOB
SYS.DBMS_OUTPUT DBMS_OUTPUT PUBLIC.DBMS_OUTPUT
SYS.DBMS_RANDOM DBMS_RANDOM PUBLIC.DBMS_RANDOM
SYS.DBMS_SESSION DBMS_SESSION PUBLIC.DBMS_SESSION
SYS.DBMS_SNAPSHOT DBMS_MVIEW PUBLIC.DBMS_MVIEW
SYS.DBMS_SQL DBMS_SQL PUBLIC.DBMS_SQL
SYS.DBMS_STANDARD DBMS_STANDARD PUBLIC.DBMS_STANDARD
SYS.DBMS_STATS DBMS_STATS PUBLIC.DBMS_STATS
SYS.DBMS_UTILITY DBMS_UTILITY PUBLIC.DBMS_UTILITY
SYS.ODCICONST ODCICONST PUBLIC.ODCICONST
SYS.PLITBLM PLITBLM PUBLIC.PLITBLM
SYS.STANDARD STANDARD N/A
SYS.UTL_I18N UTL_I18N PUBLIC.UTL_I18N
Table 2. Base objects for the view object type
Base Object Object Name Public Synonym
SYS.ALL.PROCEDURES ALL_PROCEDURES PUBLIC.ALL_PROCEDURES
SYS.ALL_TAB_PRIVS ALL_TAB_PRIVS PUBLIC.ALL_TAB_PRIVS
SYS.NLS_SESSION_ PARAMETERS NLS_SESSION_PARAMETERS PUBLIC.NLS_SESSION_PARAMETERS
SYS.PRODUCT_COMPONENT_ VERSION PRODUCT_COMPONENT_VERSION PUBLIC.PRODUCT_ COMPONENT_VERSION
SYS.USER_CONS_COLUMNS USER_CONS_COLUMNS PUBLIC.USER_CONS_ COLUMNS
SYS.USER_CONSTRAINTS USER_CONSTRAINTS PUBLIC.USER_ CONSTRAINTS
SYS.USER_DB_LINKS USER_DB_LINKS PUBLIC.USER_DB_LINKS
SYS.USER_IND_COLUMNS USER_IND_COLUMNS PUBLIC.USER_IND_ COLUMNS
SYS.USER_INDEXES USER_INDEXES PUBLIC.USER_INDEXES
SYS.USER_OBJECTS USER_OBJECTS PUBLIC.USER_OBJECTS
SYS.USER_SEGMENTS USER_SEGMENTS PUBLIC.USER_SEGMENTS
SYS.USER_SEQUENCES USER_SEQUENCES PUBLIC.USER_ SEQUENCES
SYS.USER_SOURCE USER_SOURCE PUBLIC.USER_SOURCE
SYS.USER_TAB_COLS USER_TAB_COLS PUBLIC.USER_TAB_COLS
SYS.USER_TAB_COLUMNS USER_TAB_COLUMNS PUBLIC.USER_TAB_ COLUMNS
SYS.USER_TABLES USER_TABLES PUBLIC.USER_TABLES
SYS.USER_TABLESPACES USER_TABLESPACES PUBLIC.USER_ TABLESPACES
SYS.USER_TRIGGERS USER_TRIGGERS PUBLIC.USER_TRIGGERS

Users also require access to all synonyms. If any public synonyms are removed from a default Oracle deployment, you must create a private synonym to the object in the OpenPages application user schema.

If these permissions are not available to public when you install OpenPages, the installation process grants the required permissions directly to the openpages database user automatically. Alternatively, you can grant these permissions back to public before you run the OpenPages installation process. If you want to do this, follow these steps to grant explicit permission to an object:
  1. Log on to a computer with SQL*Plus and access to the database server.
  2. From the command line, log on to SQL*Plus:
    sqlplus sys/\"<password>\"@<tns_alias> as sysdba
  3. At the SQL prompt, type the following command for the objects that are listed in Table 1:
    grant execute on <object_name> to public;

    Type the following command for the objects listed in Table 2:

    grant select on <object_name> to public;

Each package object requires the EXECUTE permission. All other objects require the SELECT permission.