Saturday, August 9. 2014
Oracle Driver and Kerberos
Continuing the previous post this entry is going to summarize how the Oracle JDBC and ODBC drivers deal with a Kerberos environment. It is important to keep in mind that the common use-case is using Kerberos only for fat applications (DBAs, PC desktop apps,...), in spite of that, the entry will try to cover all the scenarios. I am not going to describe how the setup of the environment was done (please see this blog entry by Laurent Schneider or this community thread for more information about the installation). The entry is mainly about Java, Kerberos and the Oracle JDBC/ODBC driver. The environment used for the tests was the following: Windows 2008R2 as the KDC server, Oracle 11.2.0.3 as the DDBB server and Instant Client 11.2.0.4 as the client (installed in the same Windows DC machine).
There are going to be four separated cases for the different situations I have been testing. Please do not focus on the code, I know is bad and improvable but the main objective of this entry is giving some examples about Kerberos integration with the driver (it is not an excuse but the Java and PHP code was done using notepad in a windows virtual box). The Java examples are based in the formal documentation provided by Oracle.
The OCI and ODBC driver
The Java OCI driver is the thick one, it uses native libraries, tnsnames.ora, sqlnet.ora and the rest of common configuration files, and it needs a full native Oracle client installed in the machine (in order to use Kerberos the Advance Security option should be selected in the client installation). In theory, with this driver, once you have a working sqlplus for Kerberos all the job is done. This driver uses the same underlying infrastructure that the command manages. This is the simple example for the OCI driver.
import java.sql.*; public class JdbcOci { public static void main(String[] args) throws Exception { Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; try { Class.forName("oracle.jdbc.driver.OracleDriver").newInstance(); conn = DriverManager.getConnection("jdbc:oracle:oci:/@ddbb1"); stmt = conn.prepareStatement("select user from dual"); rs = stmt.executeQuery(); if (rs.next()) { System.out.println(rs.getString(1)); } } finally { if (rs != null) try {rs.close();} catch (SQLException e) {} if (stmt != null) try {stmt.close();} catch (SQLException e) {} if (conn != null) try {conn.close();} catch (SQLException e) {} } } }
As you see the getConnection method does not receive any user or password, it is supposed that you have already logged in using another way (kinit in UNIX or common windows login with Microsoft) and the OCI driver is going to use the resulting ticket cache to authenticate against the DDBB. Because OCI is being used, no special configuration should be done, Java will use exactly the same configuration as the sqlplus client (tnsnames.ora and sqlnet.ora files). Once the user is logged in his PC, he can access to the application (and in turn the database) with no password.
It is remarkable that all the following variations work: getConnection(url), getConnection(url, null, null) and getConnection(url, "", ""). If you are planning to migrate an existing fat client application to Kerberos, it will work just changing some configuration (properties file or similar).In the ODBC counterpart the situation is more or less the same. When the client is installed in the Windows box (with the ODBC option enabled) the Oracle driver appears listed in the drivers tab of the ODBC Data Source Administrator application (Start → Administrative Tools → Data Sources (ODBC)). The name of the driver depends of the installation but it will be very similar to the one assigned in my demo: Oracle in OraClient 11g_home1_32bit.
And it works exactly as it does in Java, I decided to test it with PHP, here it is my little example.
<?php // connect using the System DS //$conn = odbc_connect("DSN=ddbb1;", null, null) or die(odbc_errormsg()); // normal connection using driver $conn = odbc_connect("Driver={Oracle in OraClient11g_home1_32bit};Dbq=ddbb1;", null, null) or die(odbc_errormsg()); $result=odbc_exec($conn, "select user from dual"); if (odbc_fetch_row($result)) { echo "Result is " . odbc_result($result, 1); } odbc_close($conn); ?>
If you check the code, two options were tested: connecting using normal oracle driver and its string and using a System Data Source string. (A kind of named connection string defined at system level, using it any application can use the DS name avoiding specific connection strings. They are defined in the System DSN tab of the previous application.) As it was commented in Java no matter if null or empty string "" are specified for username and password. It does not matter either if the connection string has empty properties for them or not (Uid=;Pwd=; properties can be passed empty or can be not defined as in the example code).
There is only one Oracle ODBC driver, and it uses the whole native client installation (libraries and configuration files) exactly as the OCI Java driver, so the following parts will only comment about the THIN Java driver.
The THIN driver: already logged
The thin driver on the other hand is a full Java implementation and, therefore, all the configuration should be passed inside the Java code (it cannot use typical database configuration files as in the previous driver). This option needs modifications in the application code (not very deep but the connection retrieval should be slightly changed). The idea of the following example is the same than in the previous case, the user is already logged in and the application is going to re-use the generated ticket cache.
import java.sql.*; import java.util.Properties; import java.io.File; import oracle.jdbc.OracleConnection; import oracle.jdbc.OracleDriver; public class JdbcThin { public static void main(String[] args) throws Exception { Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; // create the connection properties for Kerberos String url = "jdbc:oracle:thin:@//ddbb1.demo.test:1521/ddbb1"; Properties prop = new Properties(); String krb5conf = "C:/app/Administrator/product/11.2.0/client_1/network/admin/krb5.conf"; System.out.println("krb5.conf exists(): " + new File(krb5conf).exists()); System.setProperty("java.security.krb5.conf", krb5conf); prop.setProperty(OracleConnection.CONNECTION_PROPERTY_THIN_NET_AUTHENTICATION_SERVICES, "( KERBEROS5 )"); prop.setProperty(OracleConnection.CONNECTION_PROPERTY_THIN_NET_AUTHENTICATION_KRB5_MUTUAL, "true"); // cache not needed, the system cache is read by default in windows //String cc = "C:/app/Administrator/product/11.2.0/client_1/network/admin/krbcc"; //System.out.println("cc exists(): " + new File(cc).exists()); //prop.setProperty(OracleConnection.CONNECTION_PROPERTY_THIN_NET_AUTHENTICATION_KRB5_CC_NAME, cc); try { Class.forName("oracle.jdbc.driver.OracleDriver").newInstance(); conn = DriverManager.getConnection(url, prop); stmt = conn.prepareStatement("select user from dual"); rs = stmt.executeQuery(); if (rs.next()) { System.out.println(rs.getString(1)); } } finally { if (rs != null) try {rs.close();} catch (SQLException e) {} if (stmt != null) try {stmt.close();} catch (SQLException e) {} if (conn != null) try {conn.close();} catch (SQLException e) {} } } }
The example is very similar to the OCI one but now all the needed configuration is passed to the connection retrieval using a properties object. Mainly it is specified that the application is going to use Kerberos (as you see the cache file is not specified because in windows the system cache is used by default, the Java implementation manages the Local Security Authority / LSA that windows provides).
EDIT: In order to allow THIN driver to work in Windows (LSA, not file cache) it is necessary to permit Java to access the ticket session key. The following regedit key should be added or modified (by default the entry does not exist):
HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Lsa\Kerberos\Parameters Value Name: AllowTgtSessionKey Value Type: REG_DWORD Value: 0x01 (default is 0)
The THIN driver: performing a Java login
The two examples which have been seen until now use the typical scenario, a user is already logged in using Kerberos (in a windows domain it just represents logging into the PC) and the Java application just re-uses the ticket already given to him. The next example is a bit complicated because now the user logs in using the Java Kerberos implementation and then the connection is retrieved with the resulting principal.
import java.sql.*; import java.io.File; import java.io.IOException; import oracle.jdbc.OracleConnection; import oracle.jdbc.OracleDriver; import com.sun.security.auth.module.Krb5LoginModule; import java.util.HashMap; import java.util.Properties; import javax.security.auth.Subject; import javax.security.auth.callback.*;; import java.security.PrivilegedExceptionAction; public class JdbcThinLogin { public static void main(String[] args) throws Exception { // request user and password String username = System.console().readLine("User: "); final char[] password = System.console().readPassword("Password: "); // set the krb5.conf String krb5conf = "/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/krb5.conf"; System.out.println("krb5.conf exists(): " + new File(krb5conf).exists()); System.setProperty("java.security.krb5.conf", krb5conf); // prepare a login Krb5LoginModule krb5Module = new Krb5LoginModule(); Subject subject = new Subject(); HashMap state = new HashMap(); HashMap options = new HashMap(); options.put("doNotPrompt", "false"); options.put("useTicketCache", "false"); options.put("principal", username); krb5Module.initialize(subject, new CallbackHandler() { public void handle(Callback[] callbacks) throws IOException, UnsupportedCallbackException { for (int i = 0; i < callbacks.length; i++) { if (callbacks[i] instanceof PasswordCallback) { PasswordCallback pc = (PasswordCallback) callbacks[i]; pc.setPassword(password); } else { throw new UnsupportedCallbackException(callbacks[i], "Unrecognized Callback!"); } } } }, state, options); boolean login = krb5Module.login(); krb5Module.commit(); if (!login) { throw new Exception("Cannot login using kerberos!"); } System.out.println("Logged as user: " + subject.getPrincipals()); // use the login for the thin driver Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; try { conn = (Connection) Subject.doAs(subject, new PrivilegedExceptionAction() { public Object run() { Properties prop = new Properties(); prop.setProperty(OracleConnection.CONNECTION_PROPERTY_THIN_NET_AUTHENTICATION_SERVICES, "( KERBEROS5 )"); String url = "jdbc:oracle:thin:@//ddbb1.demo.test:1521/ddbb1"; try { Class.forName("oracle.jdbc.driver.OracleDriver").newInstance(); return DriverManager.getConnection(url, prop); } catch(Exception e) { e.printStackTrace(); System.exit(-1); return null; } } } ); stmt = conn.prepareStatement("select user from dual"); rs = stmt.executeQuery(); if (rs.next()) { System.out.println(rs.getString(1)); } } finally { if (rs != null) try {rs.close();} catch (SQLException e) {} if (stmt != null) try {stmt.close();} catch (SQLException e) {} if (conn != null) try {conn.close();} catch (SQLException e) {} } } }
The example is a bit longer but I think it is quite clear. The Kerberos module is used to perform a login (please in a real implementation use a LoginModule defined in a login configuration file) and the resulting subject is used to create the connection. In my opinion this option is less useful, the typical scenario is the one presented in the previous cases and I do not imagine a situation where this example was useful. But I have decided to exemplify all possible situations, so here it is.
The THIN driver: performing a Java login but saving a ticket cache
The last use-case is similar to the previous one but now the login is going to write a ticket cache. I do not know why Java implementation cannot be configured to create a cache (exactly the same that kinit command does in UNIX systems). I think that this scenario is the typical one if you are working outside Java (you log in the first time, a ticket cache is created and then all the remaining authentications use the ticket already given). So I simply do not understand why Java does not give another property (createCache for example) to write the ticket if it is needed to re-send a new ticket (the cache is empty, outdated,...). I suppose that the general idea in Java is re-using the principal subject obtained after a Kerberos login (see the previous example).
Finally I developed a working program but it uses a lot of internal classes (so please avoid it). I am going to add it here just for curiosity about what I did. Basically I followed what is done in the Kinit implementation that comes with the Java distribution for Windows platforms.
import sun.security.krb5.KrbAsReqBuilder; import sun.security.krb5.PrincipalName; import sun.security.krb5.internal.ccache.CredentialsCache; import java.io.File; import java.sql.*; import java.util.Properties; import oracle.jdbc.OracleConnection; public class JdbcThinLogin2 { static public void main(String[] args) throws Exception { // initialize Kerberos String krb5conf = "/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/krb5.conf"; System.out.println("krb5.conf exists(): " + new File(krb5conf).exists()); System.setProperty("java.security.krb5.conf", krb5conf); // request username and password String username = System.console().readLine("User: "); final char[] password = System.console().readPassword("Password: "); // get the Credentials and create a CC cache PrincipalName principal = new PrincipalName(username, PrincipalName.KRB_NT_PRINCIPAL); KrbAsReqBuilder builder = new KrbAsReqBuilder(principal, password); builder.action(); sun.security.krb5.internal.ccache.Credentials credentials = builder.getCCreds(); builder.destroy(); CredentialsCache cache = CredentialsCache.create(principal, "krb5cc"); cache.update(credentials); cache.save(); // now normal working for JDBC Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; String url = "jdbc:oracle:thin:@//ddbb1.demo.test:1521/ddbb1"; Properties prop = new Properties(); System.setProperty("java.security.krb5.conf", krb5conf); prop.setProperty(OracleConnection.CONNECTION_PROPERTY_THIN_NET_AUTHENTICATION_SERVICES, "( KERBEROS5 )"); prop.setProperty(OracleConnection.CONNECTION_PROPERTY_THIN_NET_AUTHENTICATION_KRB5_MUTUAL, "true"); prop.setProperty(OracleConnection.CONNECTION_PROPERTY_THIN_NET_AUTHENTICATION_KRB5_CC_NAME, "krb5cc"); try { Class.forName("oracle.jdbc.driver.OracleDriver").newInstance(); conn = DriverManager.getConnection(url, prop); stmt = conn.prepareStatement("select user from dual"); rs = stmt.executeQuery(); if (rs.next()) { System.out.println(rs.getString(1)); } } finally { if (rs != null) try {rs.close();} catch (SQLException e) {} if (stmt != null) try {stmt.close();} catch (SQLException e) {} if (conn != null) try {conn.close();} catch (SQLException e) {} } } }
The internal KrbAsReqBuilder and CredentialsCache classes are used to perform a login and write the credentials in a ticket cache file. Then that file is used as it was in the first THIN example. Weird program indeed but nice.
So today's entry is not very exciting but summarizes the use of Kerberos with the Oracle JDBC/ODBC driver. Some little examples are shown to understand how the different driver implementations handle with Kerberos. I repeat that the common situation is integrating fat applications (SQLDeveloper or similar applications for DBAs and in house developments) which open a direct connection against the database with a personal user. Those applications can re-use Kerberos tickets and the final solution can be very practical. Obviously your company needs to use Kerberos to authenticate users, but Microsoft forced that long time ago when Windows Active Domain was introduced.
See you next time!
I want use "The THIN driver: performing a Java login but saving a ticket cache" in my oracle forms 12c. Can you send me the same example exit java program without disconnect from database.
Thanks a lot
Marina
The last example performs a login in java and saves a credential cache in a file called "krb5cc". The program just shows the logged user and finishes (closing everything). The "krb5cc" file can still be used as if it was created using kinit. This example is also weird, cos it uses a lot of internal classes.
I don't understand what you are asking. But think that the entry is almost three years old, I hardly remember what I did and I don't have the infrastructure anymore. You can use the code, modify it or do whatever you need with it, but please don't ask for changes.
Comments