Oracle clients communicate with the database using the Transparent Network Substrate (TNS) protocol. When the listener receives a connection request (tcp port 1521, by default), it starts up a new database process and establishes a connection between the client and the database. This chapter contains security recommendations for the database TNS listener.
Follow the hardening instructions for Operating Environment Security.
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 Multimedia (formerly 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 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.
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 Multimedia option to run. Perform the following to test whether or not Oracle Multimedia is working:
Create a user to work with Oracle Multimedia 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 Multimedia option is enabled and the EXTPROC listener is properly configured.
Cleanup the test user (textuser) created during this test.
Valid Node Checking allows or denies access from specified IP addresses to Oracle services. Oracle recommends using an allowlist of IP addresses that are authorized to make a TCP connection to the database listener. To enable Valid Node Checking, set the following parameters in $TNS_ADMIN/sqlnet.ora:
tcp.validnode_checking = YES tcp.invited_nodes = ( x.x.x.x, hostname.domain, ... )
The first parameter turns on Valid Node Checking. The next parameter specify the IP addresses or host names that are permitted to make network connections to the database. Replace x.x.x.x with the application tiers' IP addresses. Application tier components include web servers, forms servers, concurrent managers, terminal servers, central administrator machines, and any remote monitoring tool that uses SQL*Net.
Note: The use of SQLNet desktop clients such as sqlplus, sqldeveloper, toad, or ADI from a windows desktop is not recommended on production databases. If implemented, the desktop cannot use DHCP (unless the DHCP server is configured with address reservation). Oracle recommends that only trusted servers be allowed to make direct database connections.
AutoConfig supports automated configuration of this setting. If the profile option "SQLNet Access" (FND_SQLNET_ACCESS) is set to "ALLOW_RESTRICTED" at the site level when AutoConfig is run on the database server, AutoConfig will add IP restrictions to sqlnet.ora. The list of host will be all those from the FND_NODES table that are registered as an Oracle E-Business Suite node.
For more information, refer to the "Technical Configuration" chapter of the Oracle E-Business Suite Setup Guide, Release 12.2.
The easiest way to verify this is to implement a manual check from a node not in the allowlist. You should not be able to connect (by using Ncat, for example) to the database listener port. When connecting through an untrusted node, it should look like this:
# nc -v db.example.com 1521 Ncat: Version 7.70 ( https://nmap.org/ncat ) Ncat: Connected to X.X.X.X:1521. Ncat: Broken pipe.
The listener log will show this for a connection attempt from a client that is not "invited".
... <txt> Incoming connection from Y.Y.Y.Y rejected ... <txt> TNS-12546: TNS:permission denied TNS-12560: TNS:protocol adapter error TNS-00516: Permission denied
In $TNS_ADMIN/listener.ora , set the following parameter:
CONNECT_TIMEOUT_$ORACLE_SID = 10
For example:
CONNECT_TIMEOUT_PRD12 = 10
Where PRD12 is the value of the ORACLE_SID in this example.
Use the parameter CONNECT_TIMEOUT to specify the amounts of time, in seconds, for the Oracle listener to wait for the connection from a client to complete.
This section describes configuration options and recommendations for enabling encryption of database network traffic.
The following table is a summary of the three available configurations for encrypting traffic to the Oracle E-Business Suite database, which are described in more detail in the sections to follow.
| Configuration Option | Database | Oracle E-Business Suite Application Tier Connections | Other Client Connections |
|---|---|---|---|
| Configuration 1: Enable Native Network Encryption on the Database Listener and Use TCP for All Client Connections | TCP with Native Network Encryption (NNE) | TCP | TCP |
| Configuration 2: Enable Native Network Encryption on the Database Listener and Enable TCPS for Non-Oracle E-Business Suite Application Tier Client Connections | TCP with NNE | TCP | TCPS |
| Configuration 3: Enable TCPS for All Client Connections | TCPS | TCPS using Oracle Connection Manager (CMAN) | TCPS |
In this configuration, native network encryption (NNE) is enabled on the database listener and a TCP connection is used for both Oracle E-Business Suite and non-Oracle E-Business Suite clients.
For most environments, NNE in conjunction with following guidance for Oracle E-Business Suite secure configuration deployment is sufficient for securing connections to your database. Oracle E-Business Suite secure configuration deployment provides additional protection for your Oracle E-Business Suite application and database tiers and includes the use of subnets, firewalls, and DMZs.
To set up Configuration 1, perform the following steps:
Deploy your Oracle E-Business Suite environment per the recommendations outlined in Restricting Network Access to Critical Services in "Overview of Secure Configuration."
Enable NNE for the database using the following steps:
Add the following lines to the $TNS_ADMIN/sqlnet_ifile.ora in your database Oracle home:
SQLNET.ENCRYPTION_SERVER=REQUIRED SQLNET.ENCRYPTION_TYPES_SERVER=(AES128,AES192,AES256) SQLNET.CRYPTO_CHECKSUM_SERVER=REQUIRED SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER=(SHA256,SHA1) SQLNET.CRYPTO_SEED=<SEED> SQLNET.IGNORE_ANO_ENCRYPTION_FOR_TCPS=TRUE
The value for SQLNET.CRYPTO_SEED for your deployment should be a random string of up to 70 characters.
If you have applied the July 2021 Database CPU or later, also add the following to the $TNS_ADMIN/sqlnet_ifile.ora in your database Oracle home :
SQLNET.ALLOW_WEAK_CRYPTO_CLIENTS=TRUE
Restart the database listener.
If you determine that encryption of database client connections other than the Oracle E-Business Suite application tier node database connections is a requirement for your environment, in addition to NNE you may enable TCPS for these client connections to the Oracle E-Business Suite database.
To set up Configuration 2, perform the following steps:
Enable NNE for the database using the instructions in step 2 of Configuration 1.
Enable TCPS for non-Oracle E-Business Suite application tier client connections. For more information, see My Oracle Support Knowledge Document 2867473.1, Enable TCPS (TLS1.2) for Clients Outside the Oracle E-Business Suite Application Tier.
If you determine that encryption of all database client connections including Oracle E-Business Suite application tier connections is a requirement for your environment, you may enable TCPS for all database client connections to the Oracle E-Business Suite database. Refer to My Oracle Support Knowledge Document 2787151.1, Enable TCPS (TLSv1.2) for SQL*Net Traffic in Oracle E-Business Suite Release 12.2.
You should configure the database listener with ADMIN_RESTRICTIONS set to ON. When ADMIN_RESTRICTIONS is ON, all the set commands in lsnrctl are disabled and the only way to change the configuration is to edit the listener.ora file.
In $TNS_ADMIN/listener.ora, set the following parameter:
ADMIN_RESTRICTIONS_<listener>=ON
For example:
ADMIN_RESTRICTIONS_VIS12=ON
where VIS12 is the name of the listener (equal to ORACLE_SID in Oracle E-Business Suite)
AutoConfig can set this if you set the AutoConfig variable s_admin_restrictions to ON and run AutoConfig on the database server.
To enable logging, in $TNS_ADMIN/listener.ora set the following parameters:
LOG_STATUS = ON LOG_DIRECTORY_$ORACLE_SID = $TNS_ADMIN LOG_FILE_$ORACLE_SID = $ORACLE_SID
For example:
LOG_STATUS = ON LOG_DIRECTORY_VIS12 = /u/db/tech_st/10.2.0/network/admin/VIS12_dbs01 LOG_FILE_VIS12 = VIS12
Where VIS21 is the LISTENER_NAME.
This is done by default in Release 12.
Notice that newer database versions use the richer ADR log settings. This silently overrides/ignores the older LOG settings. See Oracle Database documentation for more details.