14.2. An Overview of the TNS Listener

The Oracle TNS listener is a server process that provides network connectivity to the Oracle database. The listener is configured to listen for connection requests on a specified port on the database server. When an incoming connection request is received on the port, the listener will attempt to resolve the request and forward the connection information to the appropriate database instance.

A connection request will provide a username, a password, and an Oracle SID or service name. If the listener has been configured to listen for that particular SID or service name, it will forward the login information to that instance. If not, it will return an error message to the requester.

Once the database authenticates the login information, the listener will negotiate with the client to redirect their communications to another port, thus freeing up port 1521. Once the listener and the client agree on a new port, a session will be created between the client and the server on the new port.

The listener is not limited to database connections, however. It can also be used to access executable programs on the database server. This functionality, known as external procedures, was originally introduced to support Oracle Applications, which needed to call binary executables from within PL/SQL packages.

The Oracle listener is invoked through the lsnrctl program. The lsnrctl program resides in the $ORACLE_HOME/b in directory on Unix systems, and in the %ORACLE_HOME% in directory on Windows systems. This program is used to stop and start the listener. It can also be used to configure and manage the listener.

When the listener is started, it reads the contents of the listener.ora file, which is located in the $ORACLE_HOME/network/admin directory in Unix, or the %ORACLE_HOME% etworkadmin directory in Windows, by default. The listener.ora file, in general, will instruct the listener as to the services for which it needs to listen. However, this is not always the case. If no listener.ora file is found, or if the file is empty, Oracle will use its default settings. These defaults will instruct the listener to use the TCP/IP protocol and listen on port 1521. In this configuration, the listener will not support any services at startup time. However, database instances can register themselves with the listener, if appropriately configured.

From a security standpoint, it is important to note that the listener process runs under the user ID of the owner of the lsnrctl executable. This is usually the oracle account on Unix or the administrator account in Windows. Therefore, if the listener is exploited by a buffer overflow or similar attack, the hacker would gain the privileges of these accounts.

But this is not the only security risk. If an intruder can simply gain access to the listener using simple management tools, any of the following could be accomplished:

• Stopping the listener

• Locking out legitimate administrators by setting a password

• Writing to any disk location available to the process owner (usually the oracle or administrator user)

• Obtaining detailed information on database and application setup

• Setting up external process definitions that allow execution of binary executables on the server

As you will see in the next section, this access is surprisingly easy to obtain if proper security measures are not in place.

14.2.1. Managing the Listener Remotely

The listener plays a key role in allowing external access to the Oracle database and to the database server itself. Therefore, restricting access to the listener configuration tools (for example, lsnrctl) seems like a logical step in securing the database server.

What many people are unaware of, however, is that the listener can be managed remotely through port 1521. The lsnrctl program, or a program that provides similar functionality, can be used from a remote site to manage a listener. This means that an intruder would not need to gain access to your server in order to modify your listener.

Remote management can be accomplished by simply following these steps:

The lsnrctl program will now be accessing the listener on the database server. It can process all commands, with the exception of the start command.

While remote management is a very convenient feature for managing remote servers, it also makes the server highly susceptible to outside attacks unless properly secured.

14.2.2. Setting the Listener Password

The most important step in securing the Oracle listener is to set a password for the listener. This step patches the biggest security hole relating to the listener—the fact that no password is required.

Setting the password for the listener can be accomplished using several different methods. The first method is to manually edit the listener.ora file and add the following line:

PASSWORDS_LISTENER = shazam

Obviously, you can set the password to anything you like, and Oracle recommends following standard password security conventions. Once the file has been saved, restart the listener as follows:

LSNRCTL> reload
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
The command completed successfully

This process will successfully set the password, but there is one drawback that you may have already noticed: The password is stored in plaintext inside the file, another security faux pas. The second method of setting the listener password is to use the CHANGE_PASSWORD command from the lsnrctl prompt:

LSNRCTL> change_password
Old password:
New password:
Reenter new password:
  Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
Password changed for LISTENER
The command completed successfully

As you can see in this example, the command prompts for the old password, if one was previously set. If not, you can just press the Enter key. You will be prompted to enter the new password and then to confirm it by entering it a second time. The command does not echo the characters back to the screen, so enter them carefully.Once the password has been set, the change must be saved, otherwise it will exist only for the duration of the current listener session. In order to save the change, you must first use the SET PASSWORD command. Because the listener is now governed by a password, certain administrative commands require that the password be set (entered) before executing. The SET PASSWORD command allows you to enter the password; then you can use the SAVE_CONFIG command to save the change to the listener.ora file, as follows:

LSNRCTL> set password
Password:
The command completed successfully
LSNRCTL> save_config
  Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
Saved LISTENER configuration parameters.
Listener Parameter File" /apps/oracle/product/10g/network/admin/listener.ora
Old Parameter File" /apps/oracle/product/10g/network/admin/listener.bak
The command completed successfully

Once these steps have been completed, you will notice a new section has been added to your listener.ora file. An example is shown here:

#----ADDED BY TNSLSNR 24-OCT-2004 10:23:01---
PASSWORDS_LISTENER = 64F4692D9E64433F
LOGGING_LISTENER = ON
#--------------------------------------------

As you can see, with this method, the password is stored in an encrypted format in the file.

The third method of setting the listener password is to use one of Oracle's graphical tools such as Net Manager (netmgr), Network Creation Assistant (netca), or the Oracle Enterprise Manager (EM) Database Control application.

To set the listener password using EM Database Control, follow these steps:

14.2.3. Controlling Database Access

Oracle Net Services can be configured to accept requests only from specific IP addresses. Conversely, it can be configured to deny requests from specific IP addresses. This functionality is known as valid node checking, and it can be a very powerful security tool in specific network configurations.

In general, databases that serve Internet applications lie behind a firewall. By design, the only access through the firewall should be from the application servers that reside in the demilitarized zone (DMZ). Figure 14.4 shows an example of this configuration.

By implementing valid node checking, only requests coming from the application servers would be accepted, and all others would be denied. This measure will prevent unauthorized access from the Internet.

Valid node checking is implemented by manually modifying the $ORACLE_HOME/network/admin/sqlnet.ora file to add the following settings:

TCP.VALIDNODE_CHECKING This option should be set to a value of YES to enable valid node checking.

TCP.INVITED_NODES This option specifies the addresses from which requests should be accepted. These can be explicit IP addresses or host names. If host names are used, SQL*NET must have a method of resolving them (DNS, hosts file, and so on). Wildcard settings are not supported.

TCP.EXCLUDED_NODES This option specifies the addresses from which requests should not be accepted. These can be explicit IP addresses or node names. If names are used, they must be resolvable by the Oracle environment (using DNS, hosts file, and so on). Wildcard settings are not supported.

By defining the values for these options, you can control which machines have access to the database. There are, however, several important caveats that must be addressed regarding valid node checking:

• The TCP.INVITED_NODES and TCP.EXCLUDED_NODES options are mutually exclusive. Only one may be defined. Do not use both.

• Wildcard values cannot be used. Each individual host name or IP address needs to be specified. Therefore, valid node checking is not a good choice for databases being accessed by a large number of hosts (or by unknown hosts).

• If more sophisticated address verification is required, use Oracle Connection Manager.

If you can deal with these limitations, valid node checking may be a good option.

For example, to instruct the listener to accept requests only from an application server (159.158.212.1) and from the administrative team (Jeaneanne, Nicholas, Gillian, and Murphy), the following lines could be added to the sqlnet.ora file:

tcp.validnode_checking = yes
tcp.invited_nodes = (159.158.216.12, jeaneanne, nicholas,
  gillian, murphy)

This example assumes that each of the administrative team members have host-name aliases that match their names. IP addresses could also have been used.

To enact these changes, the listener must be restarted. Upon restarting, the listener will compare the IP address of incoming requests to the INVITED_NODES list. If the address is not found in the list, the request will be denied.

14.2.4. Using Listener Logging

The listener logging feature creates and maintains a log file showing all listener activity, including incoming requests and administrative commands. Enabling this feature is highly recommended. However, it is also imperative that you monitor the log file on a regular basis for suspicious activity.

For example, any failed password attempt on the listener will produce a TNS-01169 error message in the log file. If a brute force password attack is being used against your listener, the log file will show a high number of TNS-01169 errors. A simple shell script could be used to periodically scan the log file and count these errors. If the count exceeds a certain threshold, the script could shut down the listener and raise an alert.

The log file also shows administrative commands that have been executed on the listener (both authorized and unauthorized). Therefore, you can monitor the log file for unauthorized modifications made to the listener (such as new external process definitions to execute malicious code).

In the following sections, you will learn to enable listener logging using both the lsnrctl program and the EM Database Console application.

14.2.4.1. Enabling Logging Using the lsnrctl Program

Logging can be turned on through the lsnrctl program, or through any of the graphical tools mentioned earlier in the chapter. To turn on logging through lsnrctl, the following parameters can be used:

LOG_DIRECTORY The LOG_DIRECTORY parameter allows you to specify the location where the log file should be created. If this parameter is not set, Oracle will use the default location of $ORACLE_HOME/network/log (on Unix systems) or %ORACLE_HOME% etworklog (on Windows systems).

When using this parameter, make sure that the Oracle process owner has privileges to write files in this location.

LOG_FILE The LOG_FILE parameter allows you to specify the name of the log file. If this parameter is not specified, the name of the listener will be used, with the filename extension .log appended to the end. For example, if your listener uses the default name of listener, the log file name will default to listener.log.

LOG_STATUS The LOG_STATUS parameter controls whether logging is turned on or off. The parameter can be set to the values ON or OFF. When this parameter is set to a value of ON, logging will be enabled.

Changes made to any of these parameters take effect immediately. The listener does not need to be restarted. The following example shows how the parameters can be set:

LSNRCTL> set log_directory /oralogs
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=linux)
   (PORT=1521)))
LISTENER parameter "log_directory" set to /oralogs
The command completed successfully
LSNRCTL> set log_file listener_lnx1.log
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=linux)
   (PORT=1521)))
LISTENER parameter "log_file" set to listener_lnx1.log
The command completed successfully
LSNRCTL> set log_status on
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=linux)
   (PORT=1521)))
LISTENER parameter "log_status" set to ON
The command completed successfully

The steps in this example would enable listener logging to a log file named listener_lnx1.log in the /oralogs directory. Remember that you also need to save the configuration to make the changes persistent.

14.2.4.2. Enabling Listener Logging Using EM Database Control

Listener logging can also be enabled through the EM Database Control application by following these steps:

14.2.5. Removing Unneeded External Procedure Services

The listener can do more than just listen for database requests. It can also be used to run binary executable programs on the database server and to return the output back to the caller. This functionality is referred to as external procedure support.

By definition, an external procedure is a program called by a program that is written in a different language. For example, if a PL/SQL procedure calls a C function stored in a shared library, the C function would be considered an external procedure. Oracle supports external procedures that are written in C and stored in shared library objects or .DLL files.

This functionality was designed to allow command-line executables to be run from within a PL/SQL procedure. In Oracle Applications environments, this technique is commonly used. It is also used in Windows environments to implement Component Object Model (COM) functionality within Oracle.

As you can well guess, external procedure functionality poses a great security risk. Because the external procedures run with the rights of the oracle process owner, the level of damage that an unauthorized user can cause is extremely high.

An intruder may also attempt to modify the listener to define new external procedure services that could be used to execute malicious code.

By default, Oracle creates a service for external procedures in the listener. For the majority of users, this service is never used. Therefore, the service should be removed from the listener.

If your environment requires the use of external procedures, then removing the service entirely is not an option. However, there are other steps that can be taken to decrease your risk. The service can be configured under a separate listener and can be run by a less-powerful operating system account.

In the next sections, you will learn how to remove external procedure services from the listener manually and through the EM Database Control application. You will also learn how to configure external procedure services on a separate listener with greater security measures in place.

14.2.5.1. Manually Removing External Procedure Services

External procedure service definitions can be found in the listener.ora configuration file. The entry name generally will be set to PLSExtProc and the program name will be extproc.

For example, a sample listener.ora file is shown here with the external process definitions underlined:

# listener.ora Network Configuration File:
  /apps/oracle/oracle10gR1/network/admin/listener.ora
# Generated by Oracle configuration tools.


SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /apps/oracle/oracle10gR1)
      (PROGRAM = extproc)
  )
  (SID_DESC =
    (GLOBAL_DBNAME = lnx1)

(ORACLE_HOME = /apps/oracle/oracle10g)
     (SID_NAME = lnx1)
    )
  )

LISTENER =
   (DESCRIPTION_LIST =
      (DESCRIPTION =
        (ADDRESS_LIST =
           (ADDRESS = (PROTOCOL = ICP)(KEY = EXTPROC))
         )
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST =lnx1)
          (PORT = 1521))
         )
       )
   )

As you can see, there is an entry in both sections of the file. The highlighted areas need to be removed from the file using a text editor. Make sure you delete the enclosing parentheses as well.

Whenever you are manually editing the listener.ora file, be very careful that the appropriate parentheses are maintained. Mismatched parentheses can cause unexpected results.

After making the changes, the file contents should look like the following example:

# listener.ora Network Configuration File:
  /apps/oracle/oracle10gR1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = lnx1)
      (ORACLE_HOME = /apps/oracle/oracle10g)
      (SID_NAME = lnx1)
     )
  )

LISTENER =
  (DESCRIPTION_LIST =

(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =lnx1)
      (PORT = 1521))
    )
  )
)

14.2.5.2. Removing External Procedure Services Using EM Database Control

External procedure services can also be removed through the Oracle EM Database Control application by following these steps:

The external process service should now have been removed from the listener.

14.2.6. Creating a Separate Listener for External Procedures

To provide higher security for external procedures, a separate listener can be configured that handles strictly external procedure requests. By separating external procedures from the standard listener functionality, you have more options in terms of securing the listener. One option is to have the new listener executed by a user with very limited operating system privileges. Another is to limit the libraries from which procedures can be executed.

In the following sections, you will learn how these options can be used to secure your environment. You will then learn how to create a new listener incorporating these methods.

14.2.6.1. Executing the Listener with a Restricted Account

The first option is to execute the new listener using an account with restricted operating system privileges. When an external process is called, the listener spawns an extproc agent process to execute the procedure. This process inherits the operating system privileges of the listener process owner. Therefore, by executing this listener under an account with minimal operating system privileges, the potential damage caused by an intruder is drastically reduced.

The user who executes the listener process should not have general access to files owned by the oracle user account. It should not have permission to read or write to database files, nor to the Oracle server address space. It needs to have read access to the listener.ora file and should not have write access to it. This will prevent configuration changes via the SET command in lsnrctl for this listener.

14.2.6.2. Limiting the Available Libraries

Another option is to explicitly define which procedure libraries can be accessed through the listener. By default, the extproc agent can execute procedures from any DLL or shared object library stored in the following locations:

• $ORACLE_HOME/lib directory in Unix

• %ORACLE_HOME%in directory in Windows

However, you can limit the listener to accessing only the libraries explicitly specified in the listener.ora file. This can be done by editing the file manually, or through the EM Database Control application.

Whichever method that is used, the following listener.ora file settings will need to be defined:

PROGRAM The PROGRAM setting specifies the name of the external procedure agent. The external procedure agent is an executable program that will call the external procedure on behalf of the listener. When finished, the agent returns the output from the procedure back to the caller.

Oracle supplies an external procedure agent named extproc, which is used almost exclusively. However, other agent programs can be used as well. The extproc agent program resides in the %ORACLE_HOME%in directory in Windows and in the $ORACLE_HOME/lib directory in Unix.

In Windows environments, it is a requirement that the external procedure agent must reside in the %ORACLE_HOME%in directory. Unix environments do not share this restriction.

ENVS The ENVS setting is used to define any environment variables required by the agent. These variables will be set prior to execution of the agent.

One particular environment variable that can be defined is the EXTPROC_DLLS variable. This environment variable is used to restrict access to DLL and shared library files for the external procedure agent.

If the ENVS setting is not defined, the agent will be able to access any DLL or shared library file in the $ORACLE_HOME/lib directory in Unix or the %ORACLE_HOME%in directory in Windows.

If the ENVS setting is defined, the agent's access will be governed by the rules defined in it. There are several options available for this setting:

Colon-separated list This option allows you to specify specific library files that can be accessed by the agent, in addition to those found in $ORACLE_HOME/lib or %ORACLE_HOME%/bin directories. You must specify the complete path, as well as the library name.

The libraries can be specified as a list, with a colon separating the entries, as in the following example:

/usr/1ib/1ibjava.so:/apps/ora/libsql.so

ONLY directive The ONLY directive allows the agent to access only the specific libraries listed. This means that the agent is denied the default access to the libraries in the $ORACLE_HOME/lib (Unix) and %ORACLE_HOME%in (Windows) directories (except ones that are explicitly specified in this setting).

The ONLY directive offers the highest level of security, as it is the most restrictive.

This setting requires the ONLY directive to precede a colon-separated list of library files. You must also specify the complete path, as well as the library name, as in the following example:

ONLY:/usr/libjava.so:/apps/ora/libsql.so

ANY directive The ANY directive is the least restrictive of the settings. It allows access to any library file.

If you attempt to set the ENVS parameter by manually editing the listener.ora file, it is crucial that the formatting in the file is correct. Remember that the name of the environment variable must be provided, as well as the value. Also, multiple variables can be set, as long as they are separated by a comma. The entire value should be enclosed in double quotes, as in the following example:

(ENVS="EXTPROC_DLLS=ONLY:/usr/libjava.so:/usr/lib/libz.so,
ORACLE_SID=lnx1,PATH=$PATH:/usr/bin:/usr/sql")

In this example, the environment variables EXTPROC_DLLS, ORACLE_SID, and PATH are all defined.

ORACLE_HOME The ORACLE_HOME setting specifies the Oracle home location for the agent program.

SID_NAME The SID_NAME setting specifies a unique system identifier name for the external procedure agent.

14.2.6.3. Creating the Listener Using EM Database Control

The new listener can be created through the EM Database Control application by following these steps:

14.2.6.4. Using the ADMIN_RESTRICTIONS Parameter

The ADMIN_RESTRICTIONS parameter can be set to a value of ON in the listener.ora file. This parameter disallows all SET commands executed through the lsnrctl program or through Oracle's graphical utilities. Therefore, when ADMIN_RESTRICTIONS is enabled, changes to the listener configuration can only be made by manually editing the listener.ora file.

14.2.6.5. Securing the Listener Directory

The directory containing the listener.ora and sqlnet.ora files should be protected so that no user (other than the Oracle administrative account) can read or write files in the directory. This is generally the $ORACLE_HOME/network/admin (Unix) or the %ORACLE_HOME% etworkadmin directory (Windows).

14.2.6.6. Applying Oracle Listener Patches

Always be vigilant about applying security patches as they are released by Oracle. If the listener is susceptible to buffer overflow exploits (or similar attacks that attempt to cause the program itself to fail), the only solution may be a patched version of the program.

14.2.6.7. Blocking SQL*NET Traffic on Firewalls

Internet firewalls should be configured so that they do not allow SQL*NET traffic to pass through, except from known application servers. If the database isn't accessed by an application server, disable all SQL*NET traffic through the router. This ensures that only users on the local network will be able to access the database.