Password-Free Authentication to Autonomous Database Using SQLcl with Cloud Shell

We recently announced that Oracle Autonomous Database (ADB) users can now sign into the database using their Oracle Cloud Infrastructure (OCI) Identity Access Management (IAM) credentials. My last blog post and accompanying video showed how to use SQL*Plus to log in to Autonomous Database with an IAM SSO token (not a password).  Now, I’ll show you how to use an IAM SSO token from SQLcl on Cloud Shell.  

With Cloud Shell, both OCI CLI and SQLcl are already installed.  Once the user invokes the cloud shell, the delegation token is available for further authentications. We can use this delegation token to get the database access token from IAM and make it available to SQLcl. Let’s go through these commands. 

Behind the scenes - how this works

Single sign-on from Cloud Shell to Autonomous Database Shared (ADB-S) involves leveraging the delegation token associated with your IAM identity to get a database access token, which is then forwarded by the SQLcl client to the ADB instance. The overall flow is described below.

1.    The OCI Command Line Interface (CLI) uses the IAM user’s existing delegation token in the OCI Cloud Shell to request the database access token for the user (db-token).  The delegation token is automatically created for the user when the OCI Cloud Shell is invoked.  IAM sends the db-token along with the private key (not shown) to OCI CLI which then stores the db-token and private key in the default file location and makes it available to trusted applications.

2.    The user runs SQLcl and connects using the ‘/’ with the connect string for the ADB-S instance. The JDBC-thin driver gets the token and private key from the default file location when using ‘slash’ login. The JDBC-thin driver signs the db-token header with the private key and sends the db-token, the header, and the signature to the database.

3.    Since the user has already been authenticated with IAM, the ADB server only needs to verify the IAM user identity in the db-token asserted by OCI IAM. 

4.    The ADB instance then requests the IAM groups associated with the user and makes sure access is compliant with IAM policies to complete the authorization step. The IAM user must map to one ADB global schema (exclusive or shared mapping). If the IAM user does not have a mapping to a schema or doesn’t have an IAM policy to allow access, the database will reject the connection.

Let’s start

We are assuming that the IAM policy and Autonomous Database are already configured and enabled for using IAM tokens. For more information, see these documents and videos:
-    Oracle Database Security Guide
-    Oracle Autonomous Database
-    Configuration overview video

First, open your OCI dashboard and open Cloud Shell

Next, follow these steps from Jeff Smith’s blog post:

1. Use the preinstalled OCI CLI to retrieve your wallet from ADB to establish a secure TLS connection.

Create a json file (getwallet.json) to pass parameters to the OCI CLI generate-wallet command

{
"autonomousDatabaseId": "ocid1.autonomousdatabase.oc1.iad.abcdefghijklmnopqrstuvwxyz123",
"file": "mywallet.zip",
"generateType": "ALL",
"password": "your!Complex#Password1"
}

alan_willi@cloudshell:~ (us-ashburn-1)$ oci db autonomous-database generate-wallet –from-json file://getwallet.json

Using this JSON parameter file prevents the password from being visible on the command line

2. As your delegation token was already installed when you started Cloud Shell, run the following command to get the database access token (db-token) from IAM. 

alan_willi@cloudshell:~ (us-ashburn-1)$ oci iam db-token get

Private key written at /home/alan_willi/.oci/db-token/oci_db_key.pem

db-token written at: /home/alan_willi/.oci/db-token/token

db-token is valid until 2022-02-16 00:36:01

The db-token and private key are stored in the default location since I didn’t specify a location.  The db-token is an IAM identity token recognized by the Oracle Cloud database.

3. Run SQLcl and point to your Autonomous Database wallet obtained from step 1.

alan_willi@cloudshell:~ (us-ashburn-1)$ sql /nolog

SQLcl: Release 21.4 Production on Wed Feb 16 00:16:06 2022

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

SQL> set cloudconfig mywallet.zip

4. Now connect using the connect string. You can find this in your tnsnames.ora file (within the wallet zip file you downloaded in step 1) or from the Autonomous Database console.

SQL> conn /@ conn /@(description= (address=(protocol=tcps)(port=1522)(host=adb.us-ashburn-1.oraclecloud.com))(connect_data=(service_name=abcdefghij_iadalaniam_medium.adb.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adwc.uscom-east-1.oraclecloud.com, OU=Oracle BMCS US, O=Oracle Corporation, L=Redwood City, ST=California, C=US")(TOKEN_AUTH=OCI_TOKEN)))

Connected.

SQL>

Notice how we added the TOKEN_AUTH=OCI_TOKEN to the end of the connect string so that the JDBC-thin driver knows that it needs to use the OCI token for authentication. 

That’s it! Most of this was configuration and setup and all you need to do in the future is renew your database token if your token has expired.

oci iam db-token get

And then open SQLcl and point to your wallet.

Verify your user name and assigned roles

Once you’ve signed in, check your session parameters in USERENV to verify your user names and assigned roles.

Read more about IAM integration in the Database Security Guide for 19c. This blog post has an accompanying video. The entire video playlist provides an operational overview along with details on server, service, and client configurations.

This integration is the next step for integrating database users with OCI Identity. Keep an eye on www.blogs.oracle.com/cloudsecurity and www.oracle.com/security to stay up-to-date on everything related to Oracle security.