Oracle Access Requirements
To access Oracle, the following conditions must be met:
• The correct version of Oracle must be in the PATH and accessible.
UNIX:
This condition requires both the binary path and shared library path be set correctly, and that read and execute permissions be granted to access the Oracle installation files. Assigning the 'ingres' operating system user ID to the operating system user group called 'oinstall' can satisfy this requirement. 
Note: Running sqlplus from a shell (UNIX) or command prompt (Windows) shows whether Oracle is currently in the PATH.
• User logins must be defined to the Oracle instance.
• Permission must be granted to access each Oracle database.
• The ingres user must be granted the following permissions:
– DBA
– connect
– resource
Note: For Oracle 12c (Oracle 12.2), “DBA” permission satifies “connect” and “resource”. See Grant User Permissions (Oracle).
• Non-privileged users must be granted the following permissions:
– connect
– resource
• If database events are to be used with the Oracle gateway, the DBMS_ALERT package must be installed. The time to load the Oracle DBMS_ALERT package can be excessive. This overhead will impact connection time if database events are enabled. To avoid the performance overhead, consider pinning the package in the Oracle DBMS.
For more information about using the supplied PL/SQL packages and pinning using the DBMS_SHARED_POOL package, see the Oracle documentation. For more information about using the db event support in the Oracle gateway, see Developing Portable Applications.
To grant permissions for DBMS_ALERT, see Grant User Permissions (Oracle).
A non-privileged userid connecting to Enterprise Access 3.0-to-Oracle using the SQL Terminal Monitor may encounter error message “sql PLS-00201: identifier 'DBMS_ALERT' must be declared”. See Grant User Permissions (Oracle) regarding DBMS_ALERT permissions.
• The correct version of Oracle must be included in the PATH statement. This reference should point to a regular Oracle client installation or an oracle Instant Client (see Oracle Instant Client Support).
• Enterprise Access to-Oracle supports any number of “pluggable” databases (PDBs) in a multi-tenant “container database” (CDB). A PDB is a portable collection of data and meta-data that appears to Enterprise Access as a non-CDB database. All Oracle databases prior to Oracle 12c were non-CDBs. To use Enterprise Access with an Oracle 12c DBMS, the database must be a PDB, not a CDB. Enterprise Access cannot connect to a CDB.
• Enterprise Access requires the Oracle parameter NLS_LENGTH_SEMANTICS=BYTE (the default Oracle setting).
Oracle Instant Client Support
Enterprise Access supports deployment with a local install of the regular Oracle (DBMS or client) installation or the Oracle Instant Client. Instant Client is available from the Oracle website.
Enterprise Access requires only the Basic client installation, and the shared library path (to include the Oracle directories). Consult the Oracle documentation for installation requirements.
Enterprise Access expects tnsnames to be created as per a normal Oracle installation.
Grant User Permissions (Oracle)
The following example shows how to grant 'dba' and other privileges to user 'ingres' or to create a non-privileged DBMS user 'fred' with the password 'secret'. It also shows how to grant public permissions for DBMS_ALERT.
To grant permissions
1. Connect as a database administrator to the Oracle instance.
2. Assign the permissions as follows:
For a 'dba' userid:
grant dba to ingres;
grant unlimited tablespace to ingres;
grant select on sys.all_objects to ingres with grant option;
grant select on sys.all_tables to ingres with grant option;
grant select on sys.all_views to ingres with grant option;
grant select on sys.all_users to ingres with grant option;
grant select on sys.all_source to ingres with grant option;
grant select on sys.all_constraints to ingres with grant option;
grant select on sys.all_tab_comments to ingres with grant option;
grant select on sys.all_col_comments to ingres with grant option;
grant select on sys.all_cons_columns to ingres with grant option;
grant select on sys.all_sequences to ingres with grant option;
For a 'non-dba' userid:
grant connect, resource to fred identified by secret;
To grant permissions for DBMS_ALERT
1. Connect as SYSDBA to the Oracle instance.
2. Assign the permissions as follows:
grant execute on dbms_alert to public;
An alternative is:
grant execute on dbms_alert to user-id;
For more information about DBMS_ALERT requirements, see Oracle Access Requirements.
Last modified date: 08/22/2022