Oracle Autonomous Database: Authenticate to Private ADB-S via IAM credentials via Private VM with no Internet Access

6 min readAug 19, 2023

Most of the customers often configure their applications & databases in private network and access them via Jump server using a database user created for the user.

There are two drawbacks with this approach:

You need to have a Public Jump Server which ain’t required as most of the customers need this only for developer team mostly, for which they can use IPSec connection from their on-prem to OCI cloud and then they can connect even to the Private Jump Server from their On-Prem via the Private Tunnel.
Also, the users being created in Database need to be deleted every time the user leaves or no user joins. If this exercise is not repeated there will be ghost users i.e., database users who will be present even when the employee moved out of Project/Organization.

In this blog, we will address both these problems. So, let us see how these two can be achieved. Brace yourself as this is going to be a bit lengthy blog.

As this is a Private VM and to access database from it we need to install Oracle Instant Client & sqlplus on it. We will follow the below method to see how to install these on a Private VM on which OS Management Service is installed and no NAT Gateway access is present.

Installing Oracle Instant Client

To access ADB-S instance via SQLPlus on VM, we should first install Oracle Instant client & Sqlplus tools on the VM. But we don’t have Internet access of any sort on this VM as there is no NAT Gateway as well and we have OS Management is enabled on the VM. So, we will install the required software from the available software list.

Please follow the below steps to complete installation of Oracle Instant Client & sqlplus tool on VM.

Navigate to compute instance on OCI console and click on the VM name, then scroll down to OS Management to View OS Management details page for the available, installed software, etc.,

NOTE: Follow this link to enable OS Management and add your VM to it from here.

2. Navigate to Software Sources under Resources and click on “Add” and then select “Oracle Instant Client 21 for Oracle Linux 8(x86_64) and click on Add.

3. Once this is added in the software is added from step2, let us install it by clicking on the software name and selecting “oracle-instantclient-basic”, “oracle-instantclient-sqlplus“ from the list of softwares and click on install post selecting the VM.

Repeat this for SQLPlus installation as well

This completes installation of sqlplus and Oracle Instant Client.

4. Now, we can test on the VM for the availability of sqlplus on VM, by running the command “sqlplus /nolog”

This completes installing sqlplus on a private VM where OS Management service is enabled and Internet/NAT gateway are not present.

Enabling IAM Authentication on ADB-S

Now, let us see how to access ADB-S using IAM credentials rather than creating users in database. But to do this we need to have access to private ADB-S as we have to run a set of commands for which I will be creating a Public Load Balancer whose backend would be my Private ADB-S. Procedure on how to create Public LB as frontend to Private ADB is explained here.

Create an IAM Group and assign users. These users will be given access to ADB-S, so that they can login to ADB-S using their IAM Database password.

2. Check the “identity_provider_type” parameter value in existing database, which will be “NONE” by default.

SELECT NAME,VALUE FROM v$parameter WHERE NAME=’identity_provider_type’;

3. Update this “identity_provider_type” variable to IAM group, so that it accepts IAM authentication

EXEC DBMS_CLOUD_ADMIN.ENABLE_EXTERNAL_AUTHENTICATION(‘OCI_IAM’);

4. Now, let us map IAM Group created in Step1 with a shared schema.

CREATE USER <SharedUser> IDENTIFIED GLOBALLY AS ‘IAM_GROUP_NAME=<IAM Group Name>’

Eg: CREATE USER db_users IDENTIFIED GLOBALLY AS ‘IAM_GROUP_NAME=db_users’;

5. Now, let us grant permissions to the user

GRANT CREATE SESSION,CONNECT,RESOURCE,UNLIMITED TABLESPACE TO <Shared User>;

E.g. GRANT CREATE SESSION,CONNECT,RESOURCE,UNLIMITED TABLESPACE TO db_users;

6. Copy ADB wallet on to the VM from where we want to access ADB-S. Download ADB wallet, unzip and place it on the VM.

7. Update the DIRECTORY value in sqlnet.ora by setting it to the VM location where Wallet is unzipped.

8. Now, we have to set the TNS_ADMIN variable to the location where unzipped ADB Wallet is present. But this will be reset upon VM session termination. Hence, we will set it permanently by updating the .bashrc file. As this is OLE VM, you can find .bashrc file under “/home/opc” directory.

9. Now, let us update this .bashrc file as shown below by adding two entries one to set TNS_ADMIN and the other to export TNS_ADMIN. You can also do echo to test it