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!