Oracle E-Business Suite Architecture
Note that depending on the version you are running and how you arrived at that version, some of these settings may have been set by default. All you have to do in that case is verify that the settings are as described here. This chapter contains security recommendations for the database.
Follow the hardening instructions for Operating Environment Security.
To support XDB, the TNS listener process listens on two additional TCP ports: 2100 for FTP access and 8080 for http access. Oracle E-Business Suite does not require these services; they should be disabled.
To disable XDB, remove or comment out the line in
init.ora that reads:
Review database links in both production and development environments and drop those that are not required in your environment.
Security policy must balance risk of attack, cost of defense and value of data protected. This section contains recommendations that improve security, but may not be appropriate for every deployment.
Transparent data encryption (TDE) protects the data at rest by encrypting the data stored in the database data files. Oracle E-Business Suite Release 12.2 is certified with Column Encryption and Tablespace Encryption. See the following My Oracle Support Knowledge Documents for details:
1585296.1 - Using TDE Tablespace Encryption with Oracle E-Business Suite Release 12.2
1585696.1 - Using TDE Column Encryption with Oracle E-Business Suite Release 12.2
Column level TDE has a number of restrictions related to data types and indexed columns. Tablespace TDE does not have these restrictions and does not increase the storage requirement.
The Oracle database uses the external procedure service to call external C programs. This extends the functionality of PL/SQL to routines that can be written in C to perform complex calculations, such as mathematical modeling or files system interactions. This functionality exploits the ability of the listener to issue operating system commands. The external procedures are supposed to issue the commands to the listener on a special IPC pipe named EXTPROC. The specification exists in the
listener.ora parameter file as:
(ADDRESS_LIST = (ADDRESS = (PRTOCOL = IPC) (KEY = EXTPROC))
These external procedures operate by instructing the listener to issue these operating system commands on their behalf. Because the listener runs with the privilege of the operating system user, the only limits on external procedures are the limits on what that account can do.
The following Oracle E-Business suite components use EXTPROC services:
Oracle interMedia cartridges
Oracle Email Center
Oracle Demand Planning Express implementation
To protect against some EXTPROC attack vectors:
Create two Oracle TNS listeners, one for the Oracle database and one for PL/SQL EXTPROC.
Remove EXTPROC specific entries from the Oracle Database listener configuration files.
Configure the Oracle EXTPROC listener with an IPC protocol address only.
If TCP connectivity is required, configure a TCP protocol address, but use a port other than the one the Oracle listener for the database is using. Ensure that the Oracle listener created for PL/SQL EXTPROC runs as an unprivileged operating system user (e.g., "nobody" on UNIX). On Windows platforms, run the Oracle TNS listener process as an unprivileged user and not as the Windows LOCAL SYSTEM user. Give this user the operating system privilege to "Logon as a service."
If the Oracle listener for PL/SQL EXTPROC has been configured with a TCP address, do the following:
Modify the EXTPROC specific entry in
$ORACLE_HOME/network/admin/tnsnames.ora to reflect the correct port for the new Oracle listener.
Enable Valid Node Checking and restrict access to those network clients requiring EXTPROC.
Restrict access to the Oracle listener for PL/SQL EXTPROC only. Use a separate
$TNS_ADMIN/sqlnet.ora file for this Oracle listener. Store this file in any directory other than the one in which the database
sqlnet.ora files are located. Copy the
listener.ora with the configuration of the Oracle listener for PL/SQL EXTPROC into this other directory as well. Before starting the Oracle listener for PL/SQL EXTPROC, set the TNS_ADMIN environment variable (or Windows Registry parameter) to specify the directory in which the new configuration files for PL/SQL EXTPROC are stored.
Ensure that the file permissions on separate
$TNS_ADMIN/listener.ora are set to 600. Because it contains the password, only the owner should read the file.
Change the password to a strong password for any privileged database account or an ordinary user given administrative privileges in the database that has the ability to add packages or libraries and access system privileges in the database (such as CREATE ANY LIBRARY). This step may not be applicable for default Oracle E-Business Suite implementations. This may be useful for customizations that involve addition of new schemas or customized PL/SQL code to be called as an external procedure service.
See below for the format of the dedicated EXTPROC listener. The parameters appear in
$TNS_ADMIN/listener.ora. Replace the $ORACLE_SID with the name of the Oracle database instance (SID), $ORACLE_HOME with the value of ORACLE home directory for this listener, and $TNS_ADMIN with the directory location of the listener parameter files.
$ORACLE_SID_EXTPROC = (ADDRESS_LIST = (ADDRESS= (PROTOCOL= IPC)(KEY= EXTPROC$ORACLE_SID)) ) SID_LIST_$ORACLE_SID_EXTPROC = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = $ORACLE_HOME) (PROGRAM = extproc) ) ) STARTUP_WAIT_TIME_$ORACLE_SID_EXTPROC = 0 CONNECT_TIMEOUT_$ORACLE_SID_EXTPROC = 10 TRACE_LEVEL_$ORACLE_SID_EXTPROC = OFF LOG_DIRECTORY_$ORACLE_SID_EXTPROC = $TNS_ADMIN LOG_FILE_$ORACLE_SID_EXTPROC = $ORACLE_SID_EXTPROC TRACE_DIRECTORY_$ORACLE_SID_EXTPROC = $TNS_ADMIN TRACE_FILE_$ORACLE_SID_EXTPROC = $ORACLE_SID_EXTPROC
The configuration below should appear in
$TNS_ADMIN/tnsnames.ora. Replace $ORACLE_SID with the name of the Oracle database instance (SID).
extproc_connection_data = (DESCRIPTION= (ADDRESS_LIST = (ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC$ORACLE_SID)) ) (CONNECT_DATA= (SID=PLSExtProc) (PRESENTATION = RO) ) )
This example shows how to configure EXTPROC listener services. In it, the LISTENER NAME is VSEC_EXTPROC and ORACLE_SID is VSEC.
VSEC_EXTPROC = (ADDRESS_LIST = (ADDRESS= (PROTOCOL= IPC)(KEY= EXTPROCVSEC)) ) SID_LIST_VSEC_EXTPROC = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/oracle/vsecdb/10.2.0.5) (PROGRAM = extproc) ) ) STARTUP_WAIT_TIME_VSEC_EXTPROC = 0 CONNECT_TIMEOUT_VSEC_EXTPROC = 10 TRACE_LEVEL_VSEC_EXTPROC = OFF LOG_DIRECTORY_VSEC_EXTPROC = /u01/oracle/vsecdb/10.2.0.5/network/admin LOG_FILE_VSEC_EXTPROC = VSEC_EXTPROC TRACE_DIRECTORY_VSEC_EXTPROC = /u01/oracle/vsecdb/10.2.0.5/network/admin TRACE_FILE_VSEC_EXTPROC = VSEC_EXTPROC
extproc_connection_data = (DESCRIPTION= (ADDRESS_LIST = (ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROCVSEC)) ) (CONNECT_DATA= (SID=PLSExtProc) (PRESENTATION = RO) ) )
This section explains a procedure to test if EXTPROC is enabled. The EXTPROC listener must be configured and working for the Oracle interMedia option to run. Perform the following to test whether or not Oracle interMedia is working:
Create a user to work with Oracle interMedia text:
create user textuser identified by <password> default tablespace users temporary tablespace temp;
Grant 'ctxapp' role to textuser:
grant connect, resource, ctxapp to textuser;
Connect as textuser and create required test objects:
connect textuser/<password> drop table quick; create table quick ( quick_id number constraint quick_pk primary key, text varchar2(80) 0; insert into quick ( quick_id, text ) values ( 1, 'The cat sat on the mat' ); insert into quick ( quick_id, text ) values ( 2, 'The quick brown fox jumps over the lazy dog' ); insert into quick ( quick_id, text ) values ( 3, 'The dog barked like a dog' ); commit; create index quick_text on quick ( text ) indextype is ctxsys.context; col text format a45 col s format 999 select text, score(42) s from quick where contains ( text, 'dog', 42 ) >= 0 order by s desc;
If the above query works without any error, the Oracle interMedia option is enabled and the EXTPROC listener is properly configured.
Cleanup the test user (
textuser) created during this test.
Middle tier applications logon to the database through application schemas rather than end-user accounts. Some individuals (IT Administrators) may require direct access to the application database using their own schema.
This setting prevents the database from using an insecure logon protocol. Make sure
Following an installation, the application database instance contains default, open schemas with default passwords. These accounts and corresponding passwords are well-known, and they should be changed, especially for a database to be used in a production environment. Default schemas come from different sources:
Default database administration schemas
Schemas belonging to optional database features neither used nor patched by Oracle E-Business Suite
Schemas belonging to optional database features used but not patched by Oracle E-Business Suite
Schemas belonging to optional database features used and patched by Oracle E-Business Suite
Schemas common to all Oracle E-Business Suite products
Schemas associated with specific Oracle E-Business Suite products
Note: Starting in Oracle E-Business Suite Release 12.1.2, a new command-line utility named AFPASSWD is available to replace FNDCPASS. The new utility does not require passwords on the command line. Just like FNDCPASS, AFPASSWD is installed on the application tier and requires the libraries from the Tools Oracle home.
For the schemas in categories 1, 2, and 3, use standard database commands to change a password:
SQL> alter user <SCHEMA> identified by <NEW_PASSWORD>;
For the schemas in categories 4, 5, and 6 where Oracle E-Business Suite is "managing" the passwords, use the application password change tool AFPASSWD (or FNDCPASS):
$ AFPASSWD -c apps -o <SCHEMA>
You will be prompted for the APPS password and the new password (twice).
To save time, category six (6) schema passwords may be changed en masse using AFPASSWD. This is really handy as there are more than 200 schema passwords. AFPASSWD takes the
-a option which will change all category 6 passwords to the new password (this works the same as the ALLORACLE mode in FNDCPASS).
$ AFPASSWD -c apps -a
You will be prompted for the APPS password and the new password (twice).
To determine which schemas are managed by Oracle E-Business Suite (categories 4, 5, and 6), run the AD
Appendix B: Database Schemas Found in Oracle E-Business Suite contains a list of the schemas by category, instructions, and notes for managing schema passwords.
AFPASSWD only prompts for the passwords required for the current operation, allowing separation of duties between application administrators and database administrators. This also improves interoperability with Oracle Database Vault.
AFPASSWD is documented in the Oracle E-Business Suite Maintenance Guide.
The database provides parameters to enforce password management policies. However, some of the database password policy parameters could lock-out the Oracle E-Business Suite. Because of this, we make specific recommendations for or against using certain management features depending upon schema type.
Database "profiles" contain limits on database resources and password policies. Create two database profiles: one for middle tier application schemas ("managed schemas") and one for human beings. Assign middle tier application schemas to the first profile and all accounts used by individual database administrators to the second profile.
|Password Parameters||Application Profile||Administrator Profile|
For more information on profiles, see CREATE PROFILE in the Oracle SQL reference documentation.
See Appendix B: Database Schemas Found in Oracle E-Business Suite for a list of all default database users and whether it is a managed schema.
_TRACE_FILES_PUBLIC grants file system read access to anyone who has activated SQL tracing. Set this to its default value of False.
REMOTE_OS_ROLES to False to prevent insecure remote roles.
UTL_FILE_DIR limits file system access for all database accounts using the PL/SQL API
UTL_FILE. Oracle E-Business Suite requires the
UTL_FILE_DIR database initialization parameter to be set when you are using Oracle Database 11g Release 2 or Oracle Database 12c Release 1. See My Oracle Support Knowledge Document 2525754.1, Using UTL_FILE_DIR or Database Directories for PL/SQL File I/O in Oracle E-Business Suite Releases 12.1 and 12.2.
O7_DICTIONARY_ACCESSIBILITY to False to prevent users with the 'Select ANY' privilege from reading data dictionary tables. False is the default for the 10g database.
O7_DICTIONARY_ACCESSIBILITY = FALSE
The following table lists the privileges that should be granted to the APPLSYSPUB schema. These can be set by
<FND_TOP>/admin/sql/afpub.sql or fixed by
In Oracle E-Business Suite Release 12.2.2, these files were relocated to
|Privileges to be Granted to the APPLSYSPUB Schema|
|EXECUTE ON FND_DISCONNECTED|
|EXECUTE ON FND_MESSAGE|
|EXECUTE ON FND_PUB_MESSAGE|
|EXECUTE ON FND_SECURITY_PKG|
|EXECUTE ON FND_WEBFILEPUB|
|INSERT ON FND_SESSIONS|
|INSERT ON FND_UNSUCCESSFUL_LOGINS|
|SELECT ON FND_APPLICATION|
|SELECT ON FND_APPLICATION_TL|
|SELECT ON FND_APPLICATION_VL|
|SELECT ON FND_LANGUAGES_TL|
|SELECT ON FND_LANGUAGES_VL|
|SELECT ON FND_LOOKUPS|
|SELECT ON FND_PRODUCT_GROUPS|
|SELECT ON FND_PRODUCT_INSTALLATIONS|
|SELECT ON FND_NEW_MESSAGES|
The following table lists privileges required for online patch in Oracle E-Business Suite Release 12.2:
|Privileges Required for Oracle E-Business Suite Release 12.2 Online Patching|
|INSERT ON FND_SESSIONS#|
|INSERT ON FND_UNSUCCESSFUL_LOGINS#|
|SELECT ON FND_APPLICATION#|
|SELECT ON FND_APPLICATION_TL#|
|SELECT ON FND_LANGUAGES_TL#|
|SELECT ON FND_PRODUCT_GROUPS#|
|SELECT ON FND_PRODUCT_INSTALLATIONS#|
|SELECT ON FND_NEW_MESSAGES#|
To check permissions, login as SYSTEM or APPS and issue the following query:
SELECT * FROM dba_tab_privs WHERE grantee ='APPLSYSPUB';
To revoke unnecessary privileges granted to
APPLSYSPUB schema, see patch 3763612. Oracle E-Business Suite Release 12 Rapid Install has a clean
APPLSYSPUB by default. In addition, you should understand the implications of privileges on custom objects granted to PUBLIC or a role.