The Fei's Tech Notes: PKI

Thursday, September 5, 2013

Oracle Database 11g: JDBC Connection Authentication Using Certificate

Please finish instructions 1-3.1 in Oracle 11g: SQLNet Authentication Using PKI

Create location to hold jar files needed, i.e. /home/oracle/SSL
$ mkdir -p /home/oracle/SSL
Copy the jar files needed to test the connection.
$ cd $ORACLE_HOME/jlib
$ cp ojpse.jar oraclepki.jar /home/oracle/SSL/
$ cp osdt_cert.jar osdt_core.jar /home/oracle/SSL/
$ cp $ORACLE_HOME/jdbc/lib/ojdbc6.jar /home/oracle/SSL/
Create DbSSLTester.java with below code:

    import java.security.Security;

    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.Statement;

    public class DbSSLTester

    {
    public static void main(String[] args)
    throws Exception
    {
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
    Security.insertProviderAt(new oracle.security.pki.OraclePKIProvider(), 3);
    String url = "jdbc:oracle:thin:@(DESCRIPTION = " +
    "(ADDRESS_LIST = " +
    "(ADDRESS = " +
    "(PROTOCOL = TCPS)" +
    "(HOST = suzhou.goweekend.ca)" +
    "(PORT = 2484)" +
    ")" +
    ") " +
    "(CONNECT_DATA = (SERVICE_NAME = GUSU))" +
    ")";

    java.util.Properties info = new java.util.Properties();
    info.setProperty("oracle.net.authentication_services", "(TCPS)");
    info.setProperty("javax.net.ssl.trustStore", "/app/oracle/product/11.2.0.3/owm/wallets/fei/cwallet.sso");
    info.setProperty("javax.net.ssl.trustStoreType", "SSO");
    info.setProperty("javax.net.ssl.keyStore", "/app/oracle/product/11.2.0.3/owm/wallets/fei/cwallet.sso");
    info.setProperty("javax.net.ssl.keyStoreType", "SSO");

    Connection conn = DriverManager.getConnection(url, info);
    Statement stmt = conn.createStatement();
    ResultSet rset = stmt.executeQuery("select user from dual");

    while (rset.next())
    System.out.println(rset.getString(1));

    rset.close();
    stmt.close();
    conn.close();
    }
    }

$ export CLASSPATH=$CLASSPSTH:/home/oracle/SSL/ojpse.jar:/home/oracle/SSL/oraclepki.jar:/home/oracle/SSL/osdt_cert.jar:/home/oracle/SSL/osdt_core.jar:/home/oracle/SSL/ojdbc6.jar
$ export PATH=/opt/jdk1.6.0_38/bin:$PATH
$ javac DbSSLTester.java
$java DbSSLTester
FEI

Oracle Database 11g: SQLNet Authentication Using PKI

Secure connection between Oracle Server and Client

1. Create the server and client wallets

1.1 Server

Create wallet location
$ mkdir /app/oracle/product/11.2.0.3/owm/wallets/server
Initiate wallet
$ orapki wallet create -wallet /app/oracle/product/11.2.0.3/owm/wallets/server -auto_login -pwd think4me
Create self-signed certificate
$ orapki wallet add -wallet /app/oracle/product/11.2.0.3/owm/wallets/server -dn 'CN=server' -keysize 2048 -self_signed -validity 3650 -pwd think4me
Export server root certificate
$ orapki wallet export -wallet /app/oracle/product/11.2.0.3/owm/wallets/server -dn 'CN=server' -cert /app/oracle/product/11.2.0.3/owm/wallets/server/server_ca.cert

1.2 Client

Create wallet location
$ mkdir /app/oracle/product/11.2.0.3/owm/wallets/fei
Initiate wallet
$ orapki wallet create -wallet /app/oracle/product/11.2.0.3/owm/wallets/fei -auto_login -pwd think4me
Create self-signed certificate
$ orapki wallet add -wallet /app/oracle/product/11.2.0.3/owm/wallets/fei -dn 'CN=fei' -keysize 2048 -self_signed -validity 3650 -pwd think4me
Export root certificate
$ orapki wallet export -wallet /app/oracle/product/11.2.0.3/owm/wallets/fei -dn 'CN=fei' -cert /app/oracle/product/11.2.0.3/owm/wallets/fei/client_fei_ca.cert

2. Exchange the server and client root certificates

2.1 Import client root certificate into server wallet

$ orapki wallet add -wallet /app/oracle/product/11.2.0.3/owm/wallets/server -trusted_cert -cert /app/oracle/product/11.2.0.3/owm/wallets/fei/client_fei_ca.cert -pwd Welcome1

2.2 Import server root certificate into client wallet

$ orapki wallet add -wallet /app/oracle/product/11.2.0.3/owm/wallets/fei -cert /app/oracle/product/11.2.0.3/owm/wallets/server/server_ca.cert -pwd Welcome2

3. Configure TCPS on the server and client

3.1 Server

listener.ora

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = weekend.db.goweekend.ca)
      (ORACLE_HOME = /app/oracle/product/11.2.0.3)
      (SID_NAME = weekend)
    )
)
SSL_CLIENT_AUTHENTICATION = TRUE
WALLET_LOCATION =
(SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /app/oracle/product/11.2.0.3/owm/wallets/server)
    )
)
LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = db.goweekend.ca)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCPS)(HOST = db.goweekend.ca)(PORT = 2484))
    )
)

sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS)
SSL_VERSION = 0
NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME)
SSL_CLIENT_AUTHENTICATION = TRUE
WALLET_LOCATION =
(SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /app/oracle/product/11.2.0.3/owm/wallets/server)
    )
)

3.2 Client

sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES= (TCPS)
SSL_VERSION = 0
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
SSL_SERVER_DN_MATCH = Yes
SSL_CLIENT_AUTHENTICATION = TRUE
WALLET_LOCATION =
(SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /opt/app/oracle/product/11.2.0.2/owm/wallets/fei)
    )
)

tnsnames.ora

WEEKEND =
(DESCRIPTION =
   (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = tcps)(HOST = db.goweekend.ca)(PORT = 2484))
   )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = weekend)
    )
    (SECURITY =
      (SSL_SERVER_CERT_DN="CN=server"))
)

4. Configure database and user

4.1 OS_AUTHENT_PREFIX and REMOTE_OS_AUTHENT

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

SQL> show parameter remote_os_authent
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_os_authent                    boolean     FALSE
SQL> show parameter os_authent_prefix
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix                    string      ops$
SQL>

if any of them doesn't meet the requirements, use below command to change it.
Login as sysdba,

    SQL> alter system set remote_os_authent=FALSE scope=spfile;
    and
    SQL> alter system set os_authent_prefix='' scope=spfile;
If any above changes made, please restart the instance.

4.2 Users

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

SQL> create user user2 identified externally as 'CN=fei';

The user should have have create session granted so they are able to connect,

SQL> grant create session to fei;

If the user exists, please run below command to change it.

SQL> alter user identified externally as 'CN=fei';

5. Test Connection

On client, run below command:

$ sqlplus /nolog
SQL> connect /@weekend
Connected.
SQL>