Note:

Use Oracle Cloud Infrastructure Identity and Access Management Authentication with Oracle Autonomous Database

Introduction

An Oracle Cloud Infrastructure Identity and Access Management (OCI IAM) database password allows an IAM user to log in to an Oracle Autonomous Database instance as Oracle Database users typically log in with a user name and password. The user enters their IAM user name and IAM database password. An IAM database password is a different password than the Oracle Cloud Infrastructure (OCI) Console password. Using an IAM user with the password verifier you can log in to Autonomous Database with any supported database client.

OCI IAM is for making sure that only the right people can access an organization’s data and resources. You can configure the Oracle Database to use OCI IAM authentication and authorization to allow IAM users to access the database with IAM credentials. Centralizing user and credential management in IAM improves security, manageability, and user experience for the database users.

Audience

OCI IAM professionals and Administrators.

Objective

Configure Oracle Autonomous Database to use OCI IAM authentication and authorization to allow IAM users to access an Oracle Autonomous Database with IAM credentials.

Prerequisites

Task 1: Create Database Groups, Users and Policies

  1. Navigate to Identity & Security, Groups and then click Create Group. Provide the following details to create groups: DB_Admins and DB_Users.

    Database Create Groups

    • Create first group with Name DB_Admins and Description DB_Admins, then click Create.

      Database Create Groups

    • Create second group with Name DB_Usersand Description DB_Users, then click Create.

      Database Create Groups

  2. Navigate to Identity, Policy and enter following details, then click Create Policy.

    • Name: ADB-Access-Policy

    • Description: ADB-Access-Policy

    • Compartment: Ensure the correct compartment is selected

    • Policy Builder: Select the Show manual editor option

      allow group DB_Users, DB_Admins to use database-connections in compartment <compartment-name>

allow group DB_Users, DB_Admins to use autonomous-database-family in compartment <compartment-name>

      Database Create Policy

  3. Navigate to Identity, Users and enter the following details to create two test users - testuser1 and testuser2 and then click Create.

    • Select the User Type: IAM User

    • Username: testuser1

    • Description: testuser1

    • Email: testuser1@demo.com

    • Confirm Email: testuser1@demo.com

      Database Create Users

  4. Add testuser1 to DB_Users group.

    Database Create Users

  5. Repeat Step 3 to set up testuser2.

    • Select the User Type: IAM User

    • Username: testuser2

    • Description: testuser2

    • Email: testuser2@demo.com

    • Confirm Email: testuser2@demo.com

      Database Create Users

  6. Add testuser2 to DB_Admins and DB_Users groups.

    Database Create Users

    Database Create Users

Task 2: Set the IAM Database Password for Users

  1. Navigate to Identity and Users.

  2. Select testuser1. The Database username is testuser1. Select Database Password and enter the following details, then click Create Database Password.

    • Description: password

    • Password: password

    • Confirm Password: password

      Set IAM Database Password

  3. Select testuser2. The Database username is testuser2. Select Database Password and enter the following details given below then click Create Database Password.

    • Description: password

    • Password: password

    • Confirm Password: password

    Now you have successfully created the database passwords for testuser1 and testuser2.

Task 3: Configure database for Create Global User Schema Mappings and Role Mappings for DB_Users and DB_Admins groups

  1. In OCI, navigate to Autonomous Database, Database Actions and SQL. The SQL session is created.

    Autonomous Database

    Autonomous Database

  2. Verify the Current External Identity Provider for Autonomous Database using the below query.

    SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME='identity_provider_type';

    Verify the External Identity Provider

    Note: It is currently set to None.

  3. Configure OCI IAM as an External Identity Provider for Autonomous Database using the below query.

    EXEC DBMS_CLOUD_ADMIN.ENABLE_EXTERNAL_AUTHENTICATION('OCI_IAM');

    Set OCI IAM as the Identity Provider

    SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME='identity_provider_type';

    Verify the External Identity Provider

    Note: It is currently set to OCI_IAM.

  4. Configure database for create global user schema mappings and role mappings for DB_Users and DB_Admins groups in SQL.

    CREATE USER global_user IDENTIFIED GLOBALLY AS 'IAM_GROUP_NAME=DB_Users';

CREATE ROLE global_role IDENTIFIED GLOBALLY AS 'IAM_GROUP_NAME=DB_Admins';

grant CREATE SESSION to global_user;

grant DWROLE to global_role;

grant CREATE SESSION to global_role;

    Configure database schema role mapping

Task 4: Download the SQL*Plus Database Client

  1. Ensure you install the latest release updates for your Oracle Database client releases 19c - 19.20.0 based on the Operating System. In this tutorial, we are using Windows OS.

  2. Download the SQL*Plus DB client, see Install SQL*Plus DB client.

Task 5: Download Wallet

  1. Navigate to Oracle Cloud, Databases, Autonomous Database and select the Autonomous Database provisioned then click the Database Connection.

    Download Wallet

  2. Enter a password and download the wallet.

    Download Wallet

Task 6: Update the Wallet location in sqlnet.ora and set the TNS Admin variable

  1. Update the downloaded wallet location path in the sqlnet.ora file.

    Download Wallet

    Download Wallet

  2. Set up the environment variable: TNS_Admin with the wallet location path.

    Download Wallet

Task 7: Connect to DB using PowerShell

  1. Open Windows PowerShell and enter the following query.

    sqlplus /nolog

  2. Demonstrate the Local Authentication for Administrator User.

    conn admin/password@adwtest_high

    exit

    Connected as Admin User

    Now you are successfully connected to Autonomous Database as the Administrator.

  3. Demonstrate Password-Based Authentication for testuser1 and testuser2 and validate Users and Roles in the Database. Execute the below SQL query to connect to the Autonomous Database as testuser2.

    conn testuser1/password@adwtest_high

    You are connected as testuser1. Execute the below SQL queries one by one and observe the output to validate the User and Roles.

    SELECT SYS_CONTEXT ('USERENV','CURRENT_USER') FROM DUAL;

    SELECT SYS_CONTEXT ('USERENV','AUTHENTICATED_IDENTITY') FROM DUAL;

    SELECT SYS_CONTEXT ('USERENV','ENTERPRISE_IDENTITY') FROM DUAL;

    SELECT * FROM SESSION_ROLES;

    exit

    Connected as testuser1

    Now you have successfully tested the connection to the Autonomous Database as testuser1.

  4. Proceed with the below SQL query to connect to the Autonomous Database as testuser2.

    conn testuser2/password@adwtest_high

    You are connected as testuser2. Now, execute the below SQL queries one by one and observe the output to validate the User and Roles.

    SELECT SYS_CONTEXT ('USERENV','CURRENT_USER') FROM DUAL;

    SELECT SYS_CONTEXT ('USERENV','AUTHENTICATED_IDENTITY') FROM DUAL;

    SELECT SYS_CONTEXT ('USERENV','ENTERPRISE_IDENTITY') FROM DUAL;

    SELECT * FROM SESSION_ROLES;

    exit

    Connected as testuser2

    Now you have successfully tested the connection to the Autonomous Database as testuser2.

Acknowledgments

More Learning Resources

Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.

For product documentation, visit Oracle Help Center.