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:
◉ Database Server
◉ Client Workstation
◉ Active Directory
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:
[libdefaults]
default_realm = DBSECLABS.COM
clockskew = 6000
passwd_check_s_address = false
noaddresses = true
forwardable = yes
[realms]
DBSECLABS.COM = {
kdc = DBSECLABS.COM:88
}
[domain_realm]
DBSECLABS.COM = DBSECLABS.COM
.DBSECLABS.COM = DBSECLABS.COM
dbseclabs.com = DBSECLABS.COM
.dbseclabs.com = DBSECLABS.COM
The file has three sections:
◉ libdefaults – parameters that control how Kerberos is going to behave
◉ realms – for each Kerberos domain, where are the domain controllers? What port do they listen for Kerberos requests on (almost always port 88) pro tip: Notice I don’t actually have a server name or IP address, instead I use the Active Directory domain name, and DNS sends me to whichever domain controller is available. This builds in fault tolerance and lets me survive the constant maintenance of domain controllers
◉ domain_realm – these are aliases for the domains listed in the realms section. The value on the left side of the equal sign is the alias, the value on the right is the entry in the realms section that this alias should direct requests to. Kerberos is case sensitive, so I list my aliases in both upper and lower case because I never can be sure what format a client will use.
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:
#Kerberos Parameters
SQLNET.AUTHENTICATION_SERVICES=(beq,kerberos5pre,kerberos5)
SQLNET.FALLBACK_AUTHENTICATION=TRUE
SQLNET.AUTHENTICATION_KERBEROS5_SERVICE=ORACLE
SQLNET.KERBEROS5_CONF=/oracle/19/dbhome_1/network/admin/krb5.conf
SQLNET.KERBEROS5_CLOCKSKEW=6000
SQLNET.KERBEROS5_CONF_MIT=TRUE
#Following parameter is server-side only
SQLNET.KERBEROS5_KEYTAB=/oracle/19/dbhome_1/network/admin/database.keytab
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:
#Kerberos Parameters
SQLNET.AUTHENTICATION_SERVICES=(kerberos5)
SQLNET.FALLBACK_AUTHENTICATION=TRUE
SQLNET.AUTHENTICATION_KERBEROS5_SERVICE=ORACLE
SQLNET.KERBEROS5_CONF=/oracle/19/dbhome_1/network/admin/krb5.conf
SQLNET.KERBEROS5_CLOCKSKEW=6000
SQLNET.KERBEROS5_CONF_MIT=TRUE
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.
SQL> create user RUSS identified externally as 'rlowenth@DBSECLABS.COM';
User created.
SQL> grant create session to RUSS;
Grant succeeded.
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.
[oracle@russ-test ~]$ okinit rlowenth
Kerberos Utilities for Linux: Version 19.0.0.0.0 - Production on 18-SEP-2020 22:41:01
Copyright (c) 1996, 2019 Oracle. All rights reserved.
Configuration file : /opt/oracle/product/19c/dbhome_1/network/admin/krb5.conf.
Password for rlowenth@DBSECLABS.COM:
[oracle@russ-test ~]$ oklist
Kerberos Utilities for Linux: Version 19.0.0.0.0 - Production on 18-SEP-2020 22:41:07
Copyright (c) 1996, 2019 Oracle. All rights reserved.
Configuration file : /opt/oracle/product/19c/dbhome_1/network/admin/krb5.conf.
Ticket cache: FILE:/tmp/krb5cc_1001
Default principal: rlowenth@DBSECLABS.COM
Valid starting Expires Service principal
09/18/20 22:41:05 09/19/20 08:41:05 krbtgt/DBSECLABS.COM@DBSECLABS.COM
renew until 09/19/20 22:41:01
[oracle@russ-test ~]$ sqlplus /@pdb1
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Sep 18 22:41:20 2020 Version 19.8.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production Version 19.8.0.0.0
SQL> select sys_context('userenv','authentication_method') from dual;
SYS_CONTEXT('USERENV','AUTHENTICATION_METHOD')
---------------------------------------------------------
KERBEROS
Posts
0 comments:
Post a Comment