Using Proxy Authentication
By benjamin.de.boe on Jan 27, 2008
Do you have an application where every end user is a database user ? Are your application security or auditing systems built using database concepts like users and roles ? Or do you rely on Enterprise User Security, wiring up LDAP users and database users for an integrated security solution ?
If you'd answer any of these questions with "Actually yes", "To some extent" or even "I don't know, but our DBA says yes", then you probably don't like the idea that ADF Business Components and Java EE applications in general always connect to the database through a connection pool with just a single user. While that's great for performance and scaling, it really rules out a lot of nice database features that would ease your security and auditing needs. In fact, the problem boils down to the fact that you can have a very secure Java EE application that perfectly meets all security requirements, but uses a single database account that has complete access to every record in every table in your database schema. For the database, your very secure application just looks like one all-mighty but nameless user. As a Java EE best-practice-framework, ADF Business Components uses the same anonymous pooling techniques to connect to the DB and therefore suffers from the same problem. This post will show you how you can easily use Oracle DB Proxy Authentication to propagate the end user identity to the database.
Yes, I know many articles on the issue have been around for ages, but it took me some time to get things working in a 10.1.3 environment, so at least it makes me feel less dumb if I can summarize it for others.
The easiest way to propagate the identity of the end user and have access to it in the database, is by just storing it in the session context of your database connection. This approach is in fact already described in a previous post on Virtual Private Database and works perfectly well if your security machinery knows where to find this information in the DB session. Apart from being straightforward and saving you from reading this article any further, this approach also requires no additional ADF or DB configuration at all, supposing all the VPD policies are in place
Now, what if you don't have VPD available, or if you're not comfortable with the IP-setting approach described in the above-mentioned article because it hardcodes your OC4Js IP ? You can just turn to another feature of your most reliable friend the database: Proxy Authentication !
Proxy Authentication is an Oracle DB feature that allows you to create a database session for a particular user _within_ a database connection opened for someone else, for example a more general group account. In a Java EE scenario, this would mean that an end user opens a proxy session with his own, personal credentials on a database connection from the pool, opened with a generic DB account. Now you can go hardcore-secure and revoke all but connect rights from that generic account and grant the more relevant rights to the individual user accounts, eventually through roles. By doing so, you can benefit from all the nice features (ranging from simple grants on tables to user-based VPD and label security) offered by the database for enforcing a fine-grained security policy.
The more theoretical description of this approach can be found in chapter 16 of the Database Security Guide: Preserving User Identity in Multitiered Environments
To make Proxy Authentication work, you simply have to create a generic user (for the connection pool) and then grant the rights to connect through this user to the personal accounts.
CREATE USER ANY_DOG IDENTIFIED BY WELCOME;
GRANT CONNECT TO ANY_DOG;
CREATE USER GOOFY IDENTIFIED BY WELCOME;
GRANT CREATE SESSION TO GOOFY;
ALTER USER GOOFY GRANT CONNECT THROUGH ANY_DOG AUTHENTICATED USING PASSWORD;
The Goofy stuff you'll have to do for every end user account and might seem unattractive administrative overhead, but you'll typically use this for a high-security application where this overhead is considered worth the effort rather than a public internet application where any stray dog can register.
Now, for the ADF part, there are a few options. The simplest one is to just decorate the prepareSession() method of your ApplicationModule and open a proxy connection. A second option is to override the EnvInfoProvider used by ADF to obtain a database connection from the pool, as described in a fairly outdated white paper "How To Support Dynamic JDBC Credentials (actually, that solution does not use Proxy Authentication and authenticates with the personal users directly and thereby bypasses the connection pool and its performance gains). The final (and my favorite) option is to override the default ADF connection pool manager and make it open proxy sessions on top of the connections it returns.
The final option is (at least in my opinion) the most transparant, generic and least-code-involved one. You just make sure the custom ConnectionPoolManager implementation is in the classpath and edit a single setting in your Application Module configuration by right-clicking the AM and selecting Configurations > Edit > Properties and then replacing the jbo.ConnectionPoolManager parameter value with the custom implementation of your choice.
The sample code provided here has implementations for JDBC thin (oracle.cons.jbo.server.ProxyAuthConnectionPoolManager) and OCI drivers (oracle.cons.jbo.server.OCIProxyAuthConnectionPoolManager) that check whether a user object is present in the session scope (using ADFContext). This user object has to be of the supplied class oracle.cons.adf.view.ProxyAuthUser, which contains the current end users username and password. The ConnectionPoolManager implementations will then use these parameters to open a proxy session on top of the default connection retrieved from the connection pool. To complete the suite, the jarfile also contains a very simple backing bean you can integrate in your JSF login page for storing a ProxyAuthUser instance in the session.
So, to get started with this sample, you have to perform the following steps:
- create a new project in JDeveloper
- download this jarfile and add it as a library in your projects properties
- create a database connection to use for this ADF project, based on the ANY_DOG database user you created using the SQL statements above
- create a new View Object, based on the following query:
SELECT USER REAL_USER, SYS_CONTEXT('USERENV', 'PROXY_USER') PROXY_USER FROM DUAL
- create a new Application Module and add the VO to it
- create a login page with two input fields, which you bind to the username and password properties of a backing bean of class oracle.cons.adf.view.bean.ProxyAuthUserLoginBean. also add a login button and bind it to the performProxyUserLogin action method of the login bean
- create a second page on which you drag and drop the view object we created as a read-only form. This will allow you to check the actual user and proxy user for your database connection.
- wire up the pages in your faces-config navigation diagram and add the action outcome to navigate from the first to the second page as a managed bean property actionOnSuccess
- test the application. When you are directly accessing the second page, you'll be seeing ANY_DOG as the database user. If you first use the login page with the GOOFY user, you'll see that GOOFY is the actual database user and ANY_DOG the proxy user
You can also download a zipped JDev project that contains the result of the steps described above from here (the jsf-impl.jar and adf-faces-impl.jar files were removed from the WEB-INF/lib directory to minimize the archive size, so please copypaste them back into the project from some other web project before running)
So, this article showed you how you can easily enable Proxy Authentication in your ADF applications to preserve the users identity from the front end to the back end. If you're really up to it, you could also combine this approach with Enterprise User Security, wiring up users from your OID/LDAP to database users and allowing you to combine Proxy Authentication with default Java EE security mechanisms and possibly even SSO.
Currently, it's just a bit of sample code you can use to dress up JHeadstart if you want individual users to connect to the DB rather then an anonymous connection pool account. While the current ProxyAuthUser helper class extends the JHeadstart interface, you could easily externalize that and use this approach for ADF in general. The proposed option does not specifically integrate with the current security options in JHeadstart, but could be used in conjunction with for example the JHS custom security framework (since JHS 10.1.3.2) or even the JAAS flavour when you'd also use EUS. Or, if there'd be enough interest, it could be extended as an additional option within the JHeadstart framework. In any case, the discussion on the strongest or most integrated security configuration is definitely not over.