Token-Based Authentication for ADB with IAM: Part 1

Posted in: Cloud, Oracle, Technical Track
ADB authentication

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.

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 
[email protected]:~/.oci/instantclient_21_6# export LD_LIBRARY_PATH=/root/.oci/instantclient_21_6
[email protected]:~/.oci/instantclient_21_6# export PATH="$PATH":/root/.oci/instantclient_21_6
[email protected]:~/.oci/instantclient_21_6# export TNS_ADMIN=/root/.oci/instantclient_21_6

# Test connectivity 
[email protected]:~/.oci/instantclient_21_6# sqlplus [email protected]_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.

[email protected]:~/.oci/instantclient_21_6#  oci iam user create --name akala --description "test user"
{
"data": {
.....
}


[email protected]:~/.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
[email protected]:~/.oci/instantclient_21_6#  export user_ocid=$(oci iam user list --name akala | jq -r '.data[0]."id"')
[email protected]:~/.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 
[email protected]:~/.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:

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

[email protected]:~/.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": {
.......
}

 

Mapping ADB Users, Groups to the IAM Users and Instance/Resource Principals

Now that we have our connect privileges in place, it is time to look at DB privileges. There are multiple ways in which an IAM account can be mapped to a database account. Let’s take a look at all of these in detail.

  • IAM Users with exclusive schemas
  • IAM Groups with shared schemas
  • IAM Groups with global roles
  • Instance Principal/Resource Principal through shared mapping.

1. IAM Users with exclusive schemas

First, let us configure an IAM user by mapping it directly to an exclusive schema.

Creating the Exclusive Schema on the database

For this example, we will map the IAM user akala directly with a db schema called AISHWARYA_KALA, and then use the db-token based authentication to establish a connection to the database. So, we create the schema in the database and grant it a few privileges.

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

[email protected]:~/.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:

[email protected]:~/.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


[email protected]:~/.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.

 

Creating the Shared Schema on the database
SQL>  CREATE USER SHARED_SCHEMA IDENTIFIED GLOBALLY AS 'IAM_GROUP_NAME=CONNECT_ONLY';

User created.

SQL> grant connect to SHARED_SCHEMA;

Grant succeeded.

  • Please note that there is no exclusive schema (e.g. AISHWARYA_KALA that was created in the previous use case)  associated with the user (akala) in the database. If so, please drop the database user before proceeding further with this scenario.
    • DROP USER AISHWARYA_KALA ;
  • If we enable mapping with both shared groups & exclusive schemas for an IAM user, the GLOBAL EXCLUSIVE identification type takes precedence over GLOBAL SHARED.
Creating the IAM Group and granting it the required privileges
## Check if the IAM user akala (for which oci-cli is configuired) is in IAM Group CONNECT_ONLY
[email protected]:~/.oci/instantclient_21_6# export user_ocid=$(oci iam user list --name akala | jq -r '.data[0]."id"')
[email protected]:~/.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. 
[email protected]:~/.oci/instantclient_21_6# export compartment_id=$(oci iam user list --all | jq -r  '.data[0]."compartment-id"')
[email protected]:~/.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"

 

Get DB Token and establish a connection

As we see, we haven’t granted any privileges to the SHARED_SCHEMA. Hence the user is unable to query the tables:

[email protected]:~/.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

[email protected]:~/.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.

 

 

email
Want to talk with an expert? Schedule a call with our team to get the conversation started.

No comments

Leave a Reply

Your email address will not be published.