Configuring an Oracle DataSource With Proxied Usernames

When you configure a global shared JDBC datasource for a particular tc Runtime instance, by default all deployed applications that use the datasource connect to the configured database using the same username and password. This user is called a proxy, because the proxy user performs a database task on behalf of the user using the application deployed to tc Runtime. When an application user connects anonymously through a proxy, however, it is impossible to customize security for individual users or get a meaningful audit trail of the users that actually used the database.

For this reason it can be useful to configure the tc Runtime instance so that, while many applications share a particular global datasource, each application connects to the database using a different username and password via the proxy user, rather than directly through the proxy user that is configured for the datasource itself. SpringSource tc Runtime has implemented this feature using the Oracle proxy connection authentication.

NOTE: This feature applies only to Oracle datasources.

The following procedure describes how to configure tc Runtime, and your applications, to use a shared global Oracle datasource with the proxy connection authentication.

  1. Configure a standard shared global Oracle datasource for your tc Runtime instance by adding a <Resource> child element of the <GlobalNamingResource> element in the server.xml file. The actual configuration depends on your Oracle database environment, but the following snippet provides an example (relevant sections shown in bold):

    <?xml version='1.0' encoding='utf-8'?>
    <Server port="-1" shutdown="SHUTDOWN">
    
     ...
    
      <GlobalNamingResources>
    
        <Resource name="jdbc/TestDB" auth="Container"
          type="oracle.jdbc.pool.OracleDataSource"
          description="Oracle Datasource"
          factory="oracle.jdbc.pool.OracleDataSourceFactory"
          url="jdbc:oracle:thin:@//localhost:1521/orcl"
          user="default_user"
          password="password"
          connectionCachingEnabled="true"
          connectionCacheName="CXCACHE"
          connectionCacheProperties="{MaxStatementsLimit=5, MinLimit=1, MaxLimit=1, ValidateConnection=true}"/>
    
      </GlobalNamingResources>
      ...
      <Service name="Catalina">
      ...
      </Service>
    </Server>

    In the preceding server.xml snippet, by default the jdbc/TestDB datasource connects to the database as the user default_user with password password; this is the proxy user.

  2. Use the jdbc/TestDB datasource in your servlet and JSPs as usual.

    The following snippet shows an example of using it in a JSP to get a connection to the database:

    <%@ page import="java.sql.Connection,java.sql.ResultSet,java.sql.Statement,javax.naming.*,javax.sql.*"%>
    
        Context initContext = new InitialContext();
        Context envContext  = (Context)initContext.lookup("java:/comp/env");
        DataSource datasource = (DataSource)envContext.lookup("jdbc/TestDB");
        Connection con = datasource.getConnection();
        ...
  3. For each application that uses the datasource and for which you want to configure a specific proxied-user, update the application's META-INF/context.xml file by adding a <ResourceLink> element that links the global Oracle datasource to the com.springsource.tcserver.oracle.OracleProxyDataSourceFactory factory. Use the username and password attributes of <ResourceLink> to configure the specific user you want this particular application to connect to the database as, via the proxy user. For example (relevant section shown in bold):

    <?xml version='1.0' encoding='utf-8'?>
    <Context>
        <WatchedResource>WEB-INF/web.xml</WatchedResource>
        <ResourceLink global="jdbc/TestDB" name="jdbc/TestDB"
               username="proxieduser" password="proxypassword"
               factory="com.springsource.tcserver.oracle.OracleProxyDataSourceFactory"/>
    </Context>

    When the application described by this context.xml file uses the jdbc/TestDB datasource, it will connect to the database first as the proxy user (default_user) and then open a proxy connection as the proxieduser user, with password proxypassword.

    Note

    For this feature to work correctly, you must update the context.xml files for each relevant application, not the global context.xml file located in the CATALINA_BASE/conf directory.

  4. For the changes to take effect, restart your tc Runtime instance, which in turn redeploys all relevant applications.

  5. If you have not already done so, create all required Oracle database users that match the usernames you configured in the context.xml and server.xml files. For example:

    create user default_user identified by password;
    create user proxieduser identified by proxypassword;
    grant dba to default_user;
    grant dba to proxieduser;
    ALTER USER proxieduser GRANT CONNECT THROUGH default_user AUTHENTICATED USING password;

    The preceding SQL statements show how the proxieduser connects to the Oracle database through default_user. These SQL statements are just examples; for complete descriptions of these statements, see your Oracle database documentation.