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.
- 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) ) ) - 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) ) ) - 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
- 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) ) ) - 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
- 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>
-
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>
- 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.
- 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.
