During an EUS authentication, there are 2 communication channels: one between the sql client and the database, and another one between the database and the LDAP server. In a previous post, I explained that the database-to-OUD communication can be authenticated either through user/password or SSL.
The sql client-to-database connection also supports multiple authentication methods:
- user/password
- SSL
- Kerberos
In this post, I will explain how to configure Kerberos authentication. I am assuming that you already have a Kerberos server up and running.
- Create a Kerberos Principal for the Database:
The database service needs to have a corresponding Kerberos principal in the Kerberos server. If your Kerberos server is running on a Linux machine, this can be done with the kadmin utility. In my example, the kerberos administrator is kws/admin and creates a principal for the database having ORACLE_SID=orcl11g, running on host dbhost, with a Kerberos realm EXAMPLE.COM:
$ kadmin -p kws/admin
Authenticating as principal kws/admin with password.
Password for kws/admin@EXAMPLE.COM:
kadmin: add_principal -randkey orcl11g/dbhost
WARNING: no policy specified for orcl11g/dbhost@EXAMPLE.COM; defaulting to no policy
Principal "orcl11g/dbhost@EXAMPLE.COM" created.
kadmin: ktadd -k /tmp/orcl11g.keytab orcl11g/dbhost@EXAMPLE.COM
Entry for principal orcl11g/dbhost@EXAMPLE.COM with kvno 2, encryption type AES-256 CTS mode with 96-bit SHA-1 HMAC added to keytab WRFILE:/tmp/orcl11g.keytab.
Entry for principal orcl11g/dbhost@EXAMPLE.COM with kvno 2, encryption type ArcFour with HMAC/md5 added to keytab WRFILE:/tmp/orcl11g.keytab.
Entry for principal orcl11g/dbhost@EXAMPLE.COM with kvno 2, encryption type Triple DES cbc mode with HMAC/sha1 added to keytab WRFILE:/tmp/orcl11g.keytab.
Entry for principal orcl11g/dbhost@EXAMPLE.COM with kvno 2, encryption type DES cbc mode with CRC-32 added to keytab WRFILE:/tmp/orcl11g.keytab.
kadmin: exit
The add_principal command creates a principal, and the ktadd command creates and stores the secret key in the file /tmp/orcl11g.keytab.
- Copy the keytab to the database host:
$ scp /tmp/orcl11g.keytab oracle@dbhost:/path/to/orcl11g.keytab
- Create a Kerberos principal for the EUS user:
The user that will use sqlplus also needs to be provisioned in the KDC. In the example, the user principal name is eususer@EXAMPLE.COM:
$ kadmin -p kws/admin
Authenticating as principal kws/admin with password.
Password for kws/admin@EXAMPLE.COM:
kadmin: add_principal eususer
WARNING: no policy specified for eususer@EXAMPLE.COM; defaulting to no policy
Enter password for principal "eususer@EXAMPLE.COM":
Re-enter password for principal "eususer@EXAMPLE.COM":
Principal "eususer@EXAMPLE.COM" created.
kadmin: exit
$
- Configure the database to allow Kerberos authentication:
By default the database supports only user/password authentication. In order to allow Kerberos authentication, the file sqlnet.ora (located in $ORACLE_HOME/network/admin) needs to be modified to look like the following:
$ cat sqlnet.ora
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
ADR_BASE = /export/home/oracle/app/oracle
#ADDED FOR KERBEROS
SQLNET.AUTHENTICATION_SERVICES= (BEQ, KERBEROS5)
SQLNET.AUTHENTICATION_KERBEROS5_SERVICE=orcl11g
SQLNET.KERBEROS5_CONF=/etc/krb5.conf
SQLNET.KERBEROS5_KEYTAB=/path/to/orcl11g.keytab
SQLNET.KERBEROS5_CONF_MIT=true
- Configure the database host as a Kerberos client:
Edit /etc/krb5.conf and set the values for the Kerberos server in kdc and admin_server properties. In my example, the Kerberos server is running on kdc_host.fr.oracle.com:
[...]
[libdefaults]
default_realm = EXAMPLE.COM
dns_lookup_realm = false
dns_lookup_kdc = false
ticket_lifetime = 24h
forwardable = yes
[realms]
EXAMPLE.COM = {
kdc = kdc_host.fr.oracle.com
admin_server = kdc_host.fr.oracle.com
}
[domain_realm]
.example.com = EXAMPLE.COM
example.com = EXAMPLE.COM
.fr.oracle.com = EXAMPLE.COM
fr.oracle.com = EXAMPLE.COM
[...]
You can test that the database host is properly configured by trying to get a Kerberos ticket-granting ticket for the database with the kinit utility:
$ kinit -k -t /path/to/orcl11g.keytab orcl11g/dbhost@EXAMPLE.COM
$ klist
Ticket cache: FILE:/tmp/krb5cc_54321
Default principal: orcl11g/dbhost@EXAMPLE.COM
Valid starting Expires Service principal
03/03/16 11:35:50 03/03/16 21:35:50 krbtgt/EXAMPLE.COM@EXAMPLE.COM
renew until 03/04/16 11:35:50
Kerberos 4 ticket cache: /tmp/tkt54321
klist: You have no tickets cached
$
- Configure the sql client host as a Kerberos client:
this step is the same as the previous one (edition of /etc/krb5.conf), except that it must be done on the host where the sql client is running. You can test that the Kerberos configuration is working by getting a ticket-granting ticket for the Enterprise user:
$ kinit eususer
Password for eususer@EXAMPLE.COM:
$ klist
Ticket cache: FILE:/tmp/krb5cc_54321
Default principal: eususer@EXAMPLE.COM
Valid starting Expires Service principal
03/29/12 16:32:41 03/30/12 02:32:41 krbtgt/EXAMPLE.COM@EXAMPLE.COM
renew until 03/30/12 16:32:38
Kerberos 4 ticket cache: /tmp/tkt54321
klist: You have no tickets cached
$
- Configure the sql client to authenticate with Kerberos:
Create a directory to store the SQL client configuration, and define the environment variable TNS_ADMIN to point to this directory:
$ mkdir -p /path/for/sql/config
$ export TNS_ADMIN=/path/for/sql/config
In this directory, create a tnsnames.ora file defining how to contact the database:
$ cat $TNS_ADMIN/tnsnames.ora
ORCL11G = (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbhost)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = orcl11g))
)
In the same directory, create a sqlnet.ora file defining the connection method to the database:
$ cat $TNS_ADMIN/sqlnet.ora
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
SQLNET.AUTHENTICATION_SERVICES = (BEQ,KERBEROS5)
SQLNET.KERBEROS5_CONF=/etc/krb5.conf
SQLNET.KERBEROS5_CONF_MIT=true
- Link the LDAP user entry to the Kerberos principal:
The Kerberos principal that will be used with SQL client must correspond to a LDAP user. In order to do this, you have 2 options:
- if your LDAP users have the objectclass orcluserV2, you can add the attribute krbprincipalname to the user entry. For instance, if Kerberos principal eususer must correspond to LDAP entry cn=user,dc=example,dc=com, you must add krbprincipalname: eususer@EXAMPLE.COM to the entry.
- if your LDAP users do not have the objectclass orcluserV2, you can user another attribute to store the Kerberos principal name. In this case, you must declare this attribute by setting the attribute orclcommonkrbprincipalattribute of the entry cn=Common,cn=Products,cn=OracleContext,dc=example,dc=com. For instance:
dn: cn=Common,cn=Products,cn=OracleContext,dc=example,dc=com
orclcommonkrbprincipalattribute: mail
will correspond to a user entry
dn: cn=user,dc=example,dc=com
mail: eususer@EXAMPLE.COM
- Test the connection:
The user must first get a Kerberos ticket-granting ticket, then call sqlplus with the / option that specifies to use the Kerberos TGT:
$ kinit eususer
Password for eususer@EXAMPLE.COM:
$ klist
Ticket cache: FILE:/tmp/krb5cc_54321
Default principal: eususer@EXAMPLE.COM
Valid starting Expires Service principal
03/29/12 16:47:29 03/30/12 02:47:29 krbtgt/EXAMPLE.COM@EXAMPLE.COM
renew until 03/30/12 16:47:27
Kerberos 4 ticket cache: /tmp/tkt54321
klist: You have no tickets cached
$ sqlplus /@orcl11g
SQL*Plus: Release 11.2.0.2.0 Production on Thu Mar 29 16:47:35 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$ klist
Ticket cache: FILE:/tmp/krb5cc_54321
Default principal: eususer@EXAMPLE.COM
Valid starting Expires Service principal
03/29/12 16:47:29 03/30/12 02:47:29 krbtgt/EXAMPLE.COM@EXAMPLE.COM
renew until 03/30/12 16:47:27
03/29/12 16:47:35 03/30/12 02:47:29 orcl11g/dbhost@EXAMPLE.COM
renew until 03/30/12 16:47:27
Kerberos 4 ticket cache: /tmp/tkt54321
klist: You have no tickets cached
$
You can see using klist utility that after the sql connection, the user has a second ticket corresponding to the database service.
Troubleshooting tips:
If your setup is not working, you can enable Kerberos logs by adding the following in the /etc/krb5.conf of the Kerberos server:
[logging]
kdc = FILE:/var/log/krb5kdc.log
then restart the KDC to apply the changes:
$ sudo service krb5-kdc restart
The problem can be linked to the host names (if the database hostname is defined as a fully-qualified-domain-name but the database service principal uses only the short name or vice-versa). The logs will show you which service principal is used by the database and if there is a mismatch.
Another common problem is when the database is not able to find any user entry matching the Kerberos principal from the ticket. In this case, you can have a look at OUD access log and check the search done by the database to find the user entry. This will be a search equivalent to:
$ ldapsearch -b <orclcommonusersearchbase> "(<orclcommonkrbprincipalattribute>=<krb_principal from the ticket>)"
You need to make sure that the database is using the proper search base and search filter. The search base is configured in the entry cn=Common,cn=Products,cn=OracleContext,dc=example,dc=com, in the attribute orclcommonusersearchbase. To find its value, you can perform the following search operation:
$ OracleUnifiedDirectory/bin/ldapsearch -h $LDAPSERVER -p $PORT -b cn=common,cn=products,cn=oraclecontext,$BASEDN "(objectclass=*)" orclcommonusersearchbase
dn: cn=Common,cn=Products,cn=OracleContext,dc=example,dc=com
orclcommonusersearchbase: ou=people,dc=example,dc=com
Florence Blanc-Renaud's technical spot 