Skip to content

Insight and analysis of technology and business strategy

Token-Based Authentication for ADB with IAM: Part 2

Aishwarya Kala date icon
Tags: , , , , , ,

In Part 1, we discussed the essentials of DB token-based authentication and looked at examples to set up authentication for IAM users with exclusive or shared schema mappings. In this second  part, we’ll look at use cases for setting up authentication for IAM Groups with global roles.

 

3. IAM Groups with Global Roles

We currently have a setup where any IAM user added to the IAM Group CONNECT_ONLY will have the privileges to connect to ADB using db-token based authentication. However, we haven’t granted any grants to these users. Now suppose we have IAM Users who need to be given the privilege to read data from a few tables to generate sales reports. For this, we could create a global role called SALES_REPORT on our Database, and along with the IAM group CONNECT_ONLY, we could add the IAM users to the SALES_REPORT role. This will grant them the privileges required to generate reports.

Similarly, we could create an IAM group for DB admins, which is mapped with a global role with DBA privileges. Then the IAM users who are database administrators for your organization could be added to this IAM group along with the CONNECT_ONLY group, which would grant them DBA privileges. This eliminates the need to set up a dedicated database account with DBA roles separately on each database for each administrator. Also, every time a new DBA joins your team, all you need to do is add their IAM user to the required IAM groups.

Let’s take a look at both of these examples.

Creating an external Role in the database

 

SQL>  CREATE ROLE SALES_REPORT identified globally as 'IAM_GROUP_NAME=SALES_REPORT';

Role created.

SQL> grant select on  scott.emp to SALES_REPORT;

Grant succeeded.

Creating the IAM Group and granting it the required IAM User

## Create IAM Group SALES_REPORT

root@kala:~/.oci/instantclient_21_6# oci iam group create --name SALES_REPORT --description "IAM Group grant privileges to pull Sales Report"

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 SALES_REPORT | 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"


##The Verify IAM user should be in CONNECT_ONLY and SALES_REPORT IAM Groups 
root@kala:~/.oci/instantclient_21_6# oci iam user list-groups --user-id $user_ocid | jq -r '.data[]."name"'
CONNECT_ONLY
SALES_REPORT
Administrators


## Check if the policy to allow access to ADB For the group is in place via CONNECT_ONLY Group 
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 autonomous-database-family
"allow group CONNECT_ONLY to use autonomous-database-family in tenancy"

 

Get DB Token and establish a connection

The role SALES_REPORT has read-only privileges, and hence the user cannot modify the records. Also, note the session roles view that can list the roles that have been granted to the connected user:

# Fetch the db-token 

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 23:38:59


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

SQL*Plus: Release 21.0.0.0.0 - Production on Sun May 8 22:39:02 2022
Version 21.6.0.0.0

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

Last Successful login time: Sun May 08 2022 22:38:05 +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 SHARED

SQL> SELECT * FROM SESSION_ROLES;

ROLE
--------------------------------------------------------------------------------
CONNECT
SALES_REPORT

SQL> select count(*) from scott.emp;

  COUNT(*)
----------
         4

SQL> update scott.emp set name ='John';
update scott.emp set name ='John'
             *
ERROR at line 1:
ORA-01031: insufficient privileges

Groups that can be used across multiple ADBs

Now let’s take this a step further and create an IAM group mapped to multiple ADB instances.

For this example, we have 2 ADB databases, DBLAB01 and DBLAB02. We’ll create a database role called IAM_DBAS_ROLE on both the ADBs. This role will have the required privileges to manage the PDB instance, and then we will map this role to a global IAM Role called DB_ADMINS. We will use our common schema “SHARED_SCHEMA” in the database as our landing schema for the admins.

Create a DB admins role in the database DBLAB01:

 

SQL> create role IAM_DBAS_ROLE identified globally as 'IAM_GROUP_NAME=DB_ADMINS';

Role created.

SQL> Grant pdb_dba to IAM_DBAS_ROLE;

Grant succeeded.

Preparing the database DBLAB02

Since we haven’t used this ADB instance with OCI_IAM, we will need to enable the external authentication with OCI_IAM, and create a landing schema for the connections and the required roles.

Also, you will need to download the regional wallet and confirm that the WALLET_LOCATION path in sqlnet.ora is correct and that the TNS_ALIAS has the (TOKEN_AUTH=OCI_TOKEN)

 

## Connect as Admin user 
 
SQL> select name from v$pdbs;

NAME
--------------------------------------------------------------------------------
GAD9DCE9730527A_DBLAB02


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

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

User created.

SQL> grant connect to SHARED_SCHEMA;

Grant succeeded.


SQL> create role IAM_DBAS_ROLE identified globally as 'IAM_GROUP_NAME=DB_ADMINS';

Role created.

SQL> Grant pdb_dba to IAM_DBAS_ROLE;

Grant succeeded.

 

Creating the IAM Group and granting it to the desired IAM User

We are adding our IAM user “akala”  to the group DB_ADMINS. This should grant akala administrative privileges on both the ADBs.

 

## Create IAM Group DB_ADMINS

root@kala:~/.oci/instantclient_21_6# oci iam group create --name DB_ADMINS --description "IAM Group that grant privileges to Database Administrators"

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 DB_ADMINS | 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"


##The Verify IAM user should be in CONNECT_ONLY and DB_ADMINS IAM Groups
root@kala:~/.oci/instantclient_21_6# oci iam user list-groups --user-id $user_ocid | jq -r '.data[]."name"' 
CONNECT_ONLY
SALES_REPORT
DB_ADMINS
Administrators 

## Check if the policy to allow access to ADB For the group is in place via CONNECT_ONLY Group
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 autonomous-database-family
"allow group CONNECT_ONLY to use autonomous-database-family in tenancy"

Get DB Token and establish a connection to the first ADB

Validating the TNS Alias for both the autonomous databases:

root@kala:~/.oci/instantclient_21_6#  grep -i OCI 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=g********_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)))
dblab02_low = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=****.ap-mumbai-1.oraclecloud.com))(connect_data=(service_name=g********_dblab02_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)))

Test connectivity and privileges to the first ADB (DBLAB01):

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-09 00:18:38
root@kala:~/.oci/instantclient_21_6# sqlplus /@dblab01_low

SQL*Plus: Release 21.0.0.0.0 - Production on Sun May 8 23:18:44 2022
Version 21.6.0.0.0

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

Last Successful login time: Sun May 08 2022 23:09:04 +05:30

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

SQL> select name from v$pdbs;

NAME
--------------------------------------------------------------------------------
GAD9DCE9730527A_DBLAB01

SQL> SELECT * FROM SESSION_ROLES where role='PDB_DBA';

ROLE
--------------------------------------------------------------------------------
PDB_DBA

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

 

Similarly, test connectivity and privileges to the second ADB (DBLAB02):

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

SQL*Plus: Release 21.0.0.0.0 - Production on Sun May 8 23:26:57 2022
Version 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 name from v$pdbs;

NAME
--------------------------------------------------------------------------------
GAD9DCE9730527A_DBLAB02

SQL> SELECT * FROM SESSION_ROLES where role='PDB_DBA';

ROLE
--------------------------------------------------------------------------------
PDB_DBA

4. Instance Principal/Resource Principal through shared mapping

OCI applications can use OCI resource principal to connect to an ADB Instance. Similar to IAM user-based authentication process, the application using an OCI SDK can retrieve and then forward the retrieved db-token to the ADB Instance for verification and authorization. This enables the application to talk directly with the ADB without separate DB accounts for the applications.

here are a couple of things worth noting

  • When using Instance/Resource Principals, you can only use dynamic groups.
  • Also, Instance/Resource Principals can only be mapped through shared mapping.

Conclusion

In this series of blogs, we have explored how IAM authentication can be leveraged to connect directly to an ADB Instance, eliminating the need for dedicated database credentials to be maintained.

This thus enables the IAM administrators to manage all the ADB users centrally, by adding/removing users to the appropriate IAM groups as required. From a user perspective, the users no longer need to maintain individual passwords for each ADB instance, and from an application perspective, if supported, it can log on directly to an ADB without the need for dedicated credentials.

I hope you found this post helpful. Feel free to share any questions or feedback in the comments, and make sure to sign up for updates so you don’t miss the next post.

 

 
Aishwarya Kala

Top Categories

  • There are no suggestions because the search field is empty.

Tell us how we can help!
dba-cloud-services
Upcoming-Events-banner