12 Proxy Authentication

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:

Need for Proxy Authentication

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;

After the 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 scott wants 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.

Accounting purposes.

The transactions made through proxy sessions can be better accounted by proxying the connecting user, jeff, under different users, such as scott and scott2, assuming scott and 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.

Creating Proxy Connections

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:

USER NAME

This is done by supplying the user name or the password or both. The SQL statement for specifying authentication using password is:

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

DISTINGUISHED NAME

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:

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:

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:

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.

Notes:

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.

Caching Proxy Connections

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.

When the 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.

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.

Scripting on this page enhances content navigation, but does not change the content in any way.