During an EUS authentication, there are 2 communication channels: one between the sql client and the database, and another one between the database and the LDAP server. In a previous post, I explained that the database-to-OUD communication can be authenticated either through user/password or SSL.

The sql client-to-database connection also supports multiple authentication methods:

• user/password
• SSL
• Kerberos (described in this post)

In this post, I will explain how to configure SSL authentication. Note that SSL authentication is usually used in conjunction with DB-to-OUD SSL authentication. This post assumes that DB-to-OUD SSL authentication has already been set up as described there and that the database already has a wallet containing the DB certificate.

Using sqlplus, it is possible to connect to the DB using a client certificate instead of username/password. In order to do this, the DB must be configured with an SSL listener, and the sqlplus client must be configured to use a client certificate.

1. Configure the database to allow SSL authentication:
   The file sqlnet.ora located in $ORACLE_HOME/network/admin must be modified to look like the following:

   NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
     
   ADR_BASE = <$ORACLE_BASE>
   
   #ADDED FOR SSL 
   SQLNET.AUTHENTICATION_SERVICES= (TCPS, BEQ)
   SSL_CLIENT_AUTHENTICATION = TRUE
   WALLET_LOCATION = 
     (SOURCE =
       (METHOD = FILE)
       (METHOD_DATA =
         (DIRECTORY = <$ORACLE_BASE>/admin/<$ORACLE_SID>/wallet)
       )
     )

2. Configure a SSL listener on the database:
   The listeners are defined in the configuration file listener.ora, located in $ORACLE_HOME/network/admin. The content of this file defines the port and protocol used by the listener, and the location of the wallet that will store the database certificate.

   LISTENER =
     (DESCRIPTION_LIST =
       (DESCRIPTION =
         (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
         (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname>)(PORT = 1521))
   #ADDED FOR SSL
         (ADDRESS = (PROTOCOL = TCPS)(HOST = <hostname>)(PORT = 1575))
       )
     )
   
   ADR_BASE_LISTENER = <$ORACLE_BASE>
   
   #ADDED FOR SSL
   WALLET_LOCATION = (SOURCE=
     (METHOD = FILE)
       (METHOD_DATA =
         (DIRECTORY=<$ORACLE_BASE>/admin/<$ORACLE_SID>/wallet)
       )
     )
   

3. Restart the listener:
   the modification of listeners.ora requires to restart the database listener with the following commands

   $ $ORACLE_HOME/bin/lsnrctl stop
   $ $ORACLE_HOME/bin/lsnrctl start

4. Configure the sql client to use SSL authentication:

   The sql client reads its configuration from sqlnet.ora and tnsnames.ora. By default, those files are located in the same location as the DB configuration files ($ORACLE_HOME/network/admin), which can cause issues because the client and the server use a different wallet. In order to avoid configuration issues, it is recommended to have separate configuration files. To do this, the environment variable TNS_ADMIN is used to configure the path containing the client configuration files:

   $ setenv TNS_ADMIN /path/to/files
   

   or

   $ export TNS_ADMIN=/path/to/files
   

   Then the file tnsnames.ora will define how to reach the database using SSL:

   $ cat /path/to/files/tnsnames.ora 
   ORCL11G =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCPS)(HOST = <hostname>)(PORT = 1575))
       (CONNECT_DATA =
         (SERVER = DEDICATED)
         (SERVICE_NAME = orcl11g)
       )
     )
   

   And finally the file sqlnet.ora defines the SSL configuration (wallet location…):

   $ cat sqlnet.ora 
   NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
   SQLNET.AUTHENTICATION_SERVICES = (TCPS,BEQ)
   SSL_CLIENT_AUTHENTICATION = TRUE 
   WALLET_LOCATION =
     (SOURCE =
       (METHOD = FILE)
       (METHOD_DATA =
         (DIRECTORY = /path/to/client/wallet)
       )
     )

5. Create the client wallet and the client certificate:
   The client wallet will store the client certificate as well as the certificates trusted by the client (i.e. the server certificate).
   The client certificate must contain a DN that corresponds to a LDAP entry on OUD server. The following commands create a client wallet, then add a self-signed certificate to the wallet for the user cn=Joe, and finally export the self-signed certificate to a file.

   $ orapki wallet create -wallet /path/to/client/wallet -pwd <password> -auto_login
   

   $ orapki wallet add -wallet /path/to/client/wallet -dn cn=Joe,ou=users,dc=example,dc=com -keysize 1024 -self_signed -validity 365 -pwd <password>
   

   $ orapki wallet export -wallet /path/to/client/wallet -dn cn=Joe,ou=users,dc=example,dc=com -cert joe-cert.txt
   

6. Add the client certificate to the DB wallet:
   the database must trust the client certificate in order to accept SSL connections. As the above steps created a self-signed user certificate, the client certificate has to be imported as a trusted cert in the DB wallet.

   $ orapki wallet add -wallet <PathToDBWallet> -cert joe-cert.txt -trusted_cert -pwd <password>
   

7. Add the DB certificate to the client wallet:
   the client must trust the DB certificate. If the DB certificate is a self-signed certificate, this means that the DB cert must be added to the client wallet.

   $ orapki wallet add -wallet /path/to/client/wallet -cert db-cert.txt -trusted_cert -pwd <password>

8. Allow EUS to accept SSL connections between the client and the database:

    log in to Enterprise Manager: https://<hostname>:1158/em

   In the “Server” tab, select “Enterprise User Security” in the “Security” section, then “Manage Enterprise Domain” and click on “Configure”. In the “Configuration” tab, select the authentication methods that you want to allow between the client and the DB: Password, SSL, Kerberos.

9. Test the SSL connection between the client and the database:

   The following command reads the sql configuration files, extracts the path to the client wallet, then connects to the DB using the certificate found in the client wallet:

   $ export TNS_ADMIN=/path/to/files
   $ sqlplus /@orcl11g
   
   SQL*Plus: Release 11.2.0.2.0 Production on Wed Mar 28 11:18:57 2012
   
   Copyright (c) 1982, 2010, Oracle.  All rights reserved.
   
   
   Connected to:
   Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
   With the Partitioning, OLAP, Data Mining and Real Application Testing options
   
   SQL>

Once authenticated with the client certificate, the enterprise user will be mapped to a schema exactly as a user/password authenticated user. EUS will also find the groups for the LDAP user and grant the corresponding roles to the EUS user.

During an EUS authentication, Oracle Database connects to OUD server using a simple bind over SSL. The username and the password are stored in the database wallet (default location is $ORACLE_BASE /admin/<ORACLE_SID>/wallet), and can be read using the mkstore command:

$ $ORACLE_HOME/bin/mkstore -wrl $ORACLE_BASE/admin/$ORACLE_SID/wallet -viewEntry ORACLE.SECURITY.DN
Oracle Secret Store Tool : Version 11.2.0.2.0 - Production
Copyright (c) 2004, 2010, Oracle and/or its affiliates. All rights reserved.

Enter wallet password: 
 
ORACLE.SECURITY.DN = cn=orcl11g,cn=OracleContext,dc=example,dc=com
$ $ORACLE_HOME/bin/mkstore -wrl $ORACLE_BASE/admin/$ORACLE_SID/wallet -viewEntry ORACLE.SECURITY.PASSWORD
Oracle Secret Store Tool : Version 11.2.0.2.0 - Production
Copyright (c) 2004, 2010, Oracle and/or its affiliates. All rights reserved.

Enter wallet password: 
 
ORACLE.SECURITY.PASSWORD = rIwfee96
$

This behavior can be changed, and the Database can switch to certificate authentication over SSL. In order to do this:

1. Create a certificate for the Database

   For testing purpose, it is possible to create a self-signed certificate using the orapki utility (located in $ORACLE_HOME/bin).

   $ orapki wallet add -wallet <PathToDBWallet> -dn cn=<ORACLE_SID>,cn=oraclecontext,dc=example,dc=com -keysize 1024 -self_signed -validity 365 -pwd <WalletPassword>
   

   The DB certificate must then be exported to a file:

   $ orapki wallet export -wallet <PathToDBWallet> -dn cn=<ORACLE_SID>,cn=oraclecontext,dc=example,dc=com -cert db-cert.txt
   

2. Add the DB certificate to OUD truststore

   By default, OUD installed with EUS option configures SSL and a JKS truststore. The trusted certificates must be imported into <OUD_INSTANCE>/config/truststore using /usr/bin/keytool utility:

   $ keytool -importcert -alias db-cert  -file db-cert.txt -keypass <value in keystore.pin> -keystore <OUD_INSTANCE>/config/truststore -storepass <value in keystore.pin>
   

   OUD must be stopped and restarted for the truststore to be re-read.

3. Add OUD certificate to the DB wallet

   By default, OUD uses a self-signed certificate that must be added to the DB truststore. You first need to export the certificate using keytool:

   $ keytool -exportcert  -alias server-cert -keystore <OUD_INSTANCE>/config/keystore -storepass <value in keystore.pin> -file oud-cert.txt
   

   Then the certificate must be imported in the DB wallet using orapki:

   $ orapki wallet add -wallet <PathToDBWallet> -cert oud-cert.txt -trusted_cert -pwd <WalletPassword>
   

4. Configure the DB to use certificate authentication instead of password authentication.

   $ sqlplus sys as sysdba
   
   SQL*Plus: Release 11.2.0.2.0 Production on Thu Feb 4 10:55:59 2016
   
   Copyright (c) 1982, 2010, Oracle. All rights reserved.
   
   Enter password: 
   
   Connected to:
   Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
   With the Partitioning, OLAP, Data Mining and Real Application Testing options
   
   SQL> ALTER SYSTEM SET LDAP_DIRECTORY_ACCESS = SSL;
   
   System altered.
   
   SQL>
   

   The possible values for LDAP_DIRECTORY_ACCESS are NONE, PASSWORD or SSL, and govern the authentication method between the Database and OUD server.

Starting with JDK 7u75 release, the SSLv3 protocol (Secure Socket Layer) has been deactivated and is not available by default. If your OUD server is running with JDK 7u75 or higher, you may experience issues with EUS when trying to authenticate:

ORA-28030: Server encountered problems accessing LDAP directory service

And OUD access log will display the following error:

[08/Jan/2016:10:43:13 +0100] CONNECT conn=26 from=10.166.139.54:48916 to=10.166.139.54:1636 protocol=LDAPS
[08/Jan/2016:10:43:13 +0100] DISCONNECT conn=26 reason="I/O Error" msg="Client requested protocol SSLv3 not enabled or not supported"

The proper method to fix this issue is to apply patch 19285025 on the database, which will fix the LDAP library used to perform the connection between the database and OUD and use another algorithm.

A quick workaround is to edit the file $JRE_HOME/lib/security/java.security and remove “SSLv3” from the line defining jdk.tls.disabledAlgorithms (on the machine where OUD runs, for the java version used by OUD), then stop and restart OUD. This will allow OUD to use SSLv3. Note that this workaround should not be applied in production as SSLv3 is obsolete and should not be used anymore. The correct fix is to patch the database.