Securing Oracle Database 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

netca

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

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles