Oracle Java Database Connectivity (JDBC) provides proxy authentication, also called N-tier authentication. This feature is supported through both the JDBC Oracle Call Interface (OCI) driver and the JDBC Thin driver. This chapter contains the following sections:
Proxy authentication allows one JDBC connection to act as a proxy for other JDBC connections. An application may need proxy authentication for any of the following reasons:
The middle tier does not know the password of the proxy user.
It is sometimes a security concern for the middle tier to know the passwords of all the database users. In this case, proxy authentication is done by first issuing an
ALTER USER statement. For example:
ALTER USER jeff GRANT CONNECT THROUGH scott WITH ROLES role1, role2;
ALTER USER statement is processed, the application can connect as
jeff using the already authenticated credentials of
scott. Although the created session will function as if
jeff was connected normally,
jeff will not have to divulge its password to the middle tier. The proxy section has access to the schema of
jeff, as well as to what is indicated in the list of roles. Therefore, if
jeff to access its
EMP table, then the following code can be used:
CREATE ROLE role1; GRANT SELECT ON emp TO role1;
The role clause limits the user
jeff to access only those database objects of
scott that are mentioned in the list of the roles. The list of roles can be empty.
The transactions made through proxy sessions can be better accounted by proxying the connecting user,
jeff, under different users, such as
scott2 are authenticated. Transactions made under these different proxy sessions by
jeff can be logged separately.
Note:In this chapter, a JDBC connection to a database is a user session in the database and vice versa.
In order to get a proxy connection, a user, say
jeff, has to connect to the database through another user, say
scott. The proxy user,
scott, should have an active authenticated connection. Using this active connection, the driver sends issues a command to the server to create a session for the user,
jeff. The server returns the new session id, and the driver sends a session switch command to switch to this new session.
You can create proxy connections using any one of the following options:
This is done by supplying the user name or the password or both. The SQL statement for specifying authentication using password is:
ALTER USER jeff GRANT CONNECT THROUGH scott AUTHENTICATED USING password;
In this case,
jeff is the user name and
scott is the proxy for
The reason why the password option exists is for additional security. Having no
authenticated clause implies default authentication, which is using only the user name without the password. The SQL statement for specifying default authentication is:
ALTER USER jeff GRANT CONNECT THROUGH scott
This is a global name in lieu of the password of the user being proxied for. An example of the corresponding SQL statement using a distinguished name is:
CREATE USER jeff IDENTIFIED GLOBALLY AS 'CN=jeff,OU=americas,O=oracle,L=redwoodshores,ST=ca,C=us';
The string that follows the
identified globally as clause is the distinguished name. It is then necessary to authenticate using this distinguished name. The corresponding SQL statement to specify authentication using distinguished name is:
ALTER USER jeff GRANT CONNECT THROUGH scott AUTHENTICATED USING DISTINGUISHED NAME;
This is a more encrypted way of passing the credentials of the user, which is to be proxied, to the database. The certificate contains the distinguished name encoded in it. One way of generating the certificate is by creating a wallet and then decoding the wallet to get the certificate. The wallet can be created using
runutl mkwallet. It is then necessary to authenticate using the generated certificate. The SQL statement for specifying authentication using certificate is:
ALTER USER jeff GRANT CONNECT THROUGH scott AUTHENTICATED USING CERTIFICATE; .
Note:The use of certificates for proxy authentication will be desupported in future Oracle Database releases.
The JDBC OCI and Thin driver switch sessions in the same manner. The drivers permanently switch to the new session,
jeff. As a result, the proxy session,
scott, is not available until the new session,
jeff, is closed.
All the options can be associated with roles.
When opening a new proxied connection, a new session is started on the database server. Along with this session a new local transaction is created.
Proxy connections, like normal connections, can be cached. Caching proxy connections enhances the performance. To create a proxy connection, you need to first create a connection using one of the
getConnection methods on a cache enabled
OracleDataSource object. A proxy session is then created by calling the
openProxySession method on the obtained connection.
close(PROXY_CONNECTION) method is called on this physical connection, the proxy session created on this connection gets closed. This is similar to closing a proxy session on a non-cached connection. The standard
close method must be called explicitly to close the connection itself. If the
close method is called directly, without closing the proxy session, then both the proxy session and the connection are closed.
A proxy connection may be cached in the connection cache using the connection attributes feature of the connection cache. Connection attributes are name/value pairs that are user-defined and help tag a connection before returning it to the connection cache for reuse. When the tagged connection is retrieved, it can be directly used without having to do a round trip to create or close a proxy session. Implicit connection cache supports caching of any user/password authenticated connection. Therefore, any user authenticated proxy connection can be cached and retrieved.
A sample code illustrating the use of proxy sessions is as follows:
... java.util.Properties connAttr = null; ... //obtain connection from a cache enabled DataSource OracleConnection conn = ds.getConnection("scott",tiger"); conn.openProxySession(proxyType, proxyProps); ... connAttr.setProperty("CONNECTION_TAG","JOE'S_PROXY_CONNECTION"); conn.applyConnectionAttributes(connAttr); //apply attributes to the connection conn.close(); //return the tagged connection to the cache ... //come back later and ask for Joe's proxy connection conn=ds.getConnection(connAttr); //This will retrieve Joe's proxy connection ...
It is recommended that proxy connections should not be closed without applying the connection attributes. If a proxy connection is closed without applying the connection attributes, the connection is returned to the connection cache for reuse, but cannot be retrieved. The connection caching mechanism does not remember or reset session state.
A proxy connection can be removed from the connection cache by closing the connection directly. This can be done by calling the
close(INVALID_CONNECTION) method on the connection. The method closes both the connection and the proxy session and removes them from the cache.