# https://wadhahdaouehi.tn/2023/05/oracle-database-server-client-certificate-tcps-oracle-19c/

   _____                                _     _      
  / ____|                              (_)   | |     
 | (___   ___ _ ____   _____ _ __   ___ _  __| | ___ 
  \___ \ / _ \ '__\ \ / / _ \ '__| / __| |/ _` |/ _ \
  ____) |  __/ |   \ V /  __/ |    \__ \ | (_| |  __/
 |_____/ \___|_|    \_/ \___|_|    |___/_|\__,_|\___|
                                                                                                       

# Create a new auto-login wallet
export WALLET_DIRECTORY=/home/oracle/poc_tls/wallet
export WALLET_PASSWORD="VaeVictis00!"

orapki wallet create -wallet ${WALLET_DIRECTORY} -pwd ${WALLET_PASSWORD} -auto_login_local

# Create a self-signed certificate and load it into the wallet
export CERT_VALIDITY_DAYS=3650

orapki wallet add -wallet ${WALLET_DIRECTORY} -pwd ${WALLET_PASSWORD} -dn "CN=`hostname`" -keysize 2048 -self_signed -validity ${CERT_VALIDITY_DAYS}

# Check the contents of the wallet
orapki wallet display -wallet ${WALLET_DIRECTORY} -pwd ${WALLET_PASSWORD}

Note: The self-signed certificate is both a user and trusted certificate

# Export the certificate to load it into the client wallet later
export CERT_EXPORT_PATH=/home/oracle/poc_tls/export
orapki wallet export -wallet ${WALLET_DIRECTORY} -pwd ${WALLET_PASSWORD} -dn "CN= `hostname` " -cert ${CERT_EXPORT_PATH}/`hostname`-certificate.crt


   _____ _ _            _         _     _      
  / ____| (_)          | |       (_)   | |     
 | |    | |_  ___ _ __ | |_   ___ _  __| | ___ 
 | |    | | |/ _ \ '_ \| __| / __| |/ _` |/ _ \
 | |____| | |  __/ | | | |_  \__ \ | (_| |  __/
  \_____|_|_|\___|_| |_|\__| |___/_|\__,_|\___|
                                                                                            

# Create a new auto-login wallet
export WALLET_DIRECTORY=/mnt/yavin4/tmp/00000/wayland/wallet
export WALLET_PASSWORD="AdVictoriam00!"

orapki wallet create -wallet ${WALLET_DIRECTORY} -pwd ${WALLET_PASSWORD} -auto_login_local

# Create a self-signed certificate and load it into the wallet
export CERT_VALIDITY_DAYS=3650

orapki wallet add -wallet ${WALLET_DIRECTORY} -pwd ${WALLET_PASSWORD} -dn "CN=`hostname`" -keysize 2048 -self_signed -validity ${CERT_VALIDITY_DAYS}

# Check the contents of the wallet
orapki wallet display -wallet ${WALLET_DIRECTORY} -pwd ${WALLET_PASSWORD}

Note: The self-signed certificate is both a user and trusted certificate

# Export the certificate to load it into the client wallet later
export CERT_EXPORT_PATH="/mnt/yavin4/tmp/00000/wayland/cert_expo"
orapki wallet export -wallet ${WALLET_DIRECTORY} -pwd ${WALLET_PASSWORD} -dn "CN= `hostname` " -cert ${CERT_EXPORT_PATH}/`hostname`-certificate.crt


   _____          _   _  __ _           _                       _                            
  / ____|        | | (_)/ _(_)         | |                     | |                           
 | |     ___ _ __| |_ _| |_ _  ___ __ _| |_ ___    _____  _____| |__   __ _ _ __   __ _  ___ 
 | |    / _ \ '__| __| |  _| |/ __/ _` | __/ _ \  / _ \ \/ / __| '_ \ / _` | '_ \ / _` |/ _ \
 | |___|  __/ |  | |_| | | | | (_| (_| | ||  __/ |  __/>  < (__| | | | (_| | | | | (_| |  __/
  \_____\___|_|   \__|_|_| |_|\___\__,_|\__\___|  \___/_/\_\___|_| |_|\__,_|_| |_|\__, |\___|
                                                                                   __/ |     
                                                                                  |___/      

Note: Both Server/Client should trust each other

# Load the client certificate into the server wallet
export WALLET_DIRECTORY=/mnt/yavin4/tmp/00000/bakura/wallet
export WALLET_PASSWORD="VaeVictis00!"
export CERT_EXPORT_FILE="/mnt/yavin4/tmp/00000/wayland/cert_expo/wayland.swgalaxy-certificate.crt"

orapki wallet add -wallet ${WALLET_DIRECTORY} -pwd ${WALLET_PASSWORD} -trusted_cert -cert ${CERT_EXPORT_FILE}
# Check the contents of the wallet
orapki wallet display -wallet ${WALLET_DIRECTORY} -pwd ${WALLET_PASSWORD}


# Load the server certificate into the client wallet
export WALLET_DIRECTORY=/mnt/yavin4/tmp/00000/wayland/wallet
export WALLET_PASSWORD="AdVictoriam00!"
export CERT_EXPORT_FILE="/mnt/yavin4/tmp/00000/bakura/cert_expo/bakura.swgalaxy-certificate.crt"

orapki wallet add -wallet ${WALLET_DIRECTORY} -pwd ${WALLET_PASSWORD} -trusted_cert -cert ${CERT_EXPORT_FILE}
# Check the contents of the wallet
orapki wallet display -wallet ${WALLET_DIRECTORY} -pwd ${WALLET_PASSWORD}


  _      _     _                                  _               
 | |    (_)   | |                                | |              
 | |     _ ___| |_ ___ _ __   ___ _ __   ___  ___| |_ _   _ _ __  
 | |    | / __| __/ _ \ '_ \ / _ \ '__| / __|/ _ \ __| | | | '_ \ 
 | |____| \__ \ ||  __/ | | |  __/ |    \__ \  __/ |_| |_| | |_) |
 |______|_|___/\__\___|_| |_|\___|_|    |___/\___|\__|\__,_| .__/ 
                                                           | |    
                                                           |_|    

Note: I didn't succeed the LISTENER setup using a custom TNS_ADMIN or using /etc/listener.ora file

rm -rf /etc/listener.ora
rm -rf /etc/tnsnames.ora


# I'm using a read-only ORACLE_HOME
cat $(orabasehome)/network/admin/sqlnet.ora

WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /mnt/yavin4/tmp/00000/bakura/wallet)
    )
  )

SQLNET.AUTHENTICATION_SERVICES = (TCPS,BEQ,NTP)
SSL_CLIENT_AUTHENTICATION = FALSE


cat $(orabasehome)/network/admin/listener.ora
SSL_CLIENT_AUTHENTICATION = FALSE
WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /mnt/yavin4/tmp/00000/bakura/wallet)
    )
  )

LISTENER_DEMO =
   (DESCRIPTION_LIST =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = bakura.swgalaxy)(PORT = 1600))
     )
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCPS)(HOST = bakura.swgalaxy)(PORT = 1700))
     )
   ) 

# start specific listener
lsnrctl start LISTENER_DEMO

# register the database in the listener; note that TCPS adress was not required
alter system set local_listener='(DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = bakura.swgalaxy)(PORT = 1600))  ) )'  scope=both sid='*';
alter system register;

Note: I don't explicitly specified TCPS adress but TCPS connections will be OK

   _____ _ _            _              _               
  / ____| (_)          | |            | |              
 | |    | |_  ___ _ __ | |_   ___  ___| |_ _   _ _ __  
 | |    | | |/ _ \ '_ \| __| / __|/ _ \ __| | | | '_ \ 
 | |____| | |  __/ | | | |_  \__ \  __/ |_| |_| | |_) |
  \_____|_|_|\___|_| |_|\__| |___/\___|\__|\__,_| .__/ 
                                                | |    
                                                |_|    
Note: On client side, custom TNS_ADMIN worked

export TNS_ADMIN=/mnt/yavin4/tmp/00000/wayland/tns_admin

cd $TNS_ADMIN

cat sqlnet.ora 

WALLET_LOCATION =
    (SOURCE =
      (METHOD = FILE)
      (METHOD_DATA =
        (DIRECTORY = /mnt/yavin4/tmp/00000/wayland/wallet)
      )
    )

SQLNET.AUTHENTICATION_SERVICES = (TCPS,BEQ,NTP)
SSL_CLIENT_AUTHENTICATION = FALSE


cat tnsnames.ora 

HUTTPRD_tcp =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = bakura.swgalaxy)(PORT = 1600))
     )
     (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = HUTTPRD)
     )
   )

HUTTPRD_tcps =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCPS)(HOST = bakura.swgalaxy)(PORT = 1700))
     )
     (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = HUTTPRD)
     )
   )

# JABBA is a PDB inside HUTTPRD
JABBA_tcps =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCPS)(HOST = bakura.swgalaxy)(PORT = 1700))
     )
     (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = JABBA)
     )
   )


# check connections
connect c##globaldba/"secret"@HUTTPRD_tcp
connect c##globaldba/"secret"@HUTTPRD_tcps
connect c##globaldba/"secret"@JABBA_tcps

# check for connection protocol: tcp/tcps
select SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') from dual;