kerberos authentication in oracle database 11g(single sign on)

This post will demonstrate how to configure kerberors to authenticate  oracle database users..

We  will have a kerberos server, a database server and a  database client here.  Here I am using RHEL 5.4 where oracle database 11.2.0.1.0 is running.  I am using single machine for all these kind of server.

Machine name is host1.oracle.local. Database name is orcl.oracle.local.

Phase I : Here , we will install kerberos and configure it.

To install Kerberos , you need to install two rpms.

krb5-workstation

and

krb5-server.

This packages are already on Linux installation CD-ROM. MOunt CD-ROM, go to Server folder and

# rpm -ivh krb5-workstation*

# rpm -ivh krb5-server*

There are two configuration files which needs to be change:

/etc/krb5.conf

and

/var/kerberos/krb5.kdc/kdc.conf

In /etc/krb5.conf, under [libdefaults] section, [realms] section and [domain_realm], provide appropriate value for default realm. Generally, realm name is same as domain name. Realm name is written in capital letters. In my case, realm name will be ORACLE.LOCAL

…………………….//contents trimmed

[libdefaults]

default_realm = ORACLE.LOCAL

dns_lookup_realm = false

dns_lookup_kdc = false

…………………………….// contents trimmed

[realms]

ORACLE.LOCAL = {

kdc = host1.oracle.local:88

admin_server = host1.oracle.local:749

default_domain = oracle.local

}

[domain_realm]

.oracle.local =ORACLE.LOCAL

oracle.local  = ORACLE.LOCAL

[appdefaults]

…………………………..//contents trimmed

In /var/kerberos/krb5kdc/kdc.conf, modify [realms] again to point to your realm.

[kdcdefaults]

v4_mode = nopreauth

kdc_tcp_ports = 88

[realms]

ORACLE.LOCAL = {

#master_key_type = des3-hmac-sha1

acl_file = /var/kerberos/krb5kdc/kadm5.acl

………………………contents trimmed

Also edit /var/kerberos/krb5kdc/kadm5.acl

[root@host1 log]# cat /var/kerberos/krb5kdc/kadm5.acl

*/admin@ORACLE.LOCAL      *

Now, create Kerberos database

# kdb5-util create -S

After creating database,  start kerberos service

#service krb5kdc start

#service kadmin start

#kadmin.local

This will show you kadmin.local prompt.

kadmin.local:

Type ?, to get a help for list of commands.

Each entry in Kerberos database is known as principal. There can be service principal and user principal.

User principal: The entry that describes kerberos user

Service principal: The entry that describes sevice that will be kerberised . Here oracle database will be the service that will be kerberised.

kadmin.local:  listprincs

will show you list of principals that are inside kerberos database.

Now, we are ready with the kerberos authentication server . Now create a user that will be authenticated by the kerberos server. For this you need to add a user principal into the  kerberos database.

#kadmin.local: ank krbuser

WARNING: no policy specified for kkuser@ORACLE.LOCAL; defaulting to no policy

Enter password for principal “krbuser@ORACLE.LOCAL”:

Re-enter password for principal “krbuser@ORACLE.LOCAL”:

Principal “krbuser@ORACLE.LOCAL” created.

Now, issue listprincs command to view the list of principals that you have. It should show also show krbuser@ORACLE.LOCAL

Now, lets test the kerberos server by configuring client to use kerberos for user authentication.

#kinit krbuser

Password for krbuser@ORACLE.LOCAL:

# klist

Ticket cache: FILE:/tmp/krb5cc_0

Default principal: krbuser@ORACLE.LOCAL

Valid starting     Expires            Service principal

04/24/13 00:03:47  04/25/13 00:03:47  krbtgt/ORACLE.LOCAL@ORACLE.LOCAL

Kerberos 4 ticket cache: /tmp/tkt0

klist: You have no tickets cached

This output means we are successful.  Here we have ticket granting ticket using which we can ask Kerberos to provide ticket to access  a service that has already been kerberised.

Since  Kerberos server also needs to be used by Database service orcl.oracl.geet , so this service needs to be kerberised by creating service principal. Service Principal will have the following format:

kservice/kinstance@REALM

where, kservice  is a case-sensitive string that represents the Oracle service. This can be the same as the database service name.

kinstance   is typically the fully qualified DNS name of the system on which Oracle Database is running.

REALM   is the name of the Kerberos realm

 To create Service principal:

kadmin.local: ank -randkey  orcl.oracle.local/host1.oracle.local@ORACLE.LOCAL

kadmin.local:listprincs

should list your service principal

Phase II:   Configuring database server to use Kerberos server.

1. Extract the service key table from Kerberos and copy it to the Oracle database server which will be served as Kerberos client system here.

Enter the following to extract the service key table:

kadmin.local: ktadd -k /tmp/keytab orcl.oracle.local/host1.oracle.local

Now copy service key table to a location from where oracle database server can access it and give oracle user permission to access it.

#mv /tmp/keytab /etc/v5srvtab

#chown oracle:oinstall /etc/v5srvtab

2. Use network manager to modify sqlnet.ora,listener.ora  as well as tnsnames.ora

New Picture

New Picture (1)

//sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES= (BEQ,KERBEROS5)

SQLNET.AUTHENTICATION_KERBEROS5_SERVICE = ORCL.ORACLE.LOCAL

ADR_BASE = /u01/app/oracle

SQLNET.KERBEROS5_KEYTAB=/etc/v5srvtab

SQLNET.KERBEROS5_CONF=/etc/krb5.conf

//tnsnames.ora

ORCL =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = host1.oracle.local)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = ORCL.ORACLE.LOCAL)

)

)

// listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = ORCL.ORACLE.LOCAL)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

(SID_NAME = orcl)

)

)

LISTENER =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = host1.oracle.local)(PORT = 1521))

)

ADR_BASE_LISTENER = /u01/app/oracle

3. Set initializaion parameter OS_AUTHENT_PREFIX to null.

OS_AUTHENT_PREFIX=””

4. If you are using MIT Kerberos 5 then also set  following parameter in the sqlnet.ora file:

SQLNET.KERBEROS5_CONF_MIT = TRUE

Your Kerberos configuration file (krb5.conf) should have the following settings:

[libdefaults]

kdc_timesync = 1

ccache_type = 4

5. Now, create a Oracle user  that corresponds to the Kerberos user, krbuser that we created previously.

SQL> CREATE USER KRBUSER IDENTIFIED EXTERNALLY AS ‘krbuser@ORACLE.LOCAL’;

SQL> Grant create session to krbuser;

6. Restart listener.

7. Before you can connect to the database, you must ask the Kerberos server  for an initial ticket

 

[oracle@host1 admin]$ okinit krbuser

Kerberos Utilities for Linux: Version 11.2.0.1.0 – Production on 24-APR-2013 01:00:35

Copyright (c) 1996, 2009 Oracle.  All rights reserved.

Password for kkuser@ORACLE.LOCAL:

[oracle@host1 admin]$ oklist

Kerberos Utilities for Linux: Version 11.2.0.1.0 – Production on 24-APR-2013 01:00:41

Copyright (c) 1996, 2009 Oracle.  All rights reserved.

Ticket cache: /tmp/krb5cc_501

Default principal: krbuser@ORACLE.LOCAL

Valid Starting           Expires            Principal

24-Apr-2013 01:00:38  24-Apr-2013 09:00:35  krbtgt/ORACLE.LOCAL@ORACLE.LOCAL

8. NOw try to connect to oracle database

$ sqlplus /@orcl

SQL> show user

USER is “KRBUSER”

SQL> exit

$ oklist

Kerberos Utilities for Linux: Version 11.2.0.1.0 – Production on 24-APR-2013 01:11:42

Copyright (c) 1996, 2009 Oracle.  All rights reserved.

Ticket cache: /tmp/krb5cc_501

Default principal: kkuser@ORACLE.LOCAL

Valid Starting           Expires            Principal

24-Apr-2013 01:00:38  24-Apr-2013 09:00:35  krbtgt/ORACLE.LOCAL@ORACLE.LOCAL

24-Apr-2013 01:05:17  24-Apr-2013 09:00:35  ORCL.ORACLE.LOCAL/host1.koenig.com@ORACLE.LOCAL

See, we have two tickets. one is ticket granting ticket and another is ticket to access database service.

FINISHED………….