How to use External Authenticating Oracle Account when Running Batch Job

[jancur]

We have developed a Spring 2.0 Batch job which will be run from the command line. This batch job connects to an Oracle database to get the job details. During development we have used a jdbc.properties files to specify the Oracle connection properties similar to the following.

jdbc.driverClassName=oracle.jdbc.driver.OracleDriv er
jdbc.url=jdbc:oracle:thin:<at>server:1521:SID
jdbc.username=username
jdbc.password=password

Using this properties file is o.k. in our dev environments because we are allowed to know the Oracle username and password. This is not the case in our production environment though.

In production we are required to sudo into an externally authenticating UNIX account before running the batch job. This allows us to connect to Oracle via the external account without having to know the Oracle username and password.

I have tried every combination I can think of to get the batch job to run via this external account, but I always get an invalid username/password exception.

Has anyone ever run a batch job via an external authenticating account and if so can you tell me how you got it to work?

Thanks,
John

[Dave Syer]

Maybe you can explain a bit what this "externally authenticating account" is or does. How does it prevent you from having to know the connection details in Java? Do you have other Java programs running against the database from this account?

[funks]

In oracle, you can set the user as an external account in that the authentication pass-through is done via the OS login context. Similar to Microsoft SQL Server when set to Windows Integrated Security Authentication..

You might want to try the following and see if it works

String url = "jdbc:oracle:thin:@oracleserver.mydomain.com:5521:dbja"
Driver driver = new oracle.jdbc.OracleDriver();
DriverManager.registerDriver(driver);
Properties props = new Properties();
props.setProperty(OracleConnection.CONNECTION_PROPERTY_THIN_VSESSION_OSUSER,"USERNAME")
Connection conn = DriverManager.getConnection( url, props);

In regards to the OracleConnection.CONNECTION_PROPERTY_THIN_VSESSION _OSUSER, you should haven't to do that if the Java System property "user.name" is being set correct. Or when launching your java app from the os user with the proper privileges, try something like this - java -Duser.name=ACTUAL_USER_NAME classfile..

JDBC Code Using OS Authentication

Now that you have set up OS authentication to connect to the database, you can use the following JDBC code for connecting to the database:

String url = "jdbc:oracle:thin:@oracleserver.mydomain.com:5521:dbja"
Driver driver = new oracle.jdbc.OracleDriver();
DriverManager.registerDriver(driver);
Properties props = new Properties();
Connection conn = DriverManager.getConnection( url, props);

The preceding code assumes that it is executed by p_floyd on the client machine. The JDBC drivers retrieve the OS username from the user.name system property that is set by the JVM. As a result, the following thin driver-specific error no longer exists:

ORA-17443=Null user or password not supported in THIN driver

Note:
By default, the JDBC driver retrieves the OS username from the user.name system property, which is set by the JVM. If the JDBC driver is unable to retrieve this system property or if you want to override the value of this system property, then you can use the OracleConnection.CONNECTION_PROPERTY_THIN_VSESSION _OSUSER connection property. For more information, see the Oracle Javadoc.

if using windows

CREATE USER "OPS$yourdomain.com\p_floyd" IDENTIFIED EXTERNALLY;
GRANT CONNECT TO "OPS$yourdomain.com\p_floyd";

Note:
When you create the database user in Windows environment, the user name should be in the following format:

<OS_authentication_prefix_parameter>$<DOMAIN>\<OS_user_name>

When using a Windows server, there is an additional consideration. The following option must be set in the %ORACLE_HOME%\network\admin\sqlnet.ora file:

SQLNET.AUTHENTICATION_SERVICES= (NTS)

http://www.filibeto.org/sun/lib/nons...24/clntsec.htm <-- I believe that is relevant to the 11g JDBC driver.