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": {
.......
}
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
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.
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 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"
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:
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.
