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:
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
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.
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
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.
Once you’ve signed in, check your session parameters in USERENV to verify your user names and assigned roles.
USERENV parameter |
Description |
Example |
AUTHENTICATED_IDENTITY |
Your IAM console username |
|
ENTERPRISE_IDENTITY |
Your IAM user unique OCID |
ocid1.user.oc1..abcd1234… |
AUTHENTICATION_METHOD |
This returns the type of credential used to access the database |
TOKEN_GLOBAL |
IDENTIFICATION_TYPE |
Describes how the database schema was created and whether it was an exclusive or shared mapping |
GLOBAL SHARED |
CURRENT_USER |
The name of the database user (schema) whose privileges are currently active |
USER_SHARED |
SESSION_ROLES |
This describes the roles that are currently enabled to the user |
SR_DBA_GLOBAL_ROLE |
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.
Alan Williams is the Product Manager responsible for authentication and authorization technologies in the Oracle Database group. Prior to joining the Oracle Database Security team, he was involved in government and military projects involving high-security architecture, design and processes along with ITIL implementation. Alan is a 30-year veteran of the IT industry and has certifications in ITIL v3 Foundation and DOD Architecture Foundation and is a United States Air Force veteran. He earned his Bachelor’s degree from the Massachusetts Institute of Technology and Masters of Business Administration from the Rensselaer Polytechnic Institute
Previous Post
Next Post