Yes - it is possible: You can connect to the Oracle database as say: User "A" with
the password of User "B" - and that is not a security vulnerability. In this
blog posting I'd like to elaborate a bit on proxy authentication - this feature
is particular useful in three-tier-applications. Database connections are typically
part of the static application server configuration ("data-sources.xml").
And when such a java environments want to use the user accounts in the database
there is a problem: We cannot add each individual user to the datasource configuration.
The first arguments is, that there might be just too many users - the second one is that
changes to data-sources.xml are often not possible without downtime.
So we need a connection using one single technical user's credentials and the ability
to set the desired username at runtime.
And that is all what proxy authentication is about. We'll start with creating
the technical user (TECHUSER) and two "real" user accounts (REALUSER1 and REALUSER2).

TECHUSER used his own credentials to log in. But the user as which the
connection should be established was provided within the brackets. In SQL*Plus it
is that easy: We logged in as TECHUSER/techuser and we now areREALUSER1.

So far - so good. But for real world environments this is virtually useless - no end
user connects with SQL*Plus. And we cannot add the brackets to the static datasource definition
in the application server: we need to set the client user dynamically with some code - and
the following example will show how to do this. First I have a little testing program in
java language. It first connects to the database with hardcodedTECHUSER credentials,
then it looks up "as who" it is connected and finally it shows the contents of the EMP table.
Again: The username and password arguments in the call to DriverManager.getConnection are
hard coded and this will not change!

Only by giving the username on the command line we connected as REALUSER1 or REALUSER2 - the
password always was TECHUSER's one. With this approach a java program can obtain a database
connection from its application server, set the client username and connect to the
database as another "real" user. And this proxy connection can be changed while the
main "physical" connection remains open. The following code shows this - for clarity
I have removed the actual database actions and replaced them with the
pseudo method doDatabaseActions().

publicstaticvoid main(String args[]) throws Exception {
// open the physical connection as TECHUSER
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@sccloud030:1521/orcl","techuser","techuser");
// open the proxy connection for REALUSER1, do something and close
Properties props = new Properties();
props.put("PROXY_USER_NAME", "realuser1");
((OracleConnection)con).openProxySession(OracleConnection.PROXYTYPE_USER_NAME, props);
doDatabaseActions(con);
((OracleConnection)con).close(OracleConnection.PROXY_SESSION);
// the physical connection is still open ...// now open the proxy connection for REALUSER2, do something and close
props.put("PROXY_USER_NAME", "realuser2");
((OracleConnection)con).openProxySession(OracleConnection.PROXYTYPE_USER_NAME, props);
doDatabaseActions(con);
((OracleConnection)con).close(OracleConnection.PROXY_SESSION);
// finally close the physical connection
con.close();
}

When client/server applications are moving to a web environment,
this technology gets highly interesting - because the application can keep its existing
user, role and security model and move on to the web technology where all database connections
are done with one technical user.

REALUSER1 or REALUSER2 still need their CREATE SESSION privilege - the accounts also
must exist in the database and must not be locked. But (if needed) you actually can "forbid"
direct connections by setting the password to an "impossible" value ...

SQL> alter user realuser1 identified by values 'Hello'

The REALUSER1 account is still open and connects are possible in theory. But since the
IDENTIFIED BY VALUES command directly wrote "Hello" into the password table, one would
have to find out one of the password - which evaluate to "Hello" during the hashing process - I'll
say it that way: at least very difficult. Connections as REALUSER1 now are only possible as
connections through TECHUSER. But it is also possible to have both direct and proxy connections
in parallel.

Visitors on this blog are being counted by statcounter.com. For this purpose personal data like your IP address, visit length, referring sites and other information is being collected. I'm doing this since I like to know whether the blog sites are being read and from where the visitors come from.

For information about if and how the service providers use your data, please write directly to statcounter.com or visit their sites for more information. Their privacy statement is located here: http://www.statcounter.com/privacy.html.

Also, on visiting this blog one or more "cookies" will be set, if your have enabled the cookie function in your browser. Please note that most browsers allow you to control or disable that function.

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle Corporation or Oracle Germany. The views and opinions expressed by visitors to this blog are theirs and do not necessarily reflect mine.