One of my least favorite database administration activities is managing users – creating users, changing passwords, granting roles – zero fun. Even further down on my scale of fun ways to spend an afternoon is the governance that goes with user management – which users have left the organization? Do they still need the privileges they have? It’s important work, but it is a constant never-ending drumbeat of repetitive tasks that seems to always interrupt more interesting activities.
Fortunately, with a few built-in features of the database (in other words, not extra-cost options, not extra-cost software that we have to maintain) we can make someone else do that tedious work and free up our time for higher value tasks.
The solution? Microsoft Active Directory. I haven’t worked with many organizations in the past 15 years that do NOT have Active Directory running. And fortunately, the Oracle Database plays well with Active Directory and has ever since Oracle 9i. Even better, starting with Oracle Database 18c and improving in Oracle Database 19c that integration with Active Directory has become easier to set up and requires even less maintenance.
To integrate with Active Directory we are going to use two database features – Kerberos authentication, and Centrally Managed Users (note: Centrally Managed Users is an Enterprise Edition feature). When I started to write this I realized I was going WAY beyond a reasonable length for a blog, so I’m splitting this up across two different entries, one for Kerberos, the other for Centrally Managed Users. If you’d prefer to see the details on Kerberos in video form, please take a look at this YouTube video.
At the end of this post I've got a link to another video that goes into more depth on implementation and troubleshooting.
We will use Kerberos to authenticate database users. Each Active Directory domain controller is also a Kerberos Distribution Center. Kerberos can be used standalone to authenticate database users (in place of a password). A lot of times I’ll advise my clients to just use Kerberos because that does the work of placing password management on the Active Directory team’s plate, and also gets me out of having to worry about immediately deprovisioning a database user when someone leaves the company. Once the account is deleted from Active Directory, that account can no longer login to the database. We can clean it up when we get around to it. Even if we are going to configure Centrally Managed Users (CMU) I’ll usually configure Kerberos first because once that’s done, adding on CMU is just a few more minutes worth of work.
To setup Kerberos we will need to make changes in three places:
On the Database Server we configure a new network file called a krb5.conf. This file tells the Kerberos libraries where the Kerberos Distribution Center (from here on out I’m just going to call it the domain controller) is located, what port it is listening on, and which alias to send to that server. A typical krb5.conf file looks like this:
The file has three sections:
The next change we need to make on the database server is to install a Kerberos keytab file. The AD administrator needs to:
1. Create a service account for our database server – this is just a regular Active Directory user account nothing special. Because it’s a service account, I usually set “Password never expires” but follow your organizations standards
2. Have your Active Directory administrator create a keytab for you. The command they will use to do this will look like this:
ktpass -princ ORACLE/<DATABASE_SERVER_HOST_NAME>.<DATABASE_SERVER_HOST_DOMAIN>@<ACTIVE DIRECTORY DEFAULT DOMAIN> -pass <ACTIVE DIRECTORY USERS PASSWORD> -mapuser <ACTIVE_DIRECTORY_USER_NAME> -crypto ALL -ptype KRB5_NT_PRINCIPAL -out database.keytab
Copy the keytab to our database server. I usually put the keytab in $ORACLE_HOME/network/admin directory. If I’m using the new read-only $ORACLE_HOME feature I put it in $ORACLE_BASE_HOME/network/admin instead.
The last file we work with is our sqlnet.ora file. In this file we will add seven new parameters:
These parameters are described in the Database Net Services Reference guide so I’ll save space by not defining them here. One of the entries, sqlnet.kerberos5_conf, points to the krb5.conf file we discussed earlier. Another, sqlnet.kerberos5_keytab, points to the keytab file generated above.
Copy the krb5.conf file to the client workstation and update the client's sqlnet.ora with the relevant parameters:
That’s it – now we are ready to begin using Kerberos. First, we create an externally authenticated database user (identified by the Kerberos principle name) in the database and grant that user the CREATE SESSION privilege.
Now we get a Kerberos ticket from Active Directory and use it to login to the database. The "rlowenth" you see in the Kerberos principal name above, and the okinit command below, is my Active Directory username.
As you can see, we can now login to the database using Kerberos. If our client is on a Windows desktop we could just use the in-memory ticket created when we logged in and skip the okinit step. For more information, this YouTube video
from the monthly Database Security community calls talks more more about Kerberos implementation and troubleshooting – if you are not already subscribed to the monthly Database Security community calls you might want to do that now.
In my next post, I’ll take this integration with Active Directory a step farther, and enable Centrally Managed Users