Oracle Database Security

About Oracle Database Security

Oracle E-Business Suite Architecture

the picture is described in the document text

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.

Hardening

Hardening Operating Environment

Follow the hardening instructions for Operating Environment Security.

Disabling XDB

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:

*.dispatchers='(PROTOCOL=TCP) (SERVICE=sidXDB)'

Review Database Links

Review database links in both production and development environments and drop those that are not required in your environment.

Optional Secure Configurations

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)

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:

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.

Hardening External Procedure (EXTPROC) Services

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:

  1. Oracle interMedia cartridges

  2. Oracle Email Center

  3. Oracle Demand Planning Express implementation

To protect against some EXTPROC attack vectors:

  1. Create two Oracle TNS listeners, one for the Oracle database and one for PL/SQL EXTPROC.

  2. Remove EXTPROC specific entries from the Oracle Database listener configuration files.

  3. 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."

  4. If the Oracle listener for PL/SQL EXTPROC has been configured with a TCP address, do the following:

    1. Modify the EXTPROC specific entry in $ORACLE_HOME/network/admin/tnsnames.ora to reflect the correct port for the new Oracle listener.

    2. Enable Valid Node Checking and restrict access to those network clients requiring EXTPROC.

    3. 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 listener.ora and 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.

  5. 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.

  6. 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.

EXTPROC Listener Configuration

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)
            ) )

Example: EXTPROC Listener Configured Separately

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

Example: The tnsnames.ora Parameter That Corresponds to EXTPROC Listener

extproc_connection_data =
         (DESCRIPTION=
            (ADDRESS_LIST =
               (ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROCVSEC))
            )
            (CONNECT_DATA=
               (SID=PLSExtProc)
               (PRESENTATION = RO)
            ) )

EXTPROC Testing Procedure

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:

  1. Create a user to work with Oracle interMedia text:

    create user textuser identified by <password>
    default tablespace users temporary tablespace temp;
  2. Grant 'ctxapp' role to textuser:

    grant connect, resource, ctxapp to textuser;
  3. 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.

Authentication

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.

Removing Operating System Trusted Remote Logon

This setting prevents the database from using an insecure logon protocol. Make sure init.ora contains:

REMOTE_OS_AUTHENT=FALSE

Changing Default Installation Passwords

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:

  1. Default database administration schemas

  2. Schemas belonging to optional database features neither used nor patched by Oracle E-Business Suite

  3. Schemas belonging to optional database features used but not patched by Oracle E-Business Suite

  4. Schemas belonging to optional database features used and patched by Oracle E-Business Suite

  5. Schemas common to all Oracle E-Business Suite products

  6. 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 adutconf.sql script.

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.

Implementing Two Profiles for Password Management

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.

Application and Administrator Profile Password Parameters
Password Parameters Application Profile Administrator Profile
FAILED_LOGIN_ATTEMPTS UNLIMITED 5
PASSWORD_LIFE_TIME UNLIMITED 90
PASSWORD_REUSE_TIME 180 180
PASSWORD_REUSE_MAX UNLIMITED UNLIMITED
PASSWORD_LOCK_TIME UNLIMITED 7
PASSWORD_GRACE_TIME UNLIMITED 14
PASSWORD_VERIFY_FUNCTION Recommended Recommended

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.

Authorization

Restricting Access to SQL Trace Files

The init.ora parameter _TRACE_FILES_PUBLIC grants file system read access to anyone who has activated SQL tracing. Set this to its default value of False.

_TRACE_FILES_PUBLIC=FALSE

Removing Operating System Trusted Remote Roles

Set the init.ora parameter REMOTE_OS_ROLES to False to prevent insecure remote roles.

REMOTE_OS_ROLES=FALSE

Limiting File System Access Within PL/SQL

The parameter 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.

UTL_FILE_DIR=<dir1>,<dir2>,<dir3>...

Avoid:

UTL_FILE_DIR=*

Limiting Dictionary Access

Set 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

Revoking Unnecessary Grants Given to APPLSYSPUB

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 <FND_TOP>/admin/sql/afpubfix.sql.

In Oracle E-Business Suite Release 12.2.2, these files were relocated to FND_TOP>/patch/115/sql/.

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.