MEDIDAS PARA EL ANÁLISIS DE LA DISTRIBUCIÓN DEL INGRESO

As we discussed in the previous section, the extproc process acts as the

communication channel between PL/SQL and the external program. It is started by the Oracle listener and interacts with the external program through the DLL and transmits back the program output through the same passage.

The extproc process activation is governed by the Oracle Net services where the TNSNAMES.ora and LISTENER.ora files are configured by default by the Oracle Net

Configuration assistant during database software installation. However, the net configuration can be modified to enforce and ensure high-level security. We will now discuss the requirements of Oracle Net services configuration and management. Both the ORA files can be found under $ORACLE_HOME\network\admin\ directory. One

must keep a hawk eye on the security scheme to modify the files on the server as we will be working with the ORA files in this section.

TNSNAMES.ora

The TNSNAMES.ora file provides the database connection aliases or service names

to connect to the appropriate listener and, hence, the target database. It takes the requests from the client which is trying to establish the connection with the database.

In the default TNSNAMES.ora file, which is configured during the database software

installation, a ORACLR_CONNECTION_DATA service is created to support external

services. This service is responsible for activating the extproc process. It verifies the

network connection using the ADDRESS parameter value, followed by a connection

establishment using the CONNECT_DATA parameter value. The service entry in the TNSNAMES.ora file looks as follows:

ORACLR_CONNECTION_DATA = (DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) (CONNECT_DATA = (SID = CLRExtProc) (PRESENTATION = RO) ) )

In the preceding entry, the parameters KEY and SID have variable values while other

values are fixed. The values must be concurrent with those of the corresponding listener entries.

The ADDRESS parameter value checks for the listeners which can receive IPC

(Internet Procedure Calls) requests through the KEY value EXTPROC1521. The PROTOCOL parameter has a fixed value IPC to establish the interaction between

the server and the external service requests. Once the ADDRESS setup matches the

current active listener, it uses the CONNECT_DATA parameter value to shoot the extproc process. The PRESENTATION parameter is a performance booster parameter

which directs the database server to concentrate and respond to the client through a protocol—Remote-Ops (RO).

Note the CONNECT_DATA parameter value. It searches for the listener with the same SIDCLRExtProc. The CLRExtProc is a listener mode which allows PL/SQL programs to access external programs.

LISTENER.ora

All the database connection requests pass through the listeners. A listener entry in the file contains the network configuration parameters of the server.

The LISTENER entry gives the protocol and key details. From the entry details,

we can notice that it can listen to an IPC request, as well as TCP requests:

LISTENER =

(DESCRIPTION_LIST = (DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) )

)

The SID_LIST_LISTENER contains the SID details of the external services which can

interact with the LISTENER listener. It contains the SID_NAME, ORACLE_HOME, and PROGRAM parameters. The SID_NAME parameter must be in sync with the SID value

under the CONNECT_DATA parameter of the ORACLR_CONNECTION_DATA service in TNSNAMES.ora. It identifies the extproc process. The PROGRAM parameter value is

used for the program identification for extproc.

The SID_LIST_LISTENER entry in the LISTENER.ora is as follows:

SID_LIST_LISTENER = (SID_LIST = (SID_DESC =

(SID_NAME = CLRExtProc)

(ORACLE_HOME = <<Oracle Home>>) (PROGRAM = EXTPROC1521)

(ENVS= "EXTPROC_DLLS=[ONLY | ANY | (DLL path)]") )

)

The location of the shared library file has to be registered in the preceding SID_ LIST_LISTENER entry. From Oracle 9.2 and higher, Oracle has imposed restriction

on the default location of shared libraries. By default, it can interpret the shared libraries located in the $ORACLE_HOME\bin\ directory. If the shared library is

located at another location on the server, it has to be specified in the EXTPROC_DLLS

environment parameter. The permissible value for the environment are ONLY, ANY, or

the actual DLL path.

• ONLY:[DLL:DLL...] gives the authority to specify multiple DLL files in

varied locations. The DLL file paths are separated by a colon. It offers high-level security as it limits the libraries to be interpreted by extproc.

• [DLL : DLL ...]: One can specify only the DLL path without using the ONLY parameter. In such case, all the DLLs under the $ORACLE_HOME\bin\

directory are accessible by the extproc process.

Some of the sample ENVS parameter looks as follows:

• ENVS= "EXTPROC_DLLS=ANY" (allows any DLL on the server to be loaded by

the extproc process)

• ENVS= "EXTPROC_DLLS=ONLY:C:\MyDLL\Hello.dll"

• ENVS= "EXTPROC_DLLS=ONLY:C:\MyDLL\Hello.dll:C:\TestDLL\Math. dll"

These specifications allow only the DLLs from the specified locations to be loaded by the extproc process.

In secured production environments, it is advisable to include a separate listener for the extproc process to segregate the handling of the IPC and TCP requests through

the SID_LIST entries. In that case, follow the following steps:

1. Take the backup of the existing LISTENER.ora and TNSNAMES.ora files.

2. Modify the existing LISTENER and SID_LIST_LISTENER entries for TCP

requests and SID_NAME, respectively.

3. Add the EXTPROC_LISTENER and SID_LIST_EXTPROC_LISTENER entries in

the LISTENER.ora file. An example of externally registered LISTENER entries

is shown as follows:

LISTENER =

(DESCRIPTION_LIST = (DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = <<host>>)(PORT = 1521)) )

)

SID_LIST_LISTENER = (SID_LIST = (SID_DESC =

(SID_NAME = <<Database Name>>) (ORACLE_HOME = <<Oracle Home>>) )

)

EXTPROC_LISTENER = (DESCRIPTION_LIST = (DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = <<extproc key>>)) )

)

SID_LIST_EXTPROC_LISTENER = (SID_LIST =

(SID_DESC =

(SID_NAME = CLRExtProc)

(ORACLE_HOME = <<Oracle Home>>) (PROGRAM = EXTPROC1521)

(ENVS= "EXTPROC_DLLS=[ONLY | ANY | (DLL path)]") )

)

4. The original service has to be rebuilt to inherit the changes and a new service has to be created for the new listener.

With the default settings, Oracle 11g configuration has not been reported for issues in the extproc process establishment, activation, or working.