How to Guide: Configuring SQL*Plus for Single Sign-on to Oracle Autonomous Database using OCI IAM

We recently announced that users can now sign in to Oracle Autonomous Database (ADB) using their Oracle Cloud Infrastructure (OCI) Identity Access Management (IAM) credentials. You now only need to use your OCI IAM credential to access any and all of your ADB instances (instead of maintaining and memorizing accounts and credentials for each database). Managing users centrally in IAM also provides better security than managing user accounts for each database. 

In this blog post, we’ll show you how to use SQL*Plus and related utilities to single-sign-on to the Oracle Autonomous Database. The next blog post will cover SQLcl. 

Here is the overall solution architecture:

There are three key steps:

1. Using the OCI command-line interface (CLI) and API key, authenticate to OCI IAM, fetch the database token, and then put it and the private key in a known location for the database client to retrieve and use. Note that the database token is only valid for one hour, and may need to be refreshed. Alternatively, a user's currently valid security (session) token can also be used.
2. When you sign in with SQL*Plus with a slash (/), the instant client will get the database token from the given location, use the private key to sign the token, and send the token to the database.
3. The database will verify the token and retrieve your IAM groups. You will either be exclusively mapped to a given database schema (one-to-one mapping) or mapped to a database schema shared with users in a given IAM group (shared schema).

Prerequisite Steps

The DBA and IAM administrators need to complete the following tasks before you can use SQL*Plus to access the database. 

• ADB-S DBA executes a PL/SQL command to enable IAM integration – watch video
• IAM admin creates IAM policy to allow users to access Autonomous Databases using database tokens – watch video
• DBA creates global schemas and maps them to IAM users or groups – watch video

The following steps then need to be taken by every user:

Configuration Step 1. Install OCI CLI on the user’s machine

OCI CLI has many functions, but here we will focus on using the OCI CLI to fetch and store database tokens from IAM using your API key.

As Instant Client for IAM token use is only available on Linux (watch for information for other database clients in the future), start with the OCI CLI  “Quickstart” page to install and configure CLI on your Linux machine. Note that the methods for installing OCI CLI in Linux 8 and Linux 7 are slightly different (Linux 8 uses dnf, and Linux 7 uses yum), so make sure you are using the instructions for the correct Linux version. 

Verify it has been installed correctly by running:

# oci --version

The OCI CLI needs a config file containing credential information to connect with OCI IAM. You can create this file manually or use the setup dialog with CLI as follows:

# oci setup config

This will set up your initial config file and prompt you to create an API key. The API key is a private/public key pair that you use to authenticate to OCI (including IAM). 

Configuration Step 2. Upload public key to your OCI IAM profile

Once you have created your OCI CLI config file and API key, upload your public key into your OCI IAM user account.

Configuration Step 3. Download SQL*Plus, Instant Client, and ADB-S wallet

Your next step is to download and configure SQL*Plus, the Instant Client, and the wallet. These are the same steps as you did before with autonomous databases:

• Download your ADB-S database connection zip file and unzip it into your local wallet location
• Download and configure SQL*Plus and Instant Client,
  • Make sure you use the latest Database Linux SQL*Plus/Instant Client versions
  • Specify the wallet location in the sqlnet.ora file that came with the ADB-S zip file
  • Set your TNS_ADMIN environment variable to your sqlnet.ora location

Ensure SQL*Plus, the Instant Client, and your ADB-S connection information are configured correctly by signing in as a database user (for example, ADMIN if you’re a DBA).

Configuration Step 4. Configure Instant Client to accept database tokens

You will need to tell the Instant Client to look for the database OCI IAM token when you log in using a “/” slash. For that, add TOKEN_AUTH=OCI_TOKEN to the connect string in tnsnames.ora or to the connect string in SQL*Plus. If all your database connections use an IAM token, you can optionally set TOKEN_AUTH=OCI_TOKEN in sqlnet.ora instead of setting it in each connect string. Here’s an example of the new parameter in tnsnames.ora:

connectname_high=(description= (retry_count=20)(retry_delay=3) (address=(protocol=tcps)(port=1522)(host=adb.us-phoenix1.oraclecloud.com)) (connect_data=(service_name=aaaaaaaaa_bbbbbbbb_high.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)))

This parameter tells the Instant Client to retrieve a database token from the default location (~/.oci/token) on your Linux machine. Another parameter (TOKEN_LOCATION) tells the Instant Client to get the database token from a non-default location. You can specify the non-default location in OCI CLI through the --db-token-location parameter.

Ok, now the setup is all done, and we can now get to the actual use by SQL*Plus.

To retrieve the database token, go back to the command line and type:

17:34 $ oci iam db-token get
Private key written at /Users/testuser/.oci/db-token/oci_db_key.pem
db-token written at: /Users/testuser/.oci/db-token/token
db-token is valid until 2021-06-29 18:34:56

Full details about this new CLI command are documented, including additional parameters. When running db-token get without any parameters, OCI CLI uses the default location to store the database token and private key (~/.oci/token).

Remember the database token is a “Proof of Possession” token and is valid for one hour. Existing database connections started with a valid token will not be terminated when the database token expires, but any new connection will require a new database token.

Run SQL*Plus

Now that you have your database token, sign in to ADB using the IAM database token retrieved using your API key.

# sqlplus /nolog
SQL> conn /@connectname_high
Connected.

Once you’ve signed in, check your USERENV and other parameters:

SQL> SELECT SYS_CONTEXT('USERENV','CURRENT_USER') FROM DUAL;
SYS_CONTEXT('USERENV','CURRENT_USER')
------------------------------------------------------------
USER_SHARED

CURRENT_USER will show the schema name you are mapped to. This could be an exclusive mapping where only your IAM user account is mapped to this database schema (user). Or it can be a shared schema where everyone in an IAM group will also be mapped to the same database schema. In this case, USER_SHARED is a shared schema.

SQL> SELECT * FROM SESSION_ROLES;
ROLE
------------------------------------------------------------
SR_DBA_GLOBAL_ROLE

SESSION_ROLES will list the roles you have from both the mapped schema and any global role you were also mapped to. Your IAM administrator controls this through group membership. I’m a member of an IAM group that maps to the SR_DBA_GLOBAL_ROLE.

SQL> SELECT SYS_CONTEXT('USERENV','AUTHENTICATED_IDENTITY') FROM DUAL;
SYS_CONTEXT('USERENV','AUTHENTICATED_IDENTITY')
------------------------------------------------------------
alan.williams@oracle.com

AUTHENTICATED_IDENTITY will be your IAM username.

SQL> SELECT SYS_CONTEXT('USERENV','ENTERPRISE_IDENTITY') FROM DUAL;
SYS_CONTEXT('USERENV','ENTERPRISE_IDENTITY')
------------------------------------------------------------
ocid1.user.oc1..aaaaaaaabbbbbccccccdddddeeeee

ENTERPRISE_IDENTITY will be your IAM user OCID

SQL> SELECT SYS_CONTEXT('USERENV','AUTHENTICATION_METHOD') FROM DUAL;
SYS_CONTEXT('USERENV','AUTHENTICATION_METHOD')
------------------------------------------------------------
TOKEN_GLOBAL

AUTHENTICATION_METHOD will show TOKEN_GLOBAL indicating a token was used to access the database, and you are mapped to a global database schema (user)

SQL> SELECT SYS_CONTEXT('USERENV','IDENTIFICATION_TYPE') FROM DUAL;
SYS_CONTEXT('USERENV','IDENTIFICATION_TYPE')
------------------------------------------------------------
GLOBAL SHARED

IDENTIFICATION_TYPE will show if you are mapped exclusively to this database schema (GLOBAL EXCLUSIVE) or mapped to a schema shared by other IAM users (GLOBAL SHARED).

What’s Next?

Most of the steps described above were for one-time configuration setup. For further logins, just do the following:

To renew your database token:

# oci iam db-token get

To login to SQL*Plus:

# sqlplus /nolog
SQL> /@connectname_high

In this blog post, we used the API key to get the IAM token, but a security token could also be used to retrieve the database token. Read more about this and other IAM integration topics in the Database Security Guide for 19c. 

This blog post has an accompanying video. The entire video playlist is also available to provide an operational overview along with details on server and service configuration.

This integration is the first step towards fully integrating database users with OCI IAM. Keep an eye on the Cloud Security blog for more announcements.