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.
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:
- The Oracle client attempts to connect to the database and provides credentials (username and password) along with a 2FA TOTP code.
- Oracle Database receives this information, finds the user in the local catalog, and determines that the authentication type is EXTERNAL.
- Oracle Database then scans the SQLNET.ORA file to determine where to authenticate EXTERNAL users.
- Using the RADIUS connectivity information from the SQLNET.ORA file, the Oracle Database passes the credential details onto the RADIUS server.
- 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.”
- 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.”
- 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: