access_timeSeptember 22, 2015
person_outlineMartin Rakhmanov
share
In this article, I will explain how to set up an encrypted communications channel in Oracle Database. This is the third in a series of blog posts I've published about encryption as it relates to databases. Previously, I covered encrypting network communications between the client and the database server and data at rest.
Oracle Database allows you to configure encryption of client-server channel in two ways: using native encryption and using SSL. Let's examine how to configure and debug each of them for an OCI client application, more specifically for <code>sqlplus</code>. This assumes there is no Oracle software installed on the client machine initially.
Download sqlplus from http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html
Select a platform
Grab "Instant Client Package - Basic" and "Instant Client Package - SQL*Plus" archives
Unpack them into one directory (e.g. <code>G:\instantclient_12_1</code>)
Please note that in this post Oracle Database 12c Enterprise Edition is used on the server.
First, create the <code>sqlnet.ora</code> configuration file on the client, if one does not exist already. You can use tools like Process Monitor to locate the file:
For our <code>sqlplus</code> example it will be:
<code>G:\instantclient_12_1\network\admin\sqlnet.ora</code>
Next the file should be updated with encryption-specific information:
This will result in communications with the server using encryption and check-summing. Confirm this by examining network capture before and after <code>sqlnet.ora</code> change and by reading Oracle Client Trace file:
To examine the trace, we need to prepare a directory for it. Otherwise it will go to inconvenient locations (i.e., nested deep in <code>C:\WINDOWS\)</code>. So create this directory under <code>sqlplus</code> location:
<code>log\diag\clients</code>
And add this line to the <code>sqlnet.ora</code>:
Now after running <code>sqlplus</code>, look for <code>*.trc</code> files under the trace directory nested under the <code>log\diag\clients</code> directory created above. Although you can easily explore <code>*.trc</code> files in a text editor, Oracle provides a tool called <code>trcasst</code> that produces more readable output from trace files:
<code>trcasst ora_6100_6316.trc > ora_6100_6316.txt</code>
Here's a snippet from <code>ora_6100_6316.txt</code>:
For debugging encryption, however, examining <code>trc</code> files is better since more encryption-specific information is available within them rather than in the <code>trcasst</code> processed output.
SSL communication is configured in Oracle by creating two wallets (client and server) that will store certificates and modifying <code>sqlnet.ora</code>, <code>tnsnames.ora</code> and <code>listener.ora</code> configuration files.
Using the orapki utility, the process is relatively easy. See Document 401251.1 (Configuring SSL for Client Authentication and Encryption With Self Signed Certificates On Both Ends Using orapki) on Oracle's support website for details of each <code>orapki</code> option. Here I'll just post step-by-step instructions.
Setting up SSL requires certificates to be signed by a trusted certificate authority (CA). For development and testing purposes it is convenient to use self-signed certificates. Next we will configure the server and the client separately using a self-signed certificate.
Create a server wallet with <code>autologin</code> property turned on:
After this command is executed (it will prompt you for a password - choose a good one) a directory named <code>db_wallet</code> will be created along with the following files:
<code>cwallet.sso*</code> files are only created when the <code>auto_login</code> option is used and contains an "obfuscated copy of the wallet" per Oracle documentation.
Now add a self-signed certificate to the wallet and export it for subsequent import on the client:
Examine the wallet contents:
Notice the <code>Trusted Certificates</code> part - it has the <code>root_test_CA</code> certificate now. Let's export it to a file:
Our next step is to create the server certificate using the <code>orapki</code> utility, which will be presented to a client during SSL handshake phase. However for a simple test, one may use self-signed root certificate as a server certificate so I'll skip this step.
Now it's time to update the <code>sqlnet.ora</code> file with the server wallet location:
And add this line since we are not going to use client authentication:
Finally, to configure the server for SSL we must add <code>TCPS</code> entry to the <code>listener.ora</code> file:
Then add a link to the server wallet (yes, the same entry as in <code>sqlnet.ora</code> file):
Restart the listener after these changes or reload its configuration.
In our example we do not have <code>orapki</code> on the client machine, so client wallet creation and trusted certificate import could be done on the server, and then the wallet files should be copied to the client.
Creating the client wallet:
Import the root CA certificate:
This will allow the client to validate the server's certificate since now both client and server have one trusted certificate. Copy contents of the <code>client_wallet</code> directory to the client and update the client's <code>sqlnet.ora</code> to point to the wallet files just copied:
Now, to instruct our OCI application (<code>sqlplus</code>) to use SSL we must create an entry (alias) in the <code>tnsnames.ora</code> file on the client that will resolve the connection identifier so that <code>TCPS</code> protocol is used instead of <code>TCP</code>. Create a file named <code>tnsnames.ora</code> under <code>network\admin\</code> with content like the following:
Make sure that all round brackets are in place: if you type it in manually with typos, the connection attempt will fail with errors like "ORA-12154: TNS:could not resolve the connect identifier specified". That is why sometimes using a GUI tool is beneficial :).
Now try to connect:
If the connection fails, examining the trace file corresponding to this failure (see <code>log\diag\clients\user_...\host_..\trace</code>) is very helpful. Search for the words "SSL" and "error."
To verify that we are connected via SSL, run this query in <code>sqlplus</code>:
Notice you can perform additional SSL configuration on the client: selecting which algorithms to use for authentication, enabling encryption and data integrity checks, selecting which SSL version to use and some others. For example, this <code>sqlnet.ora</code> fragment instructs the client to use version <code>3.0</code> of SSL and use <code>SSL_RSA_WITH_DES_CBC_SHA</code> cipher suite:
Note that both the client and the server must support the SSL version and cipher suites specified for the connection to succeed. Again, the best source to verify what setting has been used is the client and server trace.
Examine <code>SSL_VERSION</code> value in <code>sqlnet.ora</code> file: if set, it must be <code>3.0</code> or <code>1.0</code> or <code>1.1</code> or <code>1.2</code> or certain combinations of these values (see https://docs.oracle.com/cd/E11882_01/network.112/e10835/sqlnet.htm#NETRF235 for details).
If the client trace file contains lines like these:
Examine <code>SSL_VERSION</code> value in <code>sqlnet.ora</code> on both the client and server: if set, there should be a match.
If client trace file contains lines like these:
Examine <code>SSL_CLIENT_AUTHENTICATION</code> setting in the server <code>sqlnet.ora</code> file: it must be explicitly set to <code>FALSE</code> for SSL configuration without client authentication (our example with <code>sqlplus</code>).
OCI client cannot open the wallet file. Check the syntax in the <code>sqlnet.ora</code> file and then the actual wallet files. The trace contains lines like this:
Make sure <code>tnsnames.ora</code> on the client has the correct alias for the SSL database.
There are many other possible errors, and here we only listed some of the most common. Again, examining the trace file coupled with some filesystem/network monitoring is usually sufficient to resolve the problem.
Trustwave database scanning products contain a variety of Oracle configuration checks that help ensure encryption is configured properly.
------------恢复内容结束------------