Background

A common question from Oracle customers is whether they can configure the Oracle Database to use:

• Two-Factor Authentication (2FA)
• Multi-Factor Authentication (MFA)
• A "Time-based One-time Password" (TOTP) code, which is usually a six-digit code generated from a hardware or software application.

The short answer is, "yes!" (though, not natively). You can implement it through other directory service technologies, namely RADIUS.

Previous blog posts discussed the setup and testing of the new Oracle 18c+ feature of Oracle "Centrally Managed Users" (CMU). It allows you to manage Oracle database users through Microsoft Active Directory (AD), and effectively offloads user management to AD. Oracle RADIUS authentication isn't part of CMU. It's a similar but slightly different implementation, and each has its own benefits and limitations.

However, by leveraging Oracle's compatibility with the RADIUS protocol and an external directory service, you can achieve true 2FA capabilities for the Oracle database (and on a per-user basis, meaning that application and/or service accounts remain unaffected).

This post describes how to implement Oracle database 2FA using FreeRADIUS. The subsequent post extends the setup to use the commercially available Cisco Duo platform instead.

RADIUS Quick Summary

The RADIUS (Remote Authentication Dial-in Service) protocol is based on "AAA:" Authentication, Authorization, and Accounting. This post is based mainly on the Authentication part. Furthermore, RADIUS can operate in two modes: synchronous mode and challenge-response (asynchronous) mode. Oracle Database is actually capable of both (and has been since at least Oracle 8i). However, the most practical and applicable form is "synchronous," and will therefore be the focus of this post.

RADIUS can authenticate against numerous sources including its own "users" flat file, LDAP directory services, Microsoft Active Directory, and others. RADIUS refers to "clients" and "NASs" (Network Access Servers, which broker the credentials). When authenticating against a RADIUS server, the Oracle Database acts as the "RADIUS client" and "NAS."

From the Oracle Database "Database Licensing Information User Manual" (part number E94254-18 dated April 2020):

Network encryption (native network encryption, network data integrity, and SSL/TLS) and strong authentication services (Kerberos, PKI, and RADIUS) are no longer part of Oracle Advanced Security and are available in all licensed editions of all supported releases of Oracle Database.

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/dblic/database-licensing-information-user-manual.pdf

However, to use RADIUS authentication, the Oracle client software must include the Advanced Security option. Therefore, a full client installation is required and the InstantClient is not sufficient. Verify Oracle client software compatibility using the adapters command.

Authentication Process

The process flow is relatively simple, yet important to understand:

1. The Oracle client attempts to connect to the database and provides credentials (username and password) along with a 2FA TOTP code.
2. Oracle Database receives this information, finds the user in the local catalog, and determines that the authentication type is EXTERNAL.
3. Oracle Database then scans the SQLNET.ORA file to determine where to authenticate EXTERNAL users.
4. Using the RADIUS connectivity information from the SQLNET.ORA file, the Oracle Database passes the credential details onto the RADIUS server.
5. The RADIUS server first authenticates the username/password with a directory service which could be a local file, Active Directory, an LDAP service, etc. This is "Primary Authentication."
6. If validated, the RADIUS server then authenticates the TOTP with the multi-factor authentication service (for example, the Google Authenticator PAM module, Cisco Duo, etc). This is "Secondary Authentication."
7. If also validated, the RADIUS server passes back the "Access-Accept" response to the Oracle Database which then accepts and completes the connection.

Here's an illustration of the process:

Prerequisite Setup

To use 2FA with a TOTP, a "RADIUS server" is required. Some common options include:

• FreeRADIUS: A RADIUS Software suite developed under the GNU General Public License: https://freeradius.org/
• Cisco Duo Security: Commercial access security software: https://duo.com/
• Okta: Cloud-based identity management: https://www.okta.com/

This post focuses on setting up and configuring Oracle Database 2FA through FreeRADIUS. FreeRADIUS is a pretty simple software suite for DBAs to set up for testing and experimentation purposes without worrying about licensing costs or third-party vendors.

The first requirement is to find a location to run FreeRADIUS. This can be a local server or a Virtual Machine (for example, created within VirtualBox or a cloud VM). For examples on how to create a cloud VM in OCI for DBA testing and experimentation, go to https://blog.pythian.com/part-1-creating-an-oracle-18c-centrally-managed-users-testbed-using-oracle-cloud-infrastructure/.

VM requirements are minimal for testing/experimentation purposes. One or two vCPUs with 2 GB of memory should be sufficient.

For a base OS, many versions of Linux are supported including CentOS 7 and Oracle Linux 7.

Oracle Database and RADIUS

For an explanation about how Oracle Database interacts with RADIUS (and specifically FreeRADIUS), see Jared Still's blog post: https://blog.pythian.com/using-freeradius-to-authorize-oracle-connections/

A key takeaway from that post: FreeRADIUS users need to be in UPPERCASE for Oracle Database compatibility.

Installing FreeRADIUS

This section summarizes the installation steps for FreeRADIUS on CentOS 7 or Oracle Linux 7. Perform all steps as "root" user, unless otherwise indicated:

If you are installing on Oracle Linux 7, add the necessary YUM repo (not required for CentOS 7):

cat << EOF > /etc/yum.repos.d/networkradius.repo
[networkradius]
name=NetworkRADIUS-7
baseurl=http://packages.networkradius.com/centos/7/repo/
gpgcheck=0
EOF

Perform other Linux prerequisites:

sed -i 's/=enforcing/=permissive/g' /etc/selinux/config
setenforce Permissive

yum -y update

Install the core FreeRADIUS package and the utilities package. The later adds testing tools:

yum -y install freeradius freeradius-utils

Adjust the Linux firewall (if required):

firewall-cmd --permanent --zone=public --add-port=1812/udp
firewall-cmd --permanent --zone=public --add-port=1813/udp
firewall-cmd --reload

Create a service for automatic-start (but don't start it yet):

systemctl enable radiusd.service

Initial RADIUS Testing

To perform a basic test of the RADIUS functionality, a test user is required. FreeRADIUS is configured based on several files, including an "authorize" file. This used to be a "users" file. "users" is now just a symbolic link to "authorize".

IMPORTANT: Directives in the "authorize" file are processed sequentially. Therefore, when testing, it's recommended you put the entries near or at the top to ensure they aren't skipped due to some preceding and/or superseding directive.

Add a test user called "testuser" with a password of "Passw0rd" to the file:

sed -i '1itestuser Cleartext-Password := \"Passw0rd\"' /etc/raddb/mods-config/files/authorize
head -4 /etc/raddb/mods-config/files/authorize

To test, it's recommended to manually start the FreeRADIUS software (instead of through the Linux service) in debug mode:

radiusd -X

From a different window/session, test the connection using the command:

radtest testuser Passw0rd localhost 0 testing123

IMPORTANT: The "testing123" argument is a "RADIUS secret" configured in the default "RADIUS client." This secret is used for secure communication between RADIUS clients and the server. See Jared Still's blog post for more information.

The result should be "Received Access-Accept ..."

FreeRADIUS Linux-PAM Setup

The next step is to set up FreeRADIUS to use Linux-PAM (Pluggable Authentication Modules).

IMPORTANT: For initial proof of concept experimentation and testing, local Linux users are authenticated using RADIUS and 2FA. Therefore, in this test scenario, the RADIUS server runs with root permissions so it can read all 2FA configuration files. In a production or hardened environment (including those using external directory services) you should configure FreeRADIUS to run in a "lower-permission" environment.

Change both user and group to root (in the test environment):

grep 'user\|group' /etc/raddb/radiusd.conf | grep = | grep -v ^[[:space:]]\#
sed -i '/user =/  s/\(\#\)\{0,1\}[[:space:]]user = radius.*/\t user = root/'   /etc/raddb/radiusd.conf
sed -i '/group =/ s/\(\#\)\{0,1\}[[:space:]]group = radius.*/\t group = root/' /etc/raddb/radiusd.conf
grep 'user\|group' /etc/raddb/radiusd.conf | grep = | grep -v ^[[:space:]]\#

Then, enable Pluggable Authentication Modules (PAM) including adding the required symbolic links:

grep pam /etc/raddb/sites-enabled/default
sed -i '/^\#[[:space:]]pam/ s/^\#//' /etc/raddb/sites-enabled/default
grep pam /etc/raddb/sites-enabled/default

ln -s /etc/raddb/mods-available/pam /etc/raddb/mods-enabled/pam
ls -l /etc/raddb/mods-enabled/pam
ls -l /etc/raddb/mods-available/pam

Change auth-type to PAM in the authorize file. Remember that file directives are processed sequentially, so place it near the top:

sed -i '2iDEFAULT Auth-Type := PAM' /etc/raddb/mods-config/files/authorize
head -5 /etc/raddb/mods-config/files/authorize

Finally, add a new Linux user on the same system for testing (remember that this user must be in uppercase for Oracle Database usage):

useradd RADUSER
passwd RADUSER
# Assume the set password is Passw0rd

Test FreeRADIUS and Linux-PAM authorization by running the FreeRADIUS server in debugging mode again:

radiusd -X

From another session/window, test a connection:

radtest RADUSER Passw0rd localhost 0 testing123

The result should again be "Received Access-Accept ..."

Google Authenticator PAM Module Setup

Google provides an example PAM module for 2FA through https://github.com/google/google-authenticator-libpam (Cisco Duo or Otka provide alternative options).

Install the Google Authenticator PAM module:

yum -y install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
yum -y install google-authenticator

Perform a basic configuration for the RADUSER local Linux user:

sudo -u RADUSER google-authenticator \
   --time-based \
   --allow-reuse \
   --no-rate-limit \
   --window-size=3 \
   --force

IMPORTANT: The command above uses configuration options optimized for testing. In an actual production environment, I recommend reviewing all options and deploying in a more more secure manner. For example, using "--disallow-reuse" and similar.

Sample output:

You can generate the TOTP codes using a mobile phone application such as "Google Authenticator" or "Authy." Scan the generated QR code with the phone app or manually enter the "secret key" into the app.

However, when testing, constantly generating codes on a phone can become cumbersome. Generating the codes using a Linux command might be more efficient.

To generate codes from the Linux bash shell, install the oathtool package:

yum -y install oathtool

Then, you can generate codes using the "secret key" from the user's ~/.google-authenticator file. For example:

oathtool --base32 --totp "`head -1 /home/RADUSER/.google_authenticator`"

Finally, you must adjust the Linux-PAM configuration to reference the new Google Authenticator PAM Module:

cat << EOF > /etc/pam.d/radiusd
#%PAM-1.0
auth       requisite    pam_google_authenticator.so forward_pass
auth       required     pam_sepermit.so
auth       substack     password-auth
auth       include      postlogin
account    required     pam_nologin.so
account    include      password-auth
session    include      password-auth
EOF

Testing Using the Local User and One-time Password

With the FreeRADIUS and Google Authenticator PAM Module implementation of 2FA, the password must be a single string that is comprised of the actual user password and the passcode. So, the format is: <password><TOTP>.

Other implementations (such as Cisco Duo) might allow for a comma between the two, or push notification to a mobile device. However, for FreeRADIUS and the Google Authenticator PAM Module, the format is one continuous string with no additional characters.

So, to test:

MY_PASSWD=Passw0rd
MY_OTP=$(oathtool --base32 --totp "`head -1 /home/RADUSER/.google_authenticator`")
radtest RADUSER ${MY_PASSWD}${MY_OTP} localhost 0 testing123

Example successful output:

Testing from the Oracle Database

The previously referenced blog post by Jared Still covers Oracle setup for FreeRADIUS. Review this post for additional information about the Oracle setup steps in this section. Repeating the steps in a simplified format:

As "root" on the FreeRADIUS server, add a FreeRADIUS client that the Oracle Databases will use to connect:

cat << EOF >> /etc/raddb/clients.conf

client oracle-db {
    ipaddr = 192.168.1.0/24
    secret = secretoracle
    shortname = oracle
    nastype = other
}
EOF

Restart the FreeRADIUS service to ensure all changes take effect:

systemctl restart radiusd.service
systemctl status radiusd.service

As the "oracle" user (or Oracle software owner) on the Oracle Database server, save the RADIUS secret to a file:

mkdir -p ${ORACLE_HOME}/network/security
echo "secretoracle" > ${ORACLE_HOME}/network/security/radius.key
chmod 600 ${ORACLE_HOME}/network/security/radius.key

Add the required entries to the SQLNET.ORA file and verify the file (add the appropriate IP address for the FreeRADIUS server):

cat <<EOF >> ${ORACLE_HOME}/network/admin/sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES=(BEQ,TCPS,RADIUS)
SQLNET.RADIUS_AUTHENTICATION=192.168.1.212
SQLNET.RADIUS_AUTHENTICATION_PORT=1812
SQLNET.RADIUS_SECRET=${ORACLE_HOME}/network/security/radius.key
EOF

If you want, you can include other parameters such as the RADIUS timeout and retries. Alternatively, for higher availability and redundancy, use the SQLNET.RADIUS_ALTERNATE parameter to specify a backup RADIUS server to use if the primary becomes unavailable.

IMPORTANT: The SQLNET.ORA configuration above is for the database server only (RDBMS home). For Oracle clients, all that's required is that the SQLNET.AUTHENTICATION_SERVICES parameter includes RADIUS. The other SQLNET.ORA parameters are not required/used in Oracle client homes.

Make sure that the database initialization parameters are set properly:

sqlplus -s / as sysdba << EOF
alter system set os_authent_prefix='' scope=spfile sid='*';
alter system set remote_os_authent=false scope = spfile sid='*';
shutdown immediate
startup
alter pluggable database all open;
EOF

Add the test user to the database:

sqlplus -s / as sysdba << EOF
alter session set container=PDB1;
create user raduser identified externally;
grant create session to raduser;
grant select on v_\$database to raduser;
EOF

Testing is as simple as providing the TOTP in the password field. Because the user was added to the database using "... IDENTIFIED EXTERNALLY," this is the Linux user's password.

Using a connection string such as <username>/<password><passcode>, here's an example of a full connection using SQLPlus:

Success! An Oracle Database connection to a database user using an external credential and 2FA!

If the password or the TOTP code is incorrect, then the standard "ORA-01017: invalid username/password; logon denied" error occurs.

Next Steps: Microsoft Active Directory

For many implementations, the next logical step is implemented using Microsoft Active Directory (AD) users instead of local Linux users. Switching this test setup to use AD instead of local users for authentication is relatively easy and involves the following:

• Connecting the FreeRADIUS server to the Microsoft Active Directory domain. For information about how to do this, see: https://blog.pythian.com/part-2-configuring-windows-and-linux-servers-for-cmu-testing/
• Adjusting the Linux-PAM module configuration to:

cat << EOF > /etc/pam.d/radiusd
#%PAM-1.0
auth       requisite    pam_google_authenticator.so forward_pass
auth       required     pam_sss.so use_first_pass
account    required     pam_nologin.so
account    include      password-auth
session    include      password-auth
EOF

• Logging to the FreeRADIUS server using the AD domain user and generating the ~/.google-authenticator configuration file using the previously described process.
• Creating the domain user in the database and testing.

Conclusion

Configuring the Oracle Database to use 2FA is something that many inquire about but few actually do. However, doing so isn't overly complex and allows you to leverage open source software such as FreeRADIUS. All that's required is a small VM to act as a RADIUS server.

In a real production environment, this server could become a single point of failure and therefore might require you to set it up with reliable infrastructure and/or in a redundant configuration (by including the SQLNET.RADIUS_ALTERNATE parameter in the SQLNET.ORA file).

This environment is simple and flexible for DBA testing. However, most enterprise users will instead want to leverage a more robust and flexible solution such as the commercial offerings from Cisco Duo or Otka. Configuring Oracle Database authentication using Cisco Duo is covered in the next post in this series.