Token-Based Authentication for ADB with IAM: Part 1

Oracle has introduced an authentication mechanism that enables Oracle Cloud Infrastructure (OCI) users to use their Identity and Access Management (IAM) credentials to access the Autonomous Databases (ADB) transparently.

Earlier, IAM credentials could be used to access many OCI services. Still, a separate set of database credentials or other native authentication mechanisms had to be used to access the database. This integration of OCI IAM with ADB is something to be excited about. It won’t only improve security and increase operational efficiencies through centralized account management and help with quicker and streamlined development of applications.

Let’s take a look at how this can be accomplished.

There are two ways to connect to the ADB instance via IAM Credentials; the first, by using the IAM database password verifier and the second, with the IAM token. This post will  focus on IAM token-based authentication.

Diagram illustrating the DB-token based authentication for IAM User or OCI Application

Overview

As seen in the publicly-available Oracle Database 19c Security Guide, to connect using this method, a Client needs a db-token it can send to ADB Instance. The db-token will be used to authenticate and authorize the connection.

Fetching the db-token

Once a client has been authenticated using its credentials (API-key, security token, delegation token, instance principal, and resource principal can be used with OCI SDK), a call to IAM is made to request a db-token. If the client doesn’t support db-token based authentication, it can use the OCI CLI as a helper tool to facilitate this.

Verification using the db-token

This db-token is then signed and sent to the ADB for verification. The ADB instance will use the IAM public key to validate the db-token it has received. Once the authentication has been completed, ADB will get the groups of the IAM user and use them for mapping the users to the global schema and subsequently authorize the access.

However, more interestingly, we can see that fetching a db-token is not restricted to an IAM user only. OCI applications or functions can also request for db-token based on their resource (or instance) principal. Once they obtain this, the application can forward the db-token to ADB through the database client API and complete the authorization process.

This thus eliminates the need to create separate “service accounts” or an IAM user for an application to be able to connect to ADB. As long as the Instance or Resource principal is a member of an IAM dynamic group used in an IAM policy and is mapped to a database shared schema, they can be used to fetch a db-token and connect to the Autonomous Database.

Let’s take a look at the steps we need to go through to accomplish this.

Pre-requisites

For our test, we would need the following setup in place.

• An ADB DB
• OCI Cli setup, you can refer to Jared Still’s post on Installing oci cli in linux and windows.
• Download and configure the latest SQL*Plus and Instant Client on Linux

Supported Versions –  SQL*Plus and Instant Client on Linux versions 19.13 or later, and Instant Client on Linux versions 21.4 or later.

curl https://download.oracle.com/otn_software/linux/instantclient/216000/instantclient-basic-linux.x64-21.6.0.0.0dbru.zip --output instantclient-basic-linux.x64-21.6.0.0.0dbru.zip 

curl https://download.oracle.com/otn_software/linux/instantclient/216000/instantclient-sqlplus-linux.x64-21.6.0.0.0dbru.zip --output instantclient-sqlplus-linux.x64-21.6.0.0.0dbru.zip

unzip instantclient-basic-linux.x64-21.6.0.0.0dbru.zip
unzip instantclient-sqlplus-linux.x64-21.6.0.0.0dbru.zip

• Test connectivity to your ADB

        Download the ADB wallet and update the wallet_location  parameter in your sqlnet.ora to point to the wallet location and test your connectivity

# sqlnet.ora
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/root/.oci/instantclient_21_6"))) SSL_SERVER_DN_MATCH=yes

# Set env variables 
root@kala:~/.oci/instantclient_21_6# export LD_LIBRARY_PATH=/root/.oci/instantclient_21_6
root@kala:~/.oci/instantclient_21_6# export PATH="$PATH":/root/.oci/instantclient_21_6
root@kala:~/.oci/instantclient_21_6# export TNS_ADMIN=/root/.oci/instantclient_21_6

# Test connectivity 
root@kala:~/.oci/instantclient_21_6# sqlplus admin@dblab01_high
SQL*Plus: Release 21.0.0.0.0 - Production on Wed May 4 11:50:21 2022  Version 21.6.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Enter password:
Last Successful login time: Tue May 03 2022 21:56:40 +05:30
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.15.0.1.0
SQL> 
SQL> set lines 100
   col value for a10
SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME='identity_provider_type';

NAME                   VALUE
---------------------- -----
identity_provider_type  NONE

Now that we have verified that our connectivity to the ADB is working fine let’s configure our autonomous database. You can test this using an Always Free OCI Account. Also, we will be using the oci-cli to configure users, groups, and policies. This can also be done via the console.

Preparing our ADB

Enable External authentication. The External Authentication using DB Token should be enabled for the Autonomous Database. It is worth noting that only a single type of external authentication scheme can be used at a time. For ADB-Dedicated, this is enabled by default. You can use force=>TRUE to override & set OCI_IAM as the identity provider. Please note there can only be one external authentication scheme enabled at any given time.

SQL> exec DBMS_CLOUD_ADMIN.ENABLE_EXTERNAL_AUTHENTICATION(type => 'OCI_IAM',force => TRUE );
PL/SQL procedure successfully completed.

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

Set up IAM Group & Policy to allow the use of db-token

We will create a user called “akala”, and an IAM Group called “CONNECT_ONLY” and add the user to this group. We will later make a policy for this group such that anyone that belongs to this group has the privileges required to connect to the ADB Instance. This can be equivalent to the CONNECT Role in an Oracle database.

root@kala:~/.oci/instantclient_21_6#  oci iam user create --name akala --description "test user"
{
"data": {
.....
}


root@kala:~/.oci/instantclient_21_6#  oci iam group create --name CONNECT_ONLY --description "IAM Group to allow access ADB Database"
{
  "data": {
....
}

## Replace your username & group name
root@kala:~/.oci/instantclient_21_6#  export user_ocid=$(oci iam user list --name akala | jq -r '.data[0]."id"')
root@kala:~/.oci/instantclient_21_6#  export group_ocid=$(oci iam group list --name CONNECT_ONLY | jq -r '.data[0]."id"')

## Add the user to the desired group 
root@kala:~/.oci/instantclient_21_6#  oci iam group add-user --user-id "$user_ocid" --group-id "$group_ocid"
{
"data": {
.....
"inactive-status": null,
"lifecycle-state": "ACTIVE",
.....
}

Create a policy to allow access for anyone belonging to the group “CONNECT_ONLY” to ADB:

root@kala:~/.oci/instantclient_21_6#  export compartment_id=$(oci iam user list --all | jq -r  '.data[0]."compartment-id"')
root@kala:~/.oci/instantclient_21_6#  export description="Policy to allow the group CONNECT_ONLY to access database"			 
root@kala:~/.oci/instantclient_21_6#  export name=DBLab_Users_To_ADB

root@kala:~/.oci/instantclient_21_6# oci iam policy create --compartment-id $compartment_id --name $name   --description "$description" --statements '["allow group CONNECT_ONLY to use autonomous-database-family in tenancy"]'
{
  "data": {
.......
}

SQL> CREATE USER aishwarya_kala IDENTIFIED GLOBALLY AS 'IAM_PRINCIPAL_NAME=akala';

User created.

SQL> grant read on scott.emp to aishwarya_kala;

Grant succeeded.

SQL> grant connect to aishwarya_kala;

Grant succeeded.

Establishing connection using DB-TOKEN

Before establishing a connection, you will need to add the clause TOKEN_AUTH=OCI_TOKEN  to your connection string. This allows db-token based authentication. In the below example, the keyword has been appended to the tns alias dblab01_low in tnsnames.ora

root@kala:~/.oci/instantclient_21_6#   grep -i token tnsnames.ora
dblab01_low = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=****.ap-mumbai-1.oraclecloud.com))(connect_data=(service_name=*******_dblab01_low.adb.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adb.ap-mumbai-1.oraclecloud.com, OU=Oracle ADB INDIA, O=Oracle Corporation, L=Redwood City, ST=California, C=US")(TOKEN_AUTH=OCI_TOKEN)))

Fetch the db-token & establish a connection to the database. This db-token will remain valid for an hour & oci-cli will fetch the token and store it at  ~/.oci/token by default.

You can store the db-token at a custom location using the command oci iam db-token get --db-token-location   If using a custom location, you would need to specify the path explicitly n the TNS Alias description using the parameter TOKEN_LOCATION in tnsnames.ora:

root@kala:~/.oci/instantclient_21_6# oci iam db-token get
Private key written at /root/.oci/db-token/oci_db_key.pem
db-token written at: /root/.oci/db-token/token
db-token is valid until 2022-05-08 14:17:20


root@kala:~/.oci/instantclient_21_6#  sqlplus /@dblab01_low

SQL*Plus: Release 21.0.0.0.0 - Production on Sun May 8 13:09:28 2022
Version 21.6.0.0.0

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

Last Successful login time: Sun May 08 2022 13:08:42 +05:30

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.1.0

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

SYS_CONTEXT('USERENV','IDENTIFICATION_TYPE')
--------------------------------------------------------------------------------
GLOBAL EXCLUSIVE

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

SYS_CONTEXT('USERENV','AUTHENTICATION_METHOD')
--------------------------------------------------------------------------------
TOKEN_GLOBAL

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

SYS_CONTEXT('USERENV','CURRENT_USER')
--------------------------------------------------------------------------------
AISHWARYA_KALA

SQL> SELECT SYS_CONTEXT('USERENV','AUTHENTICATED_IDENTITY') FROM DUAL;

SYS_CONTEXT('USERENV','AUTHENTICATED_IDENTITY')
--------------------------------------------------------------------------------
akala

SQL> select * from scott.emp;

NAME                  EMP_ID
-------------------- ----------
John                  25
Deepak                26
Paul                  50
Ravi                  90

2. IAM Groups with Shared Schemas

In this scenario, instead of creating an exclusive schema for each user, we can create a common schema, e.g. “SHARED_SCHEMA”, & map it to the CONNECT_ONLY IAM Role. Once this has been set up, any IAM user with the group CONNECT_ONLY assigned will have the privileges to connect to the database. We can, in turn, grant the required roles to our common schema. Hence this eliminates the need to set up a dedicated database account for all employees that need privileges to generate the report.

SQL>  CREATE USER SHARED_SCHEMA IDENTIFIED GLOBALLY AS 'IAM_GROUP_NAME=CONNECT_ONLY';

User created.

SQL> grant connect to SHARED_SCHEMA;

Grant succeeded.

## Check if the IAM user akala (for which oci-cli is configuired) is in IAM Group CONNECT_ONLY
root@kala:~/.oci/instantclient_21_6# export user_ocid=$(oci iam user list --name akala | jq -r '.data[0]."id"')
root@kala:~/.oci/instantclient_21_6# oci iam user list-groups --user-id $user_ocid | jq -r '.data[]."name"'
CONNECT_ONLY
Administrators

## Check if the policy to allow access to ADB For the group is in place. 
root@kala:~/.oci/instantclient_21_6# export compartment_id=$(oci iam user list --all | jq -r  '.data[0]."compartment-id"')
root@kala:~/.oci/instantclient_21_6# oci iam policy list --compartment-id $compartment_id | jq -r '.data[]."statements"'| grep -i CONNECT_ONLY
  "allow group CONNECT_ONLY to use autonomous-database-family in tenancy"

root@kala:~/.oci/instantclient_21_6# oci iam db-token get
Private key written at /root/.oci/db-token/oci_db_key.pem
db-token written at: /root/.oci/db-token/token
db-token is valid until 2022-05-08 16:40:45

root@kala:~/.oci/instantclient_21_6# sqlplus /@dblab01_low
SQL*Plus: Release 21.0.0.0.0 - Production on Sun May 8 15:40:58 2022Version 21.6.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.1.0

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

SYS_CONTEXT('USERENV','IDENTIFICATION_TYPE')
--------------------------------------------------------------------------------
GLOBAL SHARED

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

SYS_CONTEXT('USERENV','AUTHENTICATION_METHOD')
--------------------------------------------------------------------------------
TOKEN_GLOBAL

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

SYS_CONTEXT('USERENV','CURRENT_USER')
--------------------------------------------------------------------------------
SHARED_SCHEMA

SQL> SELECT SYS_CONTEXT('USERENV','AUTHENTICATED_IDENTITY') FROM DUAL;

SYS_CONTEXT('USERENV','AUTHENTICATED_IDENTITY')
--------------------------------------------------------------------------------
akala

SQL> SELECT * FROM SESSION_ROLES;

ROLE
--------------------------------------------------------------------------------
CONNECT

SQL> select count(*) from scott.emp;
select count(*) from scott.emp
*
ERROR at line 1:
ORA-00942: table or view does not exist

We’ll look at how to map IAM Groups with global roles in Part 2 of this blog. In the meantime, make sure to to sign up for updates so you don’t miss it.