Token-Based Authentication for ADB with IAM: Part 2
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.
