notes/tiddlywiki/Oracle SSL connection.md

Source

• https://oracle-base.com/articles/misc/configure-tcpip-with-ssl-and-tls-for-database-connections

Folder creation for configuration files

mkdir -p /mnt/yavin4/tmp/_oracle_/labo_ssl/server/wallet
mkdir -p /mnt/yavin4/tmp/_oracle_/labo_ssl/client/wallet
mkdir -p /mnt/yavin4/tmp/_oracle_/labo_ssl/client/tnsadmin
mkdir -p /mnt/yavin4/tmp/_oracle_/labo_ssl/exchange_zone/

Server wallet and certificate

Create the wallet:

orapki wallet create -wallet "/mnt/yavin4/tmp/_oracle_/labo_ssl/server/wallet" -pwd "C0mpl1cated#Ph|rase" -auto_login_local

Create certificate in wallet:

orapki wallet add -wallet "/mnt/yavin4/tmp/_oracle_/labo_ssl/server/wallet" -pwd "C0mpl1cated#Ph|rase" \
	-dn "CN=`hostname`" -keysize 1024 -self_signed -validity 3650

Display wallet contents:

orapki wallet display -wallet "/mnt/yavin4/tmp/_oracle_/labo_ssl/server/wallet" -pwd "C0mpl1cated#Ph|rase"

Export certificate:

orapki wallet export -wallet "/mnt/yavin4/tmp/_oracle_/labo_ssl/server/wallet" -pwd "C0mpl1cated#Ph|rase" \
	-dn "CN=`hostname`" -cert /mnt/yavin4/tmp/_oracle_/labo_ssl/exchange_zone/`hostname`-certificate.crt

Client wallet and certificate

Create the wallet:

orapki wallet create -wallet "/mnt/yavin4/tmp/_oracle_/labo_ssl/client/wallet" -pwd "1m#the|Client#" -auto_login_local

Create certificate in wallet:

orapki wallet add -wallet "/mnt/yavin4/tmp/_oracle_/labo_ssl/client/wallet" -pwd "1m#the|Client#" \
	-dn "CN=`hostname`" -keysize 1024 -self_signed -validity 3650

Display wallet contents:

orapki wallet display -wallet "/mnt/yavin4/tmp/_oracle_/labo_ssl/client/wallet" -pwd "1m#the|Client#"

Export certificate:

orapki wallet export -wallet "/mnt/yavin4/tmp/_oracle_/labo_ssl/client/wallet" -pwd "1m#the|Client#" \
	-dn "CN=`hostname`" -cert /mnt/yavin4/tmp/_oracle_/labo_ssl/exchange_zone/`hostname`-certificate.crt

Exchange certificates between server and client

Load client certificate into server wallet:

orapki wallet add -wallet "/mnt/yavin4/tmp/_oracle_/labo_ssl/server/wallet" -pwd "C0mpl1cated#Ph|rase" \
	-trusted_cert -cert /mnt/yavin4/tmp/_oracle_/labo_ssl/exchange_zone/taris.swgalaxy-certificate.crt

Display server wallet contents:

orapki wallet display -wallet "/mnt/yavin4/tmp/_oracle_/labo_ssl/server/wallet" -pwd "C0mpl1cated#Ph|rase"

Load server certificate into client wallet:

orapki wallet add -wallet "/mnt/yavin4/tmp/_oracle_/labo_ssl/client/wallet" -pwd "1m#the|Client#" \
	-trusted_cert -cert /mnt/yavin4/tmp/_oracle_/labo_ssl/exchange_zone/mandalore.swgalaxy-certificate.crt

Display client wallet contents:

orapki wallet display -wallet "/mnt/yavin4/tmp/_oracle_/labo_ssl/client/wallet" -pwd "1m#the|Client#"

Server network configuration

I did not succed to user custom $TNS_ADMIN location for server configuration files

In this example we will register the database on standard LISTENER and on custom LISTENER_APP listeners

Edit $ORACLE_HOME/network/admin/sqlnet.ora:

WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = /mnt/yavin4/tmp/_oracle_/labo_ssl/server/wallet)
     )
   )
SQLNET.AUTHENTICATION_SERVICES = (TCPS,NTS,BEQ)
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA, SSL_RSA_WITH_3DES_EDE_CBC_SHA)

Edit $ORACLE_HOME/network/admin/listener.ora:

SSL_CLIENT_AUTHENTICATION = FALSE
WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /mnt/yavin4/tmp/_oracle_/labo_ssl/server/wallet)
    )
  )
LISTENER_APP =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = mandalore.swgalaxy)(PORT = 12000))
      (ADDRESS = (PROTOCOL = TCPS)(HOST = mandalore.swgalaxy)(PORT = 24000))
    )
  )

Edit $ORACLE_HOME/network/admin/tnsnames.ora:

LOCAL_LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = mandalore.swgalaxy)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = mandalore.swgalaxy)(PORT = 12000))
    )
  )

Set local_listener at the database level:

alter system set local_listener='LOCAL_LISTENER'  scope=memory sid='*';
alter system register;

Client network configuration

export TNS_ADMIN=/mnt/yavin4/tmp/_oracle_/labo_ssl/client/tnsadmin

Edit $TNS_ADMIN/sqlnet.ora:

WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = /mnt/yavin4/tmp/_oracle_/labo_ssl/client/wallet)
     )
   )

SQLNET.AUTHENTICATION_SERVICES = (TCPS,NTS)
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA, SSL_RSA_WITH_3DES_EDE_CBC_SHA)

Edit $TNS_ADMIN/tnsnames.ora:

EWOKPRD_APP_SSL=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCPS)(HOST=mandalore.swgalaxy)(PORT=24000)
    )
    (CONNECT_DATA=
      (SERVICE_NAME=EWOKPRD)
    )
  )

EWOKPRD_STANDARD=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)(HOST=mandalore.swgalaxy)(PORT=1521)
    )
    (CONNECT_DATA=
      (SERVICE_NAME=EWOKPRD)
    )
  )

EWOKPRD_APP=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)(HOST=mandalore.swgalaxy)(PORT=12000)
    )
    (CONNECT_DATA=
      (SERVICE_NAME=EWOKPRD)
    )
  )

Test connections:

connect system/*****@EWOKPRD_APP_SSL
connect system/*****@EWOKPRD_APP
connect system/*****@EWOKPRD_STANDARD

Get the current protocol for your session:

select SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') from dual;

Use the following query do display the current network options for your session:

select NETWORK_SERVICE_BANNER 
from v$session_connect_info 
where SID = sys_context('USERENV','SID');

• If you get a row with NETWORK_SERVICE_BANNER like '%TCP/IP%', then you use TCP (without SSL)
• If you get a row with NETWORK_SERVICE_BANNER like '%BEQUEATH%, then you use Bequeath (LOCAL=YES)
• If you get a row with NETWORK_SERVICE_BANNER is null, then you use TCPS