Securing Oracle Database Client Connections

Friday Nov 13th 2009 by James Koopmann

Connection methods to Oracle databases have remained relatively static over the years. Unfortunately, governmental regulations and would-be attackers have not stood still. Join us as we begin a look at securing client connections.

Connection methods to Oracle databases have remained relatively static over the years. Unfortunately, governmental regulations and would-be attackers have not stood still. This, and subsequent articles, will begin to look at how we can help secure client connections.

Before venturing down the path of “Securing Client Connections”, I thought it would be best to first understand the general concepts of client-server connections. This is presented in the Oracle Database 2-Day DBA guide so I’ll be using that for most of my content here. So what does an Oracle network configuration look like?

Firstly, and probably the easiest to explain is, for a client/server connection we must have a client. The client is nothing more than a system or application (for simplistic purposes here) other than the database server that requires a connection to the database server to send or retrieve data. About the only requirement for an external system or application to connect to the database server is that it will need to have some form of client software loaded on it. This software could be Oracle’s client software, java classes, ODBC drivers, etc. Secondly, and just as easy, is the server side. The server component is the Oracle database. The difficulty now becomes defining the various pieces that are required on both the client and server side so that they can communicate properly.

While it is generally, and superficially, stated that “after installing the Oracle Database you will have a fully functional database with a client/server network environment”, this is often an understatement or completely false depending on how someone might have installed, configure, or received errors along the way. For that purpose, configuring the network really isn’t that difficult and generally follows the following steps on a Unix platform (not much different on Windows). These steps, when run on the database server, will configure what is called the Oracle Listener. The Oracle Listener is nothing more than a process on the database server that “listens” for client connection requests. When a connection request is received, the Listener will take control of the request and manage the request to the database server. There are many different ways to define an Oracle Listener. Mostly in conjunction with the connect identifier (easy connect, local, or directory naming).

1.  login as oracle user

2.  set your ORACLE_SID environment variable

export ORACLE_SID=<sid>

3.  startup Oracle's network configuration assistant


4.  make sure the radio button for 'Listener Configuration' is marked, click NEXT

5.  make sure the radio button for 'Add' is marked, click NEXT

6.  give the listener a name, use a unique listener name, LISTENER_MINE for now, click NEXT

7.  Select protocol, TCP, click NEXT

8.  Use standard port, click NEXT

9.  no need to configure another, click NEXT, click NEXT

10.  now make sure the radio button for 'Local Net Service Name configuration' is marked, click NEXT

11.  make sure the radio button for 'Add' is marked, click NEXT

12.  enter in your ORACLE_SID, click NEXT

13.  select the communication protocol, TCP, click NEXT

14.  enter in the host name, use standard port, click NEXT

15.  You can test the connection, might need to change password, when it says Test successful, click NEXT

16.  enter the name of the service name, typically your ORACLE_SID, click NEXT

17.  no need to configure another, click NEXT, click NEXT, click FINISH

Now that the Listener is setup on the database server, we need to somehow make a connection from the client machine. For the purposes of this article, and for the most part within the 2-Day series, connection from the client is performed through Oracle’s SQL*Plus software. Within the 2-Day DBA guide, the easy connection method is described. This doesn’t require much setup on the client machine (no TNSNAMES file) but still requires the user to understand some of the details of the database server. This method only requires the following command line to connect:

sqlplus <user>/<password>@//<machine>:<port>/<service_name>

Where machine is the name or IP address of the Oracle database server, port is the TCP port number the Listener is listening on, and service_name is the service defined on the database. All of these are defined when configuring the Listener from the above steps. While most client applications will use different connection methods, this easy connect will suffice as we continue. Just note that, regardless of the connection method, other connection methods must somehow provide the above credentials and connection parameters.

Now we have, at a very basic level, an understanding of the components of what is considered a client/server environment for an Oracle database; a client connection, a database server, and the Listener to facilitate and handle connection requests.

So why should we concern ourselves with network security? Plainly and simply, the above-mentioned client/server environment is only concerned with user authentication. Once a user has been authenticated and connected to the database, all that remains are the internal database user privileges. Once connection and authentication occur data can easily be selected, modified and transported throughout the world on open networks. It is this ease of access that requires data centers to embrace additional security measures that not only satisfy governmental and internal regulations but also thwart off malicious attempts and safeguard sensitive data.

In subsequent articles, this continued topic of Securing Client Connections will look at encryption of data as it travels through the network as well as how we can secure the network connections for the Oracle database. While the 2-Day Security Guide hints at topics such as enforcing access controls, secure socket layers (SSL), certificate authentication, and monitoring user and Listener activity it often points to other Oracle documentation sets. This sub series will venture out of the 2-Day Security Guide and help present those topics in the 2-Day style so that we can understand and begin to deploy them to our database environments.

» See All Articles by Columnist James Koopmann

Mobile Site | Full Site