12 Proxy Authentication

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.

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:

  ALTER USER jeff GRANT CONNECT THROUGH scott AUTHENTICATED USING password;
  

  In this case, jeff is the user name and scott is the proxy for jeff.

  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:

  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;
  

• CERTIFICATE

  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.

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.

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.