How to enable SSL Authentication for Oracle Database Client

To setup SSL, refer to steps 1-7 in document “How to enable SSL encryption for Oracle SQL*Net”

http://www.dbaglobe.com/2014/12/how-to-enable-ssl-encryption-for-oracle.html

1) Configure the listeners.ora (server) & tnsnames.ora (server & client)

 

SSL_CLIENT_AUTHENTICATION = TRUE

 

SSL_CLIENT_AUTHENTICATION = TRUE

 

2) Configuring the database

 

The database parameter OS_AUTHENT_PREFIX must be null and REMOTE_OS_AUTHENT must be FALSE.

SQL> select value from v$parameter where name='remote_os_authent';

 

VALUE

--------------------------------------------------------------------------------

FALSE

SQL> select value from v$parameter where name='os_authent_prefix';

 

VALUE

--------------------------------------------------------------------------------

ops$

 

SQL> alter system set os_authent_prefix='' scope=spfile;

 

System altered.

 

SQL> startup force

 

The user within the database has to be created specifying the distiguished name (DN) on their certificate. For example,

SQL> create user solaris112_user identified externally as 'CN=solaris112.dbaglobe.com';

 

User created.

 

SQL>  grant create session to solaris112_user;

 

Grant succeeded.

 

SQL>  grant select on v$session_connect_info to solaris112_user;

 grant select on v$session_connect_info to solaris112_user

                 *

ERROR at line 1:

ORA-02030: can only select from fixed tables/views

 

 

SQL> grant select on v_$session_connect_info to solaris112_user;

 

Grant succeeded.

 

3) Connect as the TCPS authenticated user

oracle@solaris112:~$ sqlplus /@orcl1_tcps

 

SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 15 09:12:31 2014

 

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

 

SQL> show user

USER is "SOLARIS112_USER"

 

SQL> select sys_context('userenv','network_protocol') from dual;

 

SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')

--------------------------------------------------------------------------------

tcps

 

SQL> select userenv('sid') from dual;

 

USERENV('SID')

--------------

            36

 

SQL> set pages 999

 

SQL> select * from v$session_connect_info where sid=36;

 

       SID    SERIAL# AUTHENTICATION_TYPE        OSUSER

---------- ---------- -------------------------- ------------------------------

NETWORK_SERVICE_BANNER

--------------------------------------------------------------------------------

CLIENT_CHARSET                           CLIENT_CONNEC

---------------------------------------- -------------

CLIENT_OCI_LIBRARY          CLIENT_VERSION

--------------------------- ----------------------------------------

CLIENT_DRIVER                  CLIENT_LOBATTR          CLIENT_REGID     CON_ID

------------------------------ ----------------------- ------------ ----------

        36      63563 GLOBAL                     CN=solaris112.dbaglobe.com

 

US7ASCII                                 Homogeneous

Home-based                  12.1.0.2.0

SQL*PLUS                       Client Temp Lob Rfc On             0          0

 

        36      63563 GLOBAL                     CN=solaris112.dbaglobe.com

Authentication service for Solaris: Version 12.1.0.2.0 - Production

US7ASCII                                 Homogeneous

Home-based                  12.1.0.2.0

SQL*PLUS                       Client Temp Lob Rfc On             0          0

 

        36      63563 GLOBAL                     CN=solaris112.dbaglobe.com

Encryption service for Solaris: Version 12.1.0.2.0 - Production

US7ASCII                                 Homogeneous

Home-based                  12.1.0.2.0

SQL*PLUS                       Client Temp Lob Rfc On             0          0

 

        36      63563 GLOBAL                     CN=solaris112.dbaglobe.com

Crypto-checksumming service for Solaris: Version 12.1.0.2.0 - Production

US7ASCII                                 Homogeneous

Home-based                  12.1.0.2.0

SQL*PLUS                       Client Temp Lob Rfc On             0          0