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.
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.
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.
## 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"
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
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.
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.
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.
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"
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
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
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.
Ready to optimize your Oracle Database for the future?