Monday, August 2, 2021

SSLAuthentication with Oracle database in Summary (Mutual authentication, 2 way authentication, certificate login)

 Setup steps, please reference doc here: https://docs.oracle.com/en-us/iaas/data-safe/doc/create-self-signed-certificate-db-system-client-authentication-enabled.html

Scenario 1: Client doesn't import Server certificate/CA and Server doesn't import client certificate/CA.

(Regardless SSL_CLIENT_AUTHENTICATION=TRUE or FALSE)

Result: connection failed

Message: ORA-29024: Certificate validation failure

Scenario 2: Client import Server certificate/CA and Server doesn't import client certificate/CA.

2.1 Testing with SSL_CLIENT_AUTHENTICATION = FALSE on server

Result: Success

2.2 Testing with SSL_CLIENT_AUTHENTICATION = TRUE on server

Result: Connection failed

Message: ORA-28860: Fatal SSL error

Message in Listener log:

2021-08-02T04:50:44.966526+00:00
02-AUG-2021 04:50:44 * (ADDRESS=(PROTOCOL=tcps)(HOST=10.2.0.80)(PORT=9782)) * <unknown connect data> * 542
TNS-00542: SSL Handshake failed
 TNS-12560: TNS:protocol adapter error

Scenario 3: Client import Server certificate/CA and Server import client certificate/CA.

(Regardless SSL_CLIENT_AUTHENTICATION=TRUE or FALSE)

Result: OK

Take note that 2-way SSL verification only happens if both client and server setting are true.

Scenario 4: Built-on Scenario 3, Authenticate user using SSL Certification

[oracle@ip-10-2-0-80 ~]$ orapki wallet display -wallet "/u01/app/oracle/wallet" -pwd WalletPasswd123
Oracle PKI Tool Release 23.0.0.0.0 - Production
Version 23.0.0.0.0
Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved.

Requested Certificates: 
User Certificates:
Subject:        CN=ip-10-2-0-80.ap-southeast-1.compute.internal
Trusted Certificates: 
Subject:        CN=ip-10-2-0-80.ap-southeast-1.compute.internal
Subject:        CN=ip-10-2-0-83.ap-southeast-1.compute.internal
take note: 1. ops$ and 2. identified externally. (globally is EUS user, not Certificate user)
for certification login, need SSL_CLIENT_AUTHENTICATION = TRUE on both client/server
SQL> CREATE USER ops$clientuser1 IDENTIFIED EXTERNALLY AS 'CN=ip-10-2-0-80.ap-southeast-1.compute.internal';
SQL> grant resource,connect to ops$clientuser1;

[oracle@ip-10-2-0-80 admin]$ sqlplus /@pdb1_ssl
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Aug 2 05:47:28 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
SQL> show user;
USER is "OPS$CLIENTUSER1"
  
SQL> select * from v$session_connect_info where sid=userenv('SID');
{
  "results" : [
    {
      "items" : [
        {
          "sid" : 269,
          "serial#" : 8299,
          "authentication_type" : "GLOBAL",
          "osuser" : "CN=ip-10-2-0-80.ap-southeast-1.compute.internal",
          "network_service_banner" : "",
          "client_charset" : "UTF8",
          "client_connection" : "Homogeneous",
          "client_oci_library" : "Home-based",
          "client_version" : "19.12.0.0.0",
          "client_driver" : "jdbcoci : 19.12.0.0.0",
          "client_lobattr" : "Client Temp Lob Rfc On",
          "client_regid" : 0,
          "con_id" : 3
        },
        {
          "sid" : 269,
          "serial#" : 8299,
          "authentication_type" : "GLOBAL",
          "osuser" : "CN=ip-10-2-0-80.ap-southeast-1.compute.internal",
          "network_service_banner" : "Authentication service for Linux: Version 19.0.1.0.0 - Production",
          "client_charset" : "UTF8",
          "client_connection" : "Homogeneous",
          "client_oci_library" : "Home-based",
          "client_version" : "19.12.0.0.0",
          "client_driver" : "jdbcoci : 19.12.0.0.0",
          "client_lobattr" : "Client Temp Lob Rfc On",
          "client_regid" : 0,
          "con_id" : 3
        },
        {
          "sid" : 269,
          "serial#" : 8299,
          "authentication_type" : "GLOBAL",
          "osuser" : "CN=ip-10-2-0-80.ap-southeast-1.compute.internal",
          "network_service_banner" : "Encryption service for Linux: Version 19.0.1.0.0 - Production",
          "client_charset" : "UTF8",
          "client_connection" : "Homogeneous",
          "client_oci_library" : "Home-based",
          "client_version" : "19.12.0.0.0",
          "client_driver" : "jdbcoci : 19.12.0.0.0",
          "client_lobattr" : "Client Temp Lob Rfc On",
          "client_regid" : 0,
          "con_id" : 3
        },
        {
          "sid" : 269,
          "serial#" : 8299,
          "authentication_type" : "GLOBAL",
          "osuser" : "CN=ip-10-2-0-80.ap-southeast-1.compute.internal",
          "network_service_banner" : "Crypto-checksumming service for Linux: Version 19.0.1.0.0 - Production",
          "client_charset" : "UTF8",
          "client_connection" : "Homogeneous",
          "client_oci_library" : "Home-based",
          "client_version" : "19.12.0.0.0",
          "client_driver" : "jdbcoci : 19.12.0.0.0",
          "client_lobattr" : "Client Temp Lob Rfc On",
          "client_regid" : 0,
          "con_id" : 3
        }
      ]
    }
  ]
}  

Sunday, August 1, 2021

How to enable debug for datapatch for Oracle

 [oracle@ol8 32876380]$ $ORACLE_HOME/OPatch/datapatch -verbose -debug

SQL Patching tool version 19.12.0.0.0 Production on Sun Aug 1 20:47:13 2021 Copyright (c) 2012, 2021, Oracle. All rights reserved. Build label: RDBMS_19.12.0.0.0DBRU_LINUX.X64_210715 initialize entry params_ref: $VAR1 = 'Data::Dumper'; $VAR2 = \{ 'debug' => 1, 'verbose' => 1 }; Creating invocation log dir /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_12003_2021_08_01_20_47_13 Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_12003_2021_08_01_20_47_13/sqlpatch_invocation.log Database (ORACLE_SID): orcl Home (ORACLE_HOME) : /u01/db SQL Patching arguments: debug: 1 verbose: 1 force: 0 prereq: 0 upgrade_mode_only: oh: ignorable_errors: use_rollback_files 0 bootstrap: skip_bootstrap: skip_sql_state_check: 0 skip_upgrade_check: bypass_install: 0 profile: userid: pdbs: exclude_pdbs: allow_pdb_mismatch: noqi: app: 0 binary_config: connect_string: local_inventory: 0 recomp_threshold: 300 orchestration_summary: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_12003_2021_08_01_20_47_13/sqlpatch_summary.json orchestration_progress: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_12003_2021_08_01_20_47_13/sqlpatch_progress.json Connecting to database...OK Gathering database info... open_mode: READ WRITE Getting global lock in EXCLUSIVE mode Global lock obtained in EXCLUSIVE mode create_version_hash(FEATURE, 19.1.0.0.0, , Feature Release, ) entry feature_release_description: 19.1.0.0.0 Feature Release container database! container query: SELECT name,open_mode FROM v$containers ORDER BY con_id sql row: CDB$ROOT READ WRITE sql row: PDB$SEED READ ONLY sql row: PDB1 READ WRITE container database ! printing pdbs data $VAR1 = 'Data::Dumper'; $VAR2 = { 'CDB$ROOT' => { 'lockhandle' => undef, 'pdb_name' => 'CDB$ROOT', 'startup_mode' => 'READ WRITE' }, 'PDB$SEED' => { 'lockhandle' => undef, 'pdb_name' => 'PDB$SEED', 'startup_mode' => 'READ ONLY' }, 'PDB1' => { 'lockhandle' => undef, 'pdb_name' => 'PDB1', 'startup_mode' => 'READ WRITE' } }; $VAR1 = 'Data::Dumper'; $VAR2 = [ 'CDB$ROOT', 'PDB$SEED', 'PDB1' ]; pdb_install_increment: $VAR1 = 'Data::Dumper'; $VAR2 = { 'CDB$ROOT' => 0, 'PDB$SEED' => 0, 'PDB1' => 0 }; done Note: Datapatch will only apply or rollback SQL fixes for PDBs that are in an open state, no patches will be applied to closed PDBs. Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation (Doc ID 1585822.1) initialize complete, final configuration: pdb_list: CDB$ROOT PDB$SEED PDB1 apply_list: rollback_list: upgrade_mode_only: force: 0 prereq_only: 0 user_oh: verbose: 1 debug: 1 database name: ORCL bootstrap attempt 1 Bootstrapping registry and package to current versions... bootstrap attempt 1, local_pdb_list: $VAR1 = 'Data::Dumper'; $VAR2 = [ 'CDB$ROOT', 'PDB$SEED', 'PDB1' ]; bootstrap attempt 1, retry_pdb_list: $VAR1 = 'Data::Dumper'; $VAR2 = []; Checking /u01/db/rdbms/admin/dbmssqlpatch.sql for checkout header Found header $Header: rdbms/admin/dbmssqlpatch.sql /main/16 2018/01/09 13:05:23 surman Exp $ for /u01/db/rdbms/admin/dbmssqlpatch.sql Checking /u01/db/rdbms/admin/prvtsqlpatch.plb for checkout header Found header $Header: rdbms/src/server/dict/plsicds/prvtsqlpatch.sql /st_rdbms_19/1 2021/01/18 09:12:31 apfwkr Exp $ for /u01/db/rdbms/admin/prvtsqlpatch.plb Checking /u01/db/rdbms/admin/dbmsqopi.sql for checkout header Found header $Header: rdbms/admin/dbmsqopi.sql /main/16 2017/10/25 10:57:00 sspulava Exp $ for /u01/db/rdbms/admin/dbmsqopi.sql Checking /u01/db/rdbms/admin/prvtqopi.plb for checkout header Found header $Header: rdbms/src/client/tools/qpinv/prvtqopi.sql /main/56 2018/07/27 18:15:12 aime Exp $ for /u01/db/rdbms/admin/prvtqopi.plb binary_bootstrap_info: $VAR1 = 'Data::Dumper'; $VAR2 = { 'build_label' => 'RDBMS_19.12.0.0.0DBRU_LINUX.X64_210715', 'dbmsqopi_header' => '$Header: rdbms/admin/dbmsqopi.sql /main/16 2017/10/25 10:57:00 sspulava Exp $', 'dbmssqlpatch_header' => '$Header: rdbms/admin/dbmssqlpatch.sql /main/16 2018/01/09 13:05:23 surman Exp $', 'prvtqopi_header' => '$Header: rdbms/src/client/tools/qpinv/prvtqopi.sql /main/56 2018/07/27 18:15:12 aime Exp $', 'prvtsqlpatch_header' => '$Header: rdbms/src/server/dict/plsicds/prvtsqlpatch.sql /st_rdbms_19/1 2021/01/18 09:12:31 apfwkr Exp $' }; bootstrap step 3: checking pdb CDB$ROOT database_bootstrap_info: $VAR1 = 'Data::Dumper'; $VAR2 = { 'build_label' => 'RDBMS_19.12.0.0.0DBRU_LINUX.X64_210715', 'dbmsqopi_header' => '$Header: rdbms/admin/dbmsqopi.sql /main/16 2017/10/25 10:57:00 sspulava Exp $', 'dbmssqlpatch_header' => '$Header: rdbms/admin/dbmssqlpatch.sql /main/16 2018/01/09 13:05:23 surman Exp $', 'prvtqopi_header' => '$Header: rdbms/src/client/tools/qpinv/prvtqopi.sql /main/56 2018/07/27 18:15:12 aime Exp $', 'prvtsqlpatch_header' => '$Header: rdbms/src/server/dict/plsicds/prvtsqlpatch.sql /st_rdbms_19/1 2021/01/18 09:12:31 apfwkr Exp $' }; bootstrap step 3: checking pdb PDB$SEED database_bootstrap_info: $VAR1 = 'Data::Dumper'; $VAR2 = { 'build_label' => 'RDBMS_19.12.0.0.0DBRU_LINUX.X64_210715', 'dbmsqopi_header' => '$Header: rdbms/admin/dbmsqopi.sql /main/16 2017/10/25 10:57:00 sspulava Exp $', 'dbmssqlpatch_header' => '$Header: rdbms/admin/dbmssqlpatch.sql /main/16 2018/01/09 13:05:23 surman Exp $', 'prvtqopi_header' => '$Header: rdbms/src/client/tools/qpinv/prvtqopi.sql /main/56 2018/07/27 18:15:12 aime Exp $', 'prvtsqlpatch_header' => '$Header: rdbms/src/server/dict/plsicds/prvtsqlpatch.sql /st_rdbms_19/1 2021/01/18 09:12:31 apfwkr Exp $' }; bootstrap step 3: checking pdb PDB1 database_bootstrap_info: $VAR1 = 'Data::Dumper'; $VAR2 = { 'build_label' => 'RDBMS_19.12.0.0.0DBRU_LINUX.X64_210715', 'dbmsqopi_header' => '$Header: rdbms/admin/dbmsqopi.sql /main/16 2017/10/25 10:57:00 sspulava Exp $', 'dbmssqlpatch_header' => '$Header: rdbms/admin/dbmssqlpatch.sql /main/16 2018/01/09 13:05:23 surman Exp $', 'prvtqopi_header' => '$Header: rdbms/src/client/tools/qpinv/prvtqopi.sql /main/56 2018/07/27 18:15:12 aime Exp $', 'prvtsqlpatch_header' => '$Header: rdbms/src/server/dict/plsicds/prvtsqlpatch.sql /st_rdbms_19/1 2021/01/18 09:12:31 apfwkr Exp $' }; Bootstrap queue after step 3: $VAR1 = 'Data::Dumper'; $VAR2 = [ { 'bootstrap_needed' => 0, 'bootstrap_string' => '00000', 'pdbs' => [ 'CDB$ROOT' ], 'run_dbmsqopi' => 0, 'run_dbmssqlpatch' => 0, 'run_prvtqopi' => 0, 'run_prvtsqlpatch' => 0, 'run_sqlpatch_bootstrap' => 0 }, { 'bootstrap_needed' => 0, 'bootstrap_string' => '00000', 'pdbs' => [ 'PDB$SEED' ], 'run_dbmsqopi' => 0, 'run_dbmssqlpatch' => 0, 'run_prvtqopi' => 0, 'run_prvtsqlpatch' => 0, 'run_sqlpatch_bootstrap' => 0 }, { 'bootstrap_needed' => 0, 'bootstrap_string' => '00000', 'pdbs' => [ 'PDB1' ], 'run_dbmsqopi' => 0, 'run_dbmssqlpatch' => 0, 'run_prvtqopi' => 0, 'run_prvtsqlpatch' => 0, 'run_sqlpatch_bootstrap' => 0 } ]; ...... }, 'startupMode' => 'normal', 'uniquePatchID' => '24343243' }; set descriptor_source to /u01/db/sqlpatch/32904851/24343243/32904851.xml check_and_set startup_mode normal /u01/db/sqlpatch/32904851/24343243/32904851.xml check_and_set setting startup_mode to normal Patch Description before truncating: "Database Release Update : 19.12.0.0.210720 (32904851)" Patch Description after truncating: "Database Release Update : 19.12.0.0.210720 (32904851)" .... create_version_hash(RU, 19.12.0.0.0, 210716141810, Release_Update, 32904851/24343243) entry loading info for rdbms/admin/backport_files/bug_29032457_apply.sql loading info for rdbms/admin/backport_files/bug_29032457_rollback.sql loading info for rdbms/admin/catcdbviews.sql loading info for rdbms/admin/cdcore_mig.sql loading info for rdbms/admin/cdcore_cols.sql loading info for rdbms/admin/bug29261906_rollback.sql ... loading info for rdbms/admin/bug_29784106_startq.sql loading info for rdbms/admin/bug_29784106_stopq.sql create_version_hash(RU, 19.3.0.0.0, 190407062351, Release_Update, ) entry File rdbms/admin/backport_files/bug_29032457_apply.sql comparing RU 19.3.0.0.0 Release_Update 190407062351 against high low .... }, 'patchResults' => { 'CDB$ROOT' => [ { 'errors' => undef, 'logfile' => '/u01/app/oracle/cfgtoollogs/sqlpatch/32399816/24099425/32399816_rollback_ORCL_CDBROOT_2021Aug01_20_48_36.log', 'mode' => 'rollback', 'patchID' => '32399816', 'patchUID' => '24099425', 'ru_logfile' => undef, 'status' => 'SUCCESS' }, { 'errors' => undef, 'logfile' => '/u01/app/oracle/cfgtoollogs/sqlpatch/32876380/24269510/32876380_apply_ORCL_CDBROOT_2021Aug01_20_53_24.log', 'mode' => 'apply', 'patchID' => '32876380', 'patchUID' => '24269510', 'ru_logfile' => undef, 'status' => 'SUCCESS' } ], 'PDB$SEED' => [ { 'errors' => undef, 'logfile' => '/u01/app/oracle/cfgtoollogs/sqlpatch/32399816/24099425/32399816_rollback_ORCL_PDBSEED_2021Aug01_20_53_26.log', 'mode' => 'rollback', 'patchID' => '32399816', 'patchUID' => '24099425', 'ru_logfile' => undef, 'status' => 'SUCCESS' }, { 'errors' => undef, 'logfile' => '/u01/app/oracle/cfgtoollogs/sqlpatch/32876380/24269510/32876380_apply_ORCL_PDBSEED_2021Aug01_20_53_57.log', 'mode' => 'apply', 'patchID' => '32876380', 'patchUID' => '24269510', 'ru_logfile' => undef, 'status' => 'SUCCESS' } ], 'PDB1' => [ { 'errors' => undef, 'logfile' => '/u01/app/oracle/cfgtoollogs/sqlpatch/32399816/24099425/32399816_rollback_ORCL_PDB1_2021Aug01_20_53_26.log', 'mode' => 'rollback', 'patchID' => '32399816', 'patchUID' => '24099425', 'ru_logfile' => undef, 'status' => 'SUCCESS' }, { 'errors' => undef, 'logfile' => '/u01/app/oracle/cfgtoollogs/sqlpatch/32876380/24269510/32876380_apply_ORCL_PDB1_2021Aug01_20_53_57.log', 'mode' => 'apply', 'patchID' => '32876380', 'patchUID' => '24269510', 'ru_logfile' => undef, 'status' => 'SUCCESS' } ] }, 'summary' => { 'failureReason' => undef, 'returnCode' => 0, 'totalExecutionTime' => '413.69805598259', 'totalInstalledPatches' => 6 } } }; SQL Patching tool complete on Sun Aug 1 20:54:06 2021