Lock IT Down: Tighten Oracle security via SSL secure logins

Establish secure connections in Oracle

While plenty of attention has been focused on network security, what are you suggesting that your clients do to secure their Oracle database servers?

Most Oracle users still log in to the database using a simple username and password, both of which are transmitted in plain text over the network. In other companies, employees might rely on the underlying operating system's security, trusting that if someone is able to connect to the network, he or she must be a legitimate user.

Since Oracle version 8.1.6 (8i Release 2), however, more secure options have been available. With Oracle Advanced Security (OAS), an add-on option to the Enterprise Edition, companies can secure all network traffic to and from their Oracle servers using Secure Sockets Layer (SSL). This is the same network protocol that Web servers use to protect e-commerce transactions—including credit card numbers.

Enabling SSL logins comes at a price, though: The SSL protocol isn’t as fast as unencrypted TCP/IP. Other issues to be aware of include:
  • SSL traffic cannot pass through most application proxy firewalls. This makes SSL a solution for within the client's internal networks, not a solution for connecting outside users.
  • SSL doesn’t work with versions of Oracle earlier than 8i. Clients still using version 7.3 or 8.0 won’t be able to use this feature.
  • Configuration is more challenging, requiring the creation of individual user certificates and additional Net8 setup.

I'll show you how you can use SSL and X.509 certificates to beef up the login process on your clients' Oracle databases. Proposing such an implementation could mean not only a well-paying project for you, but better sleep for your clients.

As with any change to your production system architecture, you should prototype SSL authentication using test servers and clients before implementing it.

First, some terminology
You're likely to encounter the following terms when researching SSL-based Oracle logins for your clients:
  • Encryption: Data that is scrambled so that it can be read only by the sender and receiver is encrypted. An encryption formula, or algorithm, uses one or more keys—strings of data—as raw material to scramble the data and later turn it back into readable text (decryption).
  • Authentication: Logging in to a database requires you to prove that you are the person you say you are. Providing such proof is called authentication.
  • Public key infrastructure (PKI): PKI is a method of exchanging keys for use in encryption. Each person using PKI is given two keys: a private one known only to the user, and a public one that can be freely given out to others. Information encrypted with the private key can only be decrypted with the public one, and vice versa.
  • Certificate: The user's public key, along with other identifying information, can be contained in a file called a certificate. The certificate is presented to network servers to identify the user. ANSI standard X.509 describes the format of certificates, so they are often called X.509 certificates.
  • Certificate authority (CA): CAs are third parties that are trusted to issue certificates, much as a state's Department of Motor Vehicles issues driver's licenses. Certificates are signed electronically by the CA's own private key and thus can be verified as authentic by checking against the CA’s public key. Client software—like Web browsers and Oracle's Net8 client—already have the public keys for several popular CAs loaded into them. Public keys for additional CAs can be added to the client, enabling the client to validate certificates generated by those CAs as well.
  • Wallet: A wallet is a file that contains the user's private key and certificates. The wallet is protected by a password. To log in, a user must physically possess a wallet.

Step one: Obtain and install certificates
Each user who will be using SSL—including the administrator who will do the DBA login used to start the database—must obtain a certificate from a trusted Certification Authority such as VeriSign, RSA, or GTE CyberTrust and install the certificate in a wallet file. Prices for certificates can range from several hundred to tens of thousands of dollars, depending on the number of users in an organization.

To do this, log in to the network and run Oracle Wallet Manager (OWM) (Type owm in UNIX or, from the Windows Start menu, navigate to Programs | Oracle | Network Administration | Wallet Manager).

From the Wallet menu, select New to create a wallet file on your computer. OWM will prompt you to create a directory in which the wallet file will be stored. Answer Yes. You'll also be prompted for the wallet password: Enter it twice in the boxes provided. Next, OWM will ask if you want to create a certificate request (see  Figure A).

Figure A
Oracle Wallet Manager

Answer Yes and fill out the form with this information:
  • Common name (required): Enter the login name of the user.
  • Organizational unit (optional): Enter the department or division in which the user works.
  • Organization (optional): Enter the company name.
  • Locality/City (optional): Generally, you’ll leave this blank.
  • State/Province (optional): Generally, you’ll leave this blank.
  • Country (required): Select from the drop-down list.

Click OK, and the certificate request will be created. The certificate request must be sent to one of the certificate authorities listed above. You can cut and paste the request, which consists of several lines of random letters and numbers sandwiched between comment lines, or export it to a file for e-mailing by choosing Export Certificate Request from the Operations menu.

What you get back is a similar set of text lines, which is your encrypted certificate. Use Import User Certificate from the Operations menu in OWM to load the certificate into your wallet. To see the details of the certificate, click on its entry in the Navigator pane of OWM; the Details pane will display the information.

Step two: Set up Secure Sockets Layer (SSL)
Oracle's SSL protocol and certificate management tools are installed with Oracle Advanced Security (OAS). OAS is compatible only with the Enterprise Edition of Oracle Server, not the less expensive Standard Edition, and is installed, like all Oracle products, via the Universal Installer.

During either a Server or Client install, choose the Custom option and select Oracle Advanced Security from the available products list. Or, you can add it later using the same program.

Once installed, you must configure Net8 to use SSL. Each end of the communication, server and client, must be set up separately.

To configure the client side
  1. Start Net8 Assistant (use the netasst command in UNIX or, from the Start menu in Windows, choose Programs | Oracle | Network Administration | Net8 Assistant).
  2. Click twice on Local in the Navigator window, and then click on Profile.
  3. From the drop-down list, select Oracle Advanced Security, and then click on the SSL tab.
  4. Select Configure SSL For Client and enter the pathname of the directory that contains the client's wallet file.
  5. From the File menu, select Save Network Configuration.
  6. In the Navigator, click on Service Naming, and then click on the green plus sign (+) to create a new Net Service Name.
  7. Complete the Net Service Name Wizard by entering a Net Service Name (such as prod_ssl), the protocol (TCP/IP with SSL), hostname and port (Oracle recommends 2484), and the name of the database to which you'll be connecting (e.g., PROD.MYCOMPANY.COM). Click Finish.
  8. From the File menu, select Save Network Configuration.

To configure the server side
The server is configured similarly, except that Configure SSL For Server is selected in Net8 Assistant (see  Figure B).

Figure B
Oracle Advanced Security

After you configure SSL for the server, configure a Net8 Listener using SSL. Again, in Net8 Assistant:
  1. Double-click Local, and then click on Listeners.
  2. Click the green plus sign (+) to configure a new listener.
  3. Name the listener LISTENER_SSL or something similar.
  4. Click on the Add Address button to add a listening address.
  5. Select SSL from the Protocol drop-down list and enter the hostname and port as above.
  6. From the File menu, select Save Network Configuration.

To activate the SSL listener, type lsnrctl start listener_ssl (or whatever name you chose) at the command prompt on the server.

Step three: Create global users in the database
The easy part of this whole process is adding global users to the database. Log in to Oracle SQL*Plus as a user with the CREATE USER privilege. For each user who will connect via SSL, create a user account by typing:
CREATE USER username IDENTIFIED GLOBALLY AS 'distinguishedname'
   DEFAULT TABLESPACE <tablespacename>
   TEMPORARY TABLESPACE <tablespacename>;

Grant whatever individual privileges and roles you wish to that user, and you're ready to go.

Step four: Login using SSL
To connect to an Oracle database using SSL, first open your electronic wallet with the Enterprise Login Assistant, an Oracle program installed with OAS. Enterprise Login Assistant has a picture of a traffic signal and menu commands for opening the wallet or closing it (see  Figure C).

Figure C
Enterprise Login Assistant

If the green signal light is on in the picture, the wallet is open; if the red light is on, the wallet is closed.

To open the wallet, pull down the AutoLogin menu and choose Login. Supply the wallet password when prompted and click OK. The wallet is now open, and applications can access the certificates contained in the wallet. Closing the wallet is just as easy: pull down AutoLogin again and choose Logout. You’ll be warned that applications won’t be able to use your certificates and asked if you want to proceed. Click Yes to close the wallet or No to leave the wallet open. (You can also use this tool to change your wallet's password.)

Once the wallet is open, you can connect to Oracle using any client program, such as SQL*Plus. The connect string—the combination of username, password, and Net Service Name that is used to request a connection to a specific database—must reference the Net Service Name created earlier but does not need to include a username or password. The certificate takes care of that. For example, to log in to SQL*Plus using the Net Service Name defined above, type:

The slash (/) normally separates the username and password; however, this information isn’t needed when authenticating via certificate. The At sign (@) separates the nonexistent login information from the Net Service Name, in this case, prod_ssl. The user is then logged in and has all the privileges associated with the global login, which matches his or her certificate.

Bottom line
With SSL configured on your client’s system, all traffic between users and databases will be unreadable to snooping third parties. As a bonus, users can log in the standard way with username and password: The encryption will still be active. SSL is also a prerequisite for creating Enterprise users (those managed centrally in a directory service like Microsoft Active Directory).

By being up to date on the security options available in recent versions of Oracle, you can do a better job advising your clients.

For more information, see the Oracle Advanced Security Administrator's Guide, chapter 10. (You’ll need to register on Oracle’s Web site to read this.) If you have access to Oracle's customer-only support Web site, Metalink, you can access document ID 112490.1, "Configuring Net8 TCP/IP via SSL," for step-by-step details.

Editor's Picks