OCI IAM: Interactive Oracle AI Database Authentication

As security posture requirements continue to ratchet up over time, more and more customers are looking into centralizing authentication, authorization and user life cycle management of their Oracle AI Database estate.  The two latest cloud native authentication frameworks introduced are native Entra ID integration and Oracle Cloud Identity & Access Management (OCI IAM)  integration using OAuth2 standard flows.  

OCI IAM integration offers three methods of database user authentication:

1. Password Verifier Authentication

2. DB Access Token (db-token) Authentication

3. Interactive OAuth2 Flow

These three authentication methods are only applicable to Oracle AI Database servers with an Oracle Cloud Identifier (OCID).  Per the Oracle AI Database 26ai Security Guide, supported database deployments include:

• Autonomous Database Serverless
• Autonomous Database on Dedicated Exadata Infrastructure
• Exadata Cloud@Customer Infrastructure
• Exadata Cloud Service on Dedicated Infrastructure
• Exadata Cloud Service on Cloud@Customer Infrastructure
• Base Database Service
• Exadata dedicated and Autonomous on dedicated Exadata @Azure, @AWS, @Google

They notably OCI IAM integration does not apply to:

• Standalone database server on servers, virtual machines, or cloud compute
• Database server on Windows, AIX, Solaris or HPUX
• Exadata on premises

OCI IAM Integration Setup

Here is the outline for setting up OCI IAM database authentication integration.

1. OCI IAM Users, Groups, And Memberships

Create OCI IAM groups that will be used for shared user schema and database roles and add user to the shared user schema group and all groups that will map to database roles.

 

In this example, we create the following OCI IAM groups:

• allDBUsers for the shared user schema
• dbMinPriv for minimum privilege database users
• dbMaxPriv for maximum privilege database users

This is just an example to show least and most privilege. You will come up with your own set of database roles according to the needs of your environment, business units and applications.

2. OCI IAM Policies

Add OCI IAM policies that grant use of the database-connections, database-family, and autonomous-database-family resources by tenancy or compartment to each of the OCI IAM groups specified (allDBUsers, dbMinPriv and dbMaxPriv).  There are typically two approaches that customers take to these policies. They either scope to the entire tenancy or to individual compartments within the tenancy.

Here is an example of granting OCI IAM groups to use the database-connections, database-family, and autonomous-database-family resources.

allow group MyIdentityDomain/allDBUsers to use database-connections in tenancy
allow group MyIdentityDomain/dbMinPriv to use database-connections in tenancy
allow group MyIdentityDomain/dbMaxPriv to use database-connections in tenancy

allow group MyIdentityDomain/allDBUsers to use database-family in tenancy
allow group MyIdentityDomain/dbMinPriv to use database-family in tenancy
allow group MyIdentityDomain/dbMaxPriv to use database-family in tenancy

allow group MyIdentityDomain/allDBUsers to use autonomous-database-family in tenancy
allow group MyIdentityDomain/dbMinPriv to use autonomous-database-family in tenancy
allow group MyIdentityDomain/dbMaxPriv to use autonomous-database-family in tenancy

Here is the same policy applied to a specific compartment "development:dev_dbs".

allow group MyIdentityDomain/allDBUsers to use database-connections in compartment development:dev_dbs
allow group MyIdentityDomain/dbMinPriv to use database-connections in compartment development:dev_dbs
allow group MyIdentityDomain/dbMaxPriv to use database-connections in compartment development:dev_dbs

allow group MyIdentityDomain/allDBUsers to use database-family in compartment development:dev_dbs
allow group MyIdentityDomain/dbMinPriv to use database-family in compartment development:dev_dbs
allow group MyIdentityDomain/dbMaxPriv to use database-family in compartment development:dev_dbs

allow group MyIdentityDomain/allDBUsers to use autonomous-database-family in compartment development:dev_dbs
allow group MyIdentityDomain/dbMinPriv to use autonomous-database-family in compartment development:dev_dbs
allow group MyIdentityDomain/dbMaxPriv to use autonomous-database-family in compartment development:dev_dbs

Note in both examples that the group is prefaced by the identity domain (MyIdentityDomain/). This scopes the authentication to only the groups of this identity domain. 

3. Enable OCI IAM In Database

Configure database server for OCI IAM integration Autonomous Database.  Note that this will need to be applied to each container (CDB) or pluggable (PDB) database.

BEGIN
   DBMS_CLOUD_ADMIN.ENABLE_EXTERNAL_AUTHENTICATION(
      type => 'OCI_IAM' );
END;
/

Here is the same configuration applied to a non-autonomous database.

ALTER SYSTEM SET IDENTITY_PROVIDER_TYPE=OCI_IAM SCOPE=BOTH;
ALTER SYSTEM RESET IDENTITY_PROVIDER_CONFIG SCOPE=BOTH;

Confirm the OCI IAM database configuration has been applied. Note that the identity_provider_type should now be set to OCI_IAM and the identity_provider_config should not have a value.

SQL> SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME='identity_provider_type';

NAME                      VALUE      
_________________________ __________ 
identity_provider_type    OCI_IAM    

SQL> SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME='identity_provider_config';

NAME                        VALUE    
___________________________ ________ 
identity_provider_config             

4. Configure Database Users And Roles

Configure shared or exclusive user schema and database roles that map to OCI IAM groups and grant relevant privileges to the database roles.

CREATE USER allDbUsers IDENTIFIED GLOBALLY AS 'IAM_GROUP_NAME=MyIdentityDomain/allDbUsers';

CREATE ROLE dbMinPriv IDENTIFIED GLOBALLY AS 'IAM_GROUP_NAME=MyIdentityDomain/dbMinPriv';

GRANT CREATE SESSION to dbminpriv;

CREATE ROLE dbMaxPriv IDENTIFIED GLOBALLY AS 'IAM_GROUP_NAME=MyIdentityDomain/dbMaxPriv';

GRANT pdb_dba, CREATE SESSION to dbmaxpriv;

5. Get OCI ojdbc-extensions provider for JDBC thin applications

a. Make a directory where the ojdbc-extensions jar files will reside

Windows:

cd C:
mkdir \ojdbc-extensions\oci-1.0.6
cd \ojdbc-extensions\oci-1.0.6

MacOS/Linux:

mkdir -p /var/tmp/ojdbc-extensions/oci-1.0.6
cd /var/tmp/ojdbc-extensions/oci-1.0.6

b. Download the OCI ojdbc-extensions provider from Maven repository.

curl -sko ojdbc11.jar https://repo1.maven.org/maven2/com/oracle/database/jdbc/ojdbc11/23.26.2.0.0/ojdbc11-23.26.2.0.0.jar

c. Download the desired 1.0.6 version of OCI ojdbc-extensions provider into a version specific directory:

java -jar ojdbc11.jar get-deps --coords com.oracle.database.jdbc/ojdbc-provider-oci/1.0.6 --path C:\ojdbc-extensions\oci-1.0.6

If this fails, you may need also need to load private certificate chain for Web Application Firewall (WAF) SSL/TLS termination or SSL/TLS offloading service into the Java cacerts truststore.

6. Setup Database Clients

For JDBC thin client applications, the client application will need to be configured to load the OCI ojdbc-extensions provider.

a. SQLcl

Add the OCI ojdbc-extensions provider files to SQLcl library (<SQLcl_dir>).

Windows:

cd C:\
mkdir C:\<SQLcl_dir>\lib\sdks\jdbc-oci
copy \ojdbc-extensions\oci-1.0.6\* C:\<SQLcl_dir>\lib\sdks\jdbc-oci

MacOS/Linux:

mkdir -p <SQLcl_dir>/sdks/jdbc-oci
cp /var/tmp/ojdbc-extensions/oci-1.0.6/* <SQLcl_dir>/sdks/jdbc-oci

b. SQL Developer

For SQL Developer, you will need to add the jar files to the product.conf configuration file.

Windows: <home>\AppData\Roaming\sqldeveloper\<version>/product.conf

MacOS/Linux: $HOME/.sqldeveloper/<version>/product.conf

The following two examples provide examples of how to automate the creation of the list of jar files content to add to the product.conf file.

Windows:

Get-ChildItem -Path "C:\ojdbc-extensions\oci-1.0.6" -Include "*.jar" -Recurse -Name -Force | ForEach-Object { Add-Content -Path C:\ojdbc-extensions\oci-1.0.6\product.txt  -Value "AddJavaLibFile C:\ojdbc-extensions\oci-1.0.6\$_"}

MacOS/Linux:

find /var/tmp/ojdbc-extensions/oci-1.0.6 -name "*.jar"|sed -e "s/^/AddJavaLibFile /g"

7. Get Oracle Client Wallet

Because the OCI IAM integration requires an encrypted TLS connection between the database client and server, you will need to copy the client wallet to the host where the client application resides. Here is where I put the wallet on my Windows system.

C:\ojdbc-extensions\client_wallet

8. TNS Record For Interactive Flow

Create a TNS record in the tnsnames.ora client name resolution configuration file that includes PROTCOL=TCPS, PORT=<secure_port>, WALLET_LOCATION=<path_of_client_wallet>, TOKEN_AUTH=OCI_INTERACTIVE,  OCI_COMPARTMENT, and OCI_DATABASE.

For example:

DEVDB_PDB1_SSL_OCI=
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCPS)(HOST=devdb-scan.mysubnet.odswest.oraclevcn.com)(PORT=2484))
    (SECURITY=
      (WALLET_LOCATION=C:\ojdbc-extensions\client_wallet)
      (TOKEN_AUTH=OCI_INTERACTIVE)
      (OCI_COMPARTMENT=<compartment_ocid>)
      (OCI_DATABASE=<database_ocid>)
    )
    (CONNECT_DATA=
      (SERVER=DEDICATED)
      (SERVICE_NAME=devdb_pdb1.mysubnet.odswest.oraclevcn.com)
    )
  )

9. Test Each Client Application

Here is how I tested SQLcl and SQL Developer.

a. SQLcl

sql -thin /@DEVDB_PDB1_SSL_OCI

b. SQL Developer

Open SQL Developer and add a new connection and connect with that new connection. Here is a sample configuration from my lab environment.

Name: DEVDB_PDB1_SSL_OCI_INTERACTIVE

Database Type: Oracle

Authentication Type: OS

Connection Type: TNS

Network Alias: DEVDB_PCB1_SSL_OCI

 

I hope you find this helpful.

Blessings!

RAC TLS: Enabling TLS TCPS/2484 on local node IP

Over the past couple of days, I've been preparing a Real Application Cluster (RAC) cluster via the Oracle AI Base Database Service for setting up a demonstration where I need to connect securely to the cluster nodes remotely over Transport Layer Security (TLS) to TCPS/2484 from my home computer.  The problem is two fold.

First, the public IP addresses given by Oracle Cloud Infrastructure (OCI) console map to the individual RAC node IP addresses.

Second, the default RAC listener configuration only has the non-TLS TCP/1521 port listening on the RAC node IP address. I need to add the TLS TCP/2484 port to the RAC node IP address as well.

Thankfully, the workaround was very simple.  I just needed to alter the default LISTENER in listener.ora to include both TCP/1521 and TCPS/2484 for both the local RAC node IP address (10.0.0.15) and the virtual IP address (VIP) (10.0.0.120).

Before applying the change, the client would return the following error:

ORA-12541: Cannot connect. No listener at host 132.226.96.94 port 2484

Here's the listener.ora config change where I commented out the original LISTENER definition and added a new LISTENER definition that includes the original information plus ADDRESS entries for the RAC node IP address is 10.0.0.15 and the VIP address is 10.0.0.120 as well. I also specified. Note that if the WALLET_LOCATION was already specified in listener.ora, I wouldn't need to include it here but for completeness, I added it to be clear on which wallet to use for the TCPS port.

$ tail -12 /u01/app/19.0.0.0/grid/network/admin/listener.ora

#LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.120)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCPS)(HOST = 10.0.0.120)(PORT = 2484))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.15)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCPS)(HOST = 10.0.0.15)(PORT = 2484))
      (SECURITY=(WALLET_LOCATION=/opt/oracle/dcs/commonstore/tcps_wallet))
    )
  )

Then, restart the local listener.

$ srvctl stop listener
$ srvctl start listener

Confirm that the listener includes TCPS/2484 on the local RAC node IP (10.0.0.15) and the VIP (10.0.0.120) from the listener status and netstat outputs.

$ lsnrctl status|egrep "Endpoint|HOST="
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.120)(PORT=1521))(SECURITY=(WALLET_LOCATION=/opt/oracle/dcs/commonstore/tcps_wallet)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=10.0.0.120)(PORT=2484))(SECURITY=(WALLET_LOCATION=/opt/oracle/dcs/commonstore/tcps_wallet)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.15)(PORT=1521))(SECURITY=(WALLET_LOCATION=/opt/oracle/dcs/commonstore/tcps_wallet)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=10.0.0.15)(PORT=2484))(SECURITY=(WALLET_LOCATION=/opt/oracle/dcs/commonstore/tcps_wallet)))


$ netstat -ltn | grep 2484
tcp        0      0 10.0.0.62:2484          0.0.0.0:*               LISTEN     
tcp        0      0 10.0.0.182:2484         0.0.0.0:*               LISTEN     
tcp        0      0 10.0.0.45:2484          0.0.0.0:*               LISTEN     
tcp        0      0 10.0.0.120:2484         0.0.0.0:*               LISTEN     
tcp        0      0 10.0.0.15:2484          0.0.0.0:*               LISTEN     

Confirm that the remote client can now connect over TLS to TCPS/2484 of each cluster node:

$ cat tnsnames.ora
DEVDB_SSL=
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCPS)(HOST=devdb-scan.mysubnet.odswest.oraclevcn.com)(PORT=2484))
    (SECURITY=
      (WALLET_LOCATION=C:\Oracle\client_wallet)
      (SSL_SERVER_DN_MATCH=TRUE)
      (SSL_SERVER_CERT_DN="CN=devdb-scan.mysubnet.odswest.oraclevcn.com")
    )
    (CONNECT_DATA=
      (SERVER=DEDICATED)
      (SERVICE_NAME=devdb.mysubnet.odswest.oraclevcn.com)
    )
  )

$ sqlplus system/'<db_password>@DEVDB_SSL

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
devdb1

I hope you find this helpful.

Blessings!

RAC TLS: How To Complete Self-signed Cert Setup

With Oracle Exadata Cloud Service and Base Database Service, the encrypted TCPS port 2484 is partially setup with a self-signed certificate stored in the remote grid computing (GRID) listener wallet and TCPS port 2484 is enabled on both the remote GRID listener and the local database server listener.  

IMPORTANT: Please note that use of self-signed certificates are intended for non-production environments.  Production environments should use certificates that are signed by a public or private certificate authority. Using certificates signed by public and/or private certificate authorities are not only more secure but also easy to maintain operationally because no client wallet needs to be created, periodically updated or distributed to client hosts because with public and/or private certificate authorities, the client WALLET_LOCATION can be set to SYSTEM. WALLET_LOCATION=SYSTEM means, use the local host's certificate chain trust store.

In this blog, I demonstrate the steps to complete the TCPS setup on a Real Application Cluster (RAC) Real Application Cluster (RAC) node of a container database (CDB) of a Base Database Service. Therefore, all references to the "local database server" are using the CDB TLS Dir path. If the database that you want to complete self-signed certificate configuration is a pluggable database (PDB), you would use the PDB TLS Dir outlined below.  Also, in the Base Database Service, all of the wallets are owned by the oracle user.  In the Exadata Cloud Service server, the GRID wallet may be owned by the grid user. Therefore, you may need to switch between users and in the case of copying the GRID wallet, you may need to sudo to root to copy the wallet and change the target ownership to oracle:oinstall.

The default wallet directories for the Base Database Service are as follows:

• GRID Wallet: /opt/oracle/dcs/commonstore/tcps_wallet
• WALLET_ROOT: /opt/oracle/dcs/commonstore/wallets/<DB_NAME>
• CDB TLS Dir: /opt/oracle/dcs/commonstore/wallets/<DB_NAME>/tls
• PDB TLS Dir: /opt/oracle/dcs/commonstore/wallets/<DB_NAME>/<PDB_GUID>/tls

The default wallet directories for Exadata Cloud Service are as follows:

• GRID Wallet: /var/opt/oracle/dbaas_acfs/grid/tcps_wallets
• WALLET_ROOT: /var/opt/oracle/dbaas_acfs/<DB_NAME>/wallet_root
• CDB TLS Dir: /var/opt/oracle/dbaas_acfs/wallet_root/<DB_NAME>/tls
• PDB TLS Dir: /var/opt/oracle/dbaas_acfs/<DB_NAME>/wallet_root/<PDB_GUID>/tls

For this example, the database connection string that I use to connect to the CDB database server from within the RAC cluster node is:

DEVDB_SSL=
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCPS)(HOST=devdb-scan.mysubnet.odswest.oraclevcn.com)(PORT=2484))
    (SECURITY=
      (WALLET_LOCATION=/opt/oracle/dcs/commonstore/wallets/devdb/tls)
      (SSL_SERVER_DN_MATCH=TRUE)
      (SSL_SERVER_CERT_DN="CN=devdb-scan.mysubnet.odswest.oraclevcn.com")
    )
    (CONNECT_DATA=
      (SERVER=DEDICATED)
      (SERVICE_NAME=devdb.mysubnet.odswest.oraclevcn.com)
    )
  )

You can confirm that the remote GRID listener is configured to listen on TCPS port 2484 with:

sudo su - oracle
srvctl config scan_listener
SCAN Listeners for network 1:
Registration invited nodes:
Registration invited subnets:
Endpoints: TCP:1521/TCPS:2484
SCAN Listener LISTENER_SCAN1 exists
SCAN Listener is enabled.
SCAN Listener LISTENER_SCAN2 exists
SCAN Listener is enabled.
SCAN Listener LISTENER_SCAN3 exists
SCAN Listener is enabled.

You can confirm that the local database server listener is configured to listen on TCPS port 2484 with:

sudo su - oracle
srvctl config listener
Name: LISTENER
Type: Database Listener
Network: 1, Owner: grid
Home: <CRS home>
End points: TCP:1521/TCPS:2484
Listener is enabled.
Listener is individually enabled on nodes:
Listener is individually disabled on nodes: 

And you can confirm that the GRID listener wallet has a self-signed certificate loaded with:

sudo su - oracle
cd /opt/oracle/dcs/commonstore
orapki wallet display -nologo -summary -wallet tcps_wallet
Requested Certificates:
User Certificates:
Subject:        CN=devdb-scan.mysubnet.odswest.oraclevcn.com
Trusted Certificates:
Subject:        CN=devdb-scan.mysubnet.odswest.oraclevcn.com

The steps required to complete the TCPS setup are simply to setup the local database server listener wallet and ensure it includes the remote GRID listener certificate.  There are two paths toward this end. 

EASY METHOD

The easy method is simply to copy the existing local GRID listener wallet to the local database server listener wallet and ensure the proper permissions are set on the local CDB database server listener wallet.

For Base Database Service:

sudo su - oracle
cd /opt/oracle/dcs/commonstore
mkdir -p wallets/devdb/tls

rsync -Ha tcps_wallet/. ./wallets/devdb/tls/.

chmod g+rx ./wallets/devdb/tls
chmod g+r ./wallets/devdb/tls/*

For Exadata Cloud Service:

First, lookup the wallet_root directory.

sudo su - oracle
sqlplus / as sysdba
SQL> show parameter wallet_root;

NAME      TYPE VALUE
------------------------------------ ----------- ------------------------------
wallet_root      string  /var/opt/oracle/dbaas_acfs/wallet_root

Then, create/replace the local CDB database server wallet according to the above wallet_root path.

sudo su - oracle
cd /var/opt/oracle/dbaas_acfs/grid
mkdir -p wallet_root/devdb/tls
exit


sudo /bin/bash
rsync -Ha /var/opt/oracle/dbaas_acfs/grid/tcps_wallets/.  /var/opt/oracle/dbaas_acfs/wallet_root/devdb/tls/.
chown -R oracle:oinstall /var/opt/oracle/dbaas_acfs/wallet_root/devdb/tls
chmod g+rx /var/opt/oracle/dbaas_acfs/wallet_root/devdb/tls
chmod g+r /var/opt/oracle/dbaas_acfs/wallet_root/devdb/tls/*
exit

SLIGHTLY MORE SECURE METHOD

The slightly more secure method prescribed by knowledge article KB837637 entails creating a new separate self-signed certificate for the local CDB database server listener wallet and loading it with the remote GRID listener certificate as well.

First, create the new separate local CDB database server listener wallet with auto_login enabled.

sudo su - oracle
orapki wallet create -nologo -wallet /opt/oracle/dcs/commonstore/wallets/devdb/tls -pwd "Oracle123" -auto_login

Then, create a new self-signed certificate in the local CDB database server listener wallet.

sudo su - oracle
orapki wallet add -nologo -wallet /opt/oracle/dcs/commonstore/wallets/devdb/tls -pwd "Oracle123" -dn "CN=devdb.mysubnet.odswest.oraclevcn.com" -asym_alg RSA -keysize 2048 -self_signed -validity 3650

Lastly, export the self-signed remote GRID listener certificate and load it into the local CDB database server listener wallet.

sudo su - oracle
cd /opt/oracle/dcs/commonstore
orapki wallet export -nologo -wallet tcps_wallet -dn CN=devdb-scan.mysubnet.odswest.oraclevcn.com -cert grid_wallet.pem

orapki wallet add -nologo -wallet wallets/devdb/tls -pwd "Oracle123" -dn "CN=devdb-scan.mysubnet.odswest.oraclevcn.com" -trusted_cert -cert grid_wallet.pem

CLIENT WALLET

If you test from a remote host, you will need a client wallet containing both the certificate from the local CDB database server wallet and the remote GRID listener wallet.  Here is how to create a client wallet on the database server and package it up for use on a remote database client computers.

sudo su - oracle
cd /opt/oracle/dcs/commonstore
orapki wallet export -nologo -wallet tcps_wallet -dn CN=devdb-scan.mysubnet.odswest.oraclevcn.com -cert grid_wallet.pem
orapki wallet export -nologo -wallet wallets/devdb/tls -dn CN=devdb.mysubnet.odswest.oraclevcn.com -cert cdb_wallet.pem
orapki wallet create -nologo -wallet client_wallet -pwd "Oracle123" -auto_login
orapki wallet add -nologo -wallet client_wallet -pwd "Oracle123" -dn "CN=devdb-scan.mysubnet.odswest.oraclevcn.com" -trusted_cert -cert grid_wallet.pem
orapki wallet add -nologo -wallet client_wallet -pwd "Oracle123" -dn "CN=devdb.mysubnet.odswest.oraclevcn.com" -trusted_cert -cert cdb_wallet.pem

cat > client_wallet/tnsnames.ora <<EOF
DEVDB_SSL=
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCPS)(HOST=devdb-scan.mysubnet.odswest.oraclevcn.com)(PORT=2484))
    (SECURITY=
      (WALLET_LOCATION=.)
      (SSL_SERVER_DN_MATCH=TRUE)
      (SSL_SERVER_CERT_DN="CN=devdb-scan.mysubnet.odswest.oraclevcn.com")
    )
    (CONNECT_DATA=
      (SERVER=DEDICATED)
      (SERVICE_NAME=devdb. mysubnet.odswest.oraclevcn.com)
    )
  )
EOF

cat > client_wallet/sqlnet.ora <<EOF
WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=.))
SSL_CLIENT_AUTHENTICATION=FALSE
EOF

zip -r client_wallet.zip client_wallet

TEST

At this point, if you connect and login through port 2484, the login should be successful and once logged in confirm that the connection is over secure encrypted TCPS network protocol.

sqlplus system@DEVDB_SSL
Enter password: 
Last Successful login time: Mon Jun 22 2026 21:24:04 +00:00

Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.31.0.0.0

SQL> SELECT sys_context ('userenv','NETWORK_PROTOCOL') FROM DUAL;

SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')
--------------------------------------------------------------------------------
tcps

TROUBLESHOOTING

If when initially attempting to connect securely and login to port 2484 with a SQL client BEFORE setting up the local CDB database server listener wallet, the following error would be returned because the database server wallet for the local listener had not yet been setup or didn't yet have auto_login enabled.

ORA-28759: failure to open file

Here is how to create the wallet where devdb is the name of the CDB database server and Oracle123 is the password used to protect the wallet from changes. You should set the wallet password to whatever you desire.

sudo su - oracle
orapki wallet create -nologo -wallet /opt/oracle/dcs/commonstore/wallets/devdb/tls -pwd "Oracle123" -auto_login

If the local CDB database server listener wallet existed and had auto_login enabled but had not yet been loaded with a signed certificate, the following error would be returned to the SQL client:

ORA-28845: No certificate

Here is how to add a self-signed certificate to the local listener's CDB database server wallet where devdb is the CDB database server name, Oracle123 is the wallet password and devdb.mysubnet.odswest.oraclevcn.com is CDB database name used for the local listener wallet certificate subject CN value.  The certificate subject CN value can be arbitrary but for consistency's sake, I typically use the database name and the domain name.

sudo su - oracle
orapki wallet add -nologo -wallet /opt/oracle/dcs/commonstore/wallets/devdb/tls -pwd "Oracle123" -dn "CN=devdb.mysubnet.odswest.oraclevcn.com" -asym_alg RSA -keysize 2048 -self_signed -validity 3650

Once the local listener CDB database server wallet is loaded with the new self-signed certificate, if you attempt to login locally to the CDB database server, the following error is returned:

ORA-29024: Certificate validation failure

This is because the self signed certificate of the remote GRID listener has not yet been loaded into the local CDB database server listener wallet.  This is accomplished by exporting the self-signed certificate from the remote GRID listener wallet and loading that certificate into the wallet of the local CDB database server wallet.

sudo su - oracle
cd /opt/oracle/dcs/commonstore
orapki wallet export -nologo -wallet tcps_wallet -dn CN=devdb-scan.mysubnet.odswest.oraclevcn.com -cert grid_wallet.pem

orapki wallet add -nologo -wallet wallets/devdb/tls -pwd "Oracle123" -dn "CN=devdb-scan.mysubnet.odswest.oraclevcn.com" -trusted_cert -cert grid_wallet.pem

I hope you find this helpful.

Blessings!

MacOS ODB Client Setup With EntraID Integration

A customer that does all of their Oracle database development and administration from Apple Silicon based MacOS computers and asked what the most efficient method is for setting up Oracle suite of database client tools on MacOS where the tools are setup for Entra ID integration.  I worked with them to get everything setup and am sharing my notes in case anyone else would like to do the same. Note that this is just one of many ways that you could set everything up.  There is nothing special about the $HOME/.oracle directory.  I just used a single base directory ($HOME/.oracle) for simplicity.

1. Setup Base Directory

Make a base directory in which to store the environment, TNS admin data, wallets, and downloaded software

mkdir -p $HOME/.oracle/env $HOME/.oracle/bits $HOME/.oracle/admin $HOME/.oracle/instant_client

2. Download Software

Download the following software into $HOME/.oracle/bits:

• JDK SE 21 [ARM64 Compressed Archive]
• Oracle 26ai Instant Client [Basic Package and SQL*Plus Package]
• SQLcl
• SQL Developer 

3. Extract Software Into Desired Directories

Extract and place each software package in respective folders in $HOME/.oracle in a Terminal window:

cd $HOME/.oracle
tar -zxf bits/jdk-21_macos-aarch64_bin.tar.gz
mv jdk-21.0.11.jdk/Contents jdk
$ rmdir jdk-21.0.11.jdk
unzip -qo bits/sqlcl-26.1.2.132.1334.zip
unzip -qo -d $HOME/Applications $HOME/.oracle/bits/sqldeveloper-24.3.1.347.1826-macos-x64.app.zip
hdiutil attach $HOME/.oracle/bits/instantclient-basic-macos.arm64-23.26.1.0.0.dmg -mountpoint /Volumes/ic_basic
rsync -Ha /Volumes/ic_basic/. $HOME/.oracle/instant_client/. 
hdiutil detach /Volumes/ic_basic
hdiutil attach bits/instantclient-sqlplus-macos.arm64-23.26.1.0.0.dmg -mountpoint /Volumes/ic_sqlplus
rsync -Ha /Volumes/ic_sqlplus/. $HOME/.oracle/instant_client/.
hdiutil detach /Volumes/ic_sqlplus

Download ojdbc-extensions 1.0.6 and make product.conf config file:

cd $HOME/.oracle
mkdir -p ojdbc-extensions/1.0.6
cd ojdbc-extensions
curl -sko o11.jar https://repo1.maven.org/maven2/com/oracle/database/jdbc/ojdbc11/23.26.2.0.0/ojdbc11-23.26.2.0.0.jar
JAVA_HOME=$HOME/.oracle/jdk/Home
$JAVA_HOME/bin/java -jar o11.jar get-deps --coords com.oracle.database.jdbc/ojdbc-provider-azure/1.0.6 --path 1.0.6

4. Setup SQL Developer For Entra ID Integration

Backup existing SQL Developer product.conf and install new product.conf that includes the JDK 21 home and specifies all of the ojbc-extensions libraries to load:

cd $HOME/.oracle/ojdbc-extensions
export JAVA_HOME=$HOME/.oracle/jdk/Home
echo "SetJavaHome $JAVA_HOME" > $HOME/.sqldeveloper/24.3.1/product.conf
productfile="$HOME/.sqldeveloper/24.3.1/product.conf"
cp ${productfile} ${productfile}-$(date +'%Y%m%d%H%M%S')
find 1.0.6 -name "*.jar"|sed -e "s/^/AddJavaLibFile \$HOME\/.oracle\/ojdbc-extensions\//g" >> ${productfile}

5. Add ojdbc-extensions To SQLcl For Entra ID Integration

Link the ojdbc-extensions/1.0.6 directory to sqlcl/lib/sdks/jdbc-azure so that SQLcl will load the ojbc-extensions on startup:

mkdir -p $HOME/.oracle/sqlcl/lib/sdks
ln -s $HOME/.oracle/ojdbc-extensions/1.0.6 $HOME/.oracle/sqlcl/lib/sdks/jdbc-azure

6. Make Environment File For Client Runtime

Make environment variables:

$ cat /Users/buz/.oracle/env/ora.env 
export JAVA_HOME=$HOME/.oracle/jdk/Home
export TNS_ADMIN=$HOME/.oracle/admin
PATH=$JAVA_HOME/bin:$HOME/.oracle/sqlcl/bin:$HOME/.oracle/instant_client:$HOME/Applications/SQLDeveloper.app/Contents/Resources/sqldeveloper/sqldeveloper/bin:$PATH

7. Create Oracle Database Server Name Resolution File

Make tnsnames.ora file. Note that this output is just sample output from my lab.

$ cat $HOME/.oracle/admin/tnsnames.ora
HRDB_26ai =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hrdb.dbauthdemo.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = hrdb)
    )
  )

HRDB_26ai_SSL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCPS)(HOST = hrdb.dbauthdemo.com)(PORT = 2484))
    (SECURITY=(WALLET_LOCATION=SYSTEM))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = hrdb)
    )
  )

PDB1_26ai_ENTRA = 
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCPS)(HOST=hrdb.dbauthdemo.com)(PORT=2484))
    (SECURITY=
      (SSL_SERVER_DN_MATCH=TRUE)
      (SSL_SERVER_CERT_DN="CN=hrdb.dbauthdemo.com")
      (WALLET_LOCATION=SYSTEM)
      (TOKEN_AUTH=AZURE_INTERACTIVE)
      (TENANT_ID=8d2aef2e-f5e0-46fc-86c2-82604520aea9)
      (CLIENT_ID=e5124a85-ac3e-14a4-f2ca-1ad635cf781a)
      (AZURE_DB_APP_ID_URI=https://dbauthdemo.com/e5124a85-ac3e-14a4-f2ca-1ad635cf781a)
    )
    (CONNECT_DATA=
      (SERVER=DEDICATED)
      (SERVICE_NAME=pdb1)
    )
  )

PDB1_26ai =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCP)(HOST=hrdb.dbauthdemo.com)(PORT=1521))
    (CONNECT_DATA=
      (SERVER = DEDICATED)
      (SERVICE_NAME=pdb1)
    )
  )

PDB1_26ai_SSL =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCPS)(HOST=hrdb.dbauthdemo.com)(PORT=2484))
    (SECURITY=(WALLET_LOCATION=SYSTEM))
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SERVICE_NAME=pdb1)
    )
  )

8. Test Each Client

Load environment variables:

. $HOME/.oracle/env/ora.env

SQL*Plus Local Account Over TLS

sqlplus system@PDB1_26ai

SQL*Plus Entra ID Integration Over TLS

Note that as of May, 2026, the SQL*Plus for MacOS does not yet support Entra ID integration. You will see the following error message:

ERROR: ORA-25723: Unsupported platform

However, for when SQL*Plus eventually does support Entra ID integration, here is a sample command for connecting to an Entra ID integrated database:

sqlplus /@PDB1_26ai_ENTRA

SQLcl Local Account Over TLS

Note, if you see an SLF4J errors, they can be ignored.

sql -thin system@PDB1_26ai

SQLcl Entra ID Integration Over TLS

sql -thin /@PDB1_26ai_ENTRA

SQL Developer

Either open SQL Developer from the application bar or from the command line

sqldeveloper

Then, add connection profiles with the following parameters for Entra ID integrated databases:

Name: <your_database_preferred_name>

Database Type: Oracle

Authentication Type: OS

Connection Type: TNS

Network Alias: <select desired database>

Once the SQL Developer connection is configured, double-click the connection to connect.

I hope you found this helpful!

Blessings

Upgrading ODB 23.26.1 to 23.26.2 and adding TCPS

Last week I was troubleshooting an Oracle AI 26 Database unified audit log project that required upgrading from 23.26.1 to 23.26.2  I had to iterate through the setup multiple times to make sure that I had the flow just right.  I did not know that stand alone Oracle AI 26 Databases use an out-of-place Gold Image release updates (RUs)  method rather than the traditional "opatch apply".  Given that this was new to me, I thought that I would helpful to write up my notes from that journey for my own edification but hopefully help others as well.  In addition, since this project was oriented around Entra ID integration with TCPS/TLS connections required been database client and server, those steps are included as well.

1. Make sure /etc/hosts entry is preceded by TLS cert FQDN:

grep hrdb /etc/hosts
10.0.0.33 hrdb.dbauthdemo.com hrdb.sub10241351260.odswest.oraclevcn.com hrdb

2. Make sure hostname is fully qualified using the TLS cert FQDN:

sudo hostnamectl set-hostname $(hostname -f)

3. Upload Oracle AI Database 23.26.1 (V1054592-01.zip from https://edelivery.oracle.com) and 23.26.2 (p39099680_230000_Linux-x86-64.zip  from https://updates.oracle.com/download/39099680.html) to /u01/bits:

ls -1 /u01/bits
p39099680_230000_Linux-x86-64.zip (23.26.2)
V1054592-01.zip (23.26.1)

4.  Extract and install software and setup 19c database server:

Note: manage_db.sh in OUD POC Kit at https://github.com/oudlabs/oudpockit

/u01/manage_db.sh setup --dbv 26ai

5. Set LOCAL_LISTENER because I’m running 26ai on default port and 19c on alternate port in my lab.

$ORACLE_HOME/bin/sqlplus / as sysdba
SHOW PARAMETER LOCAL_LISTENER;

ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=hrdb.dbauthdemo.com)(PORT=1521))' SCOPE=BOTH;

ALTER SYSTEM REGISTER;

6. Update 26 AI database to most recent release update (23.26.2) using the out-of-place Gold Image release update method found in the patch README file:

unzip -qo -d /u01/app/oracle/26ai/dbhome_2 /u01/bits/p39099680_230000_Linux-x86-64.zip

/u01/app/oracle/26ai/dbhome_2/runInstaller -silent -setupDBHomeAs /u01/app/oracle/26ai/dbhome_1

sudo /u01/app/oracle/26ai/dbhome_2/root.sh

/u01/app/oracle/26ai/dbhome_2/bin/dbca -silent -moveDatabase -sourceDB hrdb

7. Alter db26ai.env to change dbhome_1 (23.26.1 home) to dbhome_2 (23.26.2 home):

cat /u01/cfg/db26ai.env 

export ORACLE_BASE="/u01/app/oracle/26ai"
export ORACLE_HOME="/u01/app/oracle/26ai/dbhome_2"
export TNS_ADMIN="/u01/app/oracle/26ai/dbhome_2/network/admin"
export ORACLE_SID="hrdb"
export ORACLE_INSTANCE="/u01/app/oracle/26ai/dbhome_2"
export ORACLE_UNQNAME="hrdb"
export WALLET_ROOT="/u01/app/oracle/26ai/wallet_root"

8. Re-load the db26ai env and restart database:

. /u01/cfg/db26ai.env
/u01/manage_db.sh stop --dbv 26ai
/u01/manage_db.sh start --dbv 26ai

9. Set wallet_root and then lookup PDB1 GUID:

. /u01/cfg/db26ai.env
$ORACLE_HOME/bin/sqlplus / as sysdba
show parameter wallet_root;

alter system set wallet_root='/u01/app/oracle/26ai/wallet_root' scope=spfile;

select guid from v$containers where name = 'PDB1';

GUID
--------------------------------

512D5CFBAB67AA47E0632100000A0A24

10. Make tls directories (replace PDB GUID with value you retrieve from previous step):

mkdir -p $WALLET_ROOT/tls $WALLET_ROOT/<PDB_GUID>/tls

11. Extract wallet_root from backup with:

unzip -qo -d "$WALLET_ROOT/.." /u01/hrdb_wallet_root.zip

12. Copy wallet to PDB1 tls directory (replace PDB GUID with retrieved PDB GUID value):

rsync -Hav $WALLET_ROOT/tls/. $WALLET_ROOT/<PDB_GUID>/tls/.

13. Display wallet root contents to make sure they are valid:

$ORACLE_HOME/bin/orapki wallet display -nologo -complete  -wallet $WALLET_ROOT/tls

14. Create or update sqlnet.ora:

cat $TNS_ADMIN/sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES = (TCPS,NTS,BEQ)

SSL_CLIENT_AUTHENTICATION = FALSE

15. Create or update listener.ora:

cat $TNS_ADMIN/listener.ora
SSL_CLIENT_AUTHENTICATION = FALSE


LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hrdb.dbauthdemo.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCPS)(HOST = hrdb.dbauthdemo.com)(PORT = 2484))
      (SECURITY=(WALLET_LOCATION=/u01/app/oracle/26ai/wallet_root/tls))
    )
  )

16. Create or update tnsnames.ora:

cat $TNS_ADMIN/tnsnames.ora
HRDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hrdb.dbauthdemo.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = hrdb)
    )
  )

HRDB_SSL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCPS)(HOST = hrdb.dbauthdemo.com)(PORT = 2484))
    (SECURITY=(WALLET_LOCATION=/u01/app/oracle/26ai/wallet_root/tls))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = hrdb)
    )
  )

pdb1 =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCP)(HOST=hrdb.dbauthdemo.com)(PORT=1521))
    (CONNECT_DATA=
      (SERVER = DEDICATED)
      (SERVICE_NAME=pdb1)
    )
  )

pdb1_ssl =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCPS)(HOST=hrdb.dbauthdemo.com)(PORT=2484))
    (SECURITY=(WALLET_LOCATION=/u01/app/oracle/26ai/wallet_root/tls))
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SERVICE_NAME=pdb1)
    )
  )

17. Update /etc/oratab hrdb entry because I’m running 19c and 26ai on two separate home’s on the same host:

From:

hrdb:/u01/app/oracle/26ai/dbhome_1:N

To:

hrdb26ai:/u01/app/oracle/26ai/dbhome_2:N

18. Restart database server:

/u01/manage_db.sh stop --dbv 26ai
/u01/manage_db.sh start --dbv 26ai

19. Test that you can login to each alias in tnsnames.ora:

$ORACLE_HOME/bin/sqlplus system/Oracle123@hrdb

$ORACLE_HOME/bin/sqlplus system/Oracle123@hrdb_ssl

$ORACLE_HOME/bin/sqlplus system/Oracle123@pdb1

$ORACLE_HOME/bin/sqlplus system/Oracle123@pdb1_ssl

20. Configure PDB1 for Entra ID integration and add necessary users and roles:

$ORACLE_HOME/bin/sqlplus system/Oracle123@pdb1_ssl

ALTER SYSTEM SET IDENTITY_PROVIDER_TYPE=AZURE_AD SCOPE=BOTH;
SHOW PARAMETER IDENTITY_PROVIDER_TYPE;
ALTER SYSTEM SET IDENTITY_PROVIDER_CONFIG =
'{ 
   "application_id_uri" : "https://dbauthdemo.com/16736175-ca41-8f33-af0d-4616ade17621",
   "tenant_id" : "7f4c6e3e-a1e0-43fe-14c5-c2f051a0a3a1",
   "app_id" : "16736175-ca41-8f33-af0d-4616ade17621"
}' SCOPE=BOTH;
SHOW PARAMETER IDENTITY_PROVIDER_CONFIG;

drop USER alldbusers;
CREATE USER alldbusers IDENTIFIED GLOBALLY AS 'AZURE_ROLE=pdb.users';
SELECT USERNAME, EXTERNAL_NAME from DBA_USERS where username='ALLDBUSERS';

drop ROLE entra_dba;
CREATE ROLE entra_dba IDENTIFIED GLOBALLY AS 'AZURE_ROLE=dba.role';
GRANT pdb_dba TO entra_dba;
SELECT EXTERNAL_NAME FROM DBA_ROLES WHERE ROLE = 'ENTRA_DBA';

drop ROLE dbsession;
CREATE ROLE dbsession IDENTIFIED GLOBALLY AS 'AZURE_ROLE=session.role';
GRANT CREATE SESSION TO dbsession;
SELECT EXTERNAL_NAME FROM DBA_ROLES WHERE ROLE = 'DBSESSION';

drop USER hrapp;
CREATE USER hrapp IDENTIFIED GLOBALLY AS 'AZURE_ROLE=hr.app';
GRANT CREATE SESSION, CONNECT, SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE to hrapp;
SELECT EXTERNAL_NAME FROM DBA_ROLES WHERE ROLE = 'HRAPP';

21. Create TNS entry for PDB1 over TLS/SSL on database client (23.4 or newer).  Note that WALLET_LOCATION is set to SYSTEM, which means that the database client uses the client operating system's trust store to confirm the authenticity of the TLS certificate because the TLS certificate had been signed by a public certificate authority.

PDB1_26ai_ENTRA =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCPS)(HOST=hrdb.dbauthdemo.com)(PORT=2484))
    (SECURITY=
      (SSL_SERVER_DN_MATCH=TRUE)
      (SSL_SERVER_CERT_DN="CN=hrdb.dbauthdemo.com")
      (WALLET_LOCATION=SYSTEM)
      (TOKEN_AUTH=AZURE_INTERACTIVE)
      (TENANT_ID=8d2aef2e-f5e0-46fc-86c2-82604520aea9)
      (CLIENT_ID=e5124a85-ac3e-14a4-f2ca-1ad635cf781a)
      (AZURE_DB_APP_ID_URI=https://dbauthdemo.com/16736175-ca41-8f33-af0d-4616ade17621)
    )
    (CONNECT_DATA=
      (SERVER=DEDICATED)
      (SERVICE_NAME=pdb1)
    )
  )

22. Test Entra ID integration authentication to the database. In this example, I use SQLcl 26.1 from my MacOS laptop.

sql -thin /@pdb1_entra

Initiating the connection to the database using the interactive OAuth2 flow pops up a browser window to Entra ID to login.  Here you either enter user email and password or select an existing user.

If you selected an existing user, the next step will be dictated by the Entra ID conditional access policies based on several factors including from where your connecting.  In my case, it just prompts for the user's password.

The default Entra ID tenancy conditional access policy requires a one-time PIN.

Upon successfully authentication, Entra ID returns an access token through the browser's localhost to the client, with which the client makes another call to Entra ID with the authorization token to request an access token.

Upon receipt of the access token, the database client sends the access token over a TLS encrypted connection to the databaase server.  The Oracle database server downloads the current set of keys from Entra ID and confirms the authenticity of the access token and returns the authenticated SQL prompt to the client application.  Here, I show the returned SQL prompt along with a few queries to show what it looks like for an authenticated user of a shared user schema.

I hope you find this helpful.

Blessings!