Using MFA While Logging into the Oracle Database

Hello Everyone,

There is another great development on security side of Oracle database users. With latest release updates (I think 19.28 and 23.9) we can now enable Multi Factor Authentication (MFA) while a user is logging into the database. That is a fantastic security feature especially for production environments. Your database users will now provide both username/password but database will wait for mobile application confirmation to log them in. Even if your username/password is stolen somehow, they also have to access to your phone too.

So, how can we do it? I already tested a simple version and would like to share it with you. I will try to do my best and might not go to all details but I think it will be enough.

I have an OCI account and Oracle Base DB System on my cloud already and I will enable MFA for one of my PDB. My database is on 23.9 version.

There are some actions:

1. Create an integrated application on OCI
2. Set some Database parameters
3. Set sqlnet.ora parameters
4. Create a new wallet for MFA
5. Create an IAM user
6. Create database user

Let’s start.

Create an integrated application on OCI

For this part, I created a small video. you can check on youtube: https://youtu.be/FyXGCFQacLM

And please don’t make fun of it. this is my first video ever 🙂

DB Parameters

We need to set these parameters on database:

MFA_OMA_IAM_DOMAIN_URL : This is your domain URL. You can see how to get it on the youtube video above. On domain details page (Identity & Security => Domains => your default domain) Domain URL.

MFA_SMTP_HOST: is your smtp host.

MFA_SMTP_PORT: 587 (for tls)

MFA_SENDER_EMAIL_ID: your sender mail address

MFA_SENDER_EMAIL_DISPLAYNAME: Human readable name.

so this is how I set:

I use an email relay on OCI so my smtp host is Frankfurt email relay.

SQLNET.ORA Parameters

set an inbound connect timeout to wait your db users.

SQLNET.INBOUND_CONNECT_TIMEOUT=120

Create a new wallet for MFA

use your default wallet directory and create a folder called “mfa”. if you are going to do this on a PDB then create mfa folder under your pdb identifier folder.

my default wallet directory is /opt/oracle/dcs/commonstore/wallets/MYDB

my pdb identifier is 65B4E5C11AB94715C052D6189D0BD70A

so I created mfa folder as:

mkdir /opt/oracle/dcs/commonstore/wallets/MYDB/65B4E5C11AB94715C052D6189D0BD70A/mfa

Now, we need to create a new wallet hier:

Now, we will add “client ID” and “client Secret” into the wallet. both values come from Integrated Application (video shows how to capture them).

Create an IAM user

So, now we need to create an IAM user on OCI for our db user. This user shouldn’t be any group so that user cannot do anything int OCI console but by doing that user has to confirm his/her email address and install Oracle Mobile Authenticator application on his/her phone and user’s email address will be registered.

I will not create a video for that 🙂 Go to Identity & Security => Domains => your default domain => User Management. then create a user by providing name and email address. no need to add any group.

Important: After this point, you will get a verification mail to this new user. you must verify your account by clicking the link and then you must login to OCI console. during the logging in, you will be asked for MFA method. choose mobile app. then use Oracle Mobile Authenticator application on your phone to register it.

Create database user

And finally, we are all set. Now we can create a user which requires MFA to login database.

I just use another mail address of mine. Let’s connect:

Hmm, it is waiting 🙂 that is a good sign and at the same time I got this notification on my phone OMA (Oracle Mobile Authenticator app)

Voila! after clicking “Allow” button, I am able to connect to the database.

Troubleshoot

during these actions I got only one error and I got it while creating database user:

ORA-28474: Failure to enroll the user for Oracle Mobile Authenticator (OMA) Push due to

This is because of the either email address that you use in create user command is not a IAM user (you must create an IAM user with that email) or you didn’t verify your email address yet and didn’t login to the OCI to setup OMA.

 

This is a major security feature from my point of view. I strongly encourage people around me to use MFA on their databases (especially for productions). I hope this post helps you to start.

wish you all healthy, happy days.