Using Proxy Authentication

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.

Key Ring Image:   

Contents

Using the DB session context

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

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

How to get things done ?

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:

  1. create a new project in JDeveloper
  2. download this jarfile and add it as a library in your projects properties
  3. create a database connection to use for this ADF project, based on the ANY_DOG database user you created using the SQL statements above
  4. create a new View Object, based on the following query:
    SELECT USER REAL_USER, SYS_CONTEXT('USERENV', 'PROXY_USER') PROXY_USER FROM DUAL
  5. create a new Application Module and add the VO to it
  6. 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
  7. 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.
  8. 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
  9. 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)

What's more ?

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.

Comments:

Hi Benjamin Firstly thank you for this article as it is just what we were looking for as we have a number of small applications that need to update data as individual users as appossed to the overall application module connection. I have created an example that works fine and indeed functions as expected so its all good there. However, there seems to be an issue with using this method of connecting with more than one application on the same application server. The second application using this method exceptions with an overall JBO 30003 and thus it cannot check out the application module. This is documented in SR 6866249.992 and is currently being looked into. As your article was at the start of this i wondered if you would any ideas or information regarding deploying more than one application using this method. Any help is greatly appreciated. Regards Tony

Posted by Tony Gray on April 09, 2008 at 11:21 PM PDT #

Tony, thanks for your interest in the article. I've taken a quick look at the SR and tried to reproduce it on my own AS, but did not experience the issue you describe. Have you tried to deploy the same application without configuring the proxy authentication connection pool manager ? Best regards, Benjamin

Posted by Benjamin De Boe on April 15, 2008 at 12:59 AM PDT #

Hi, Benjamin, Is it work with Toplink too? Can I setup Toplink as the same way as you setup ADFBC to work with proxy auth? Is there any documentation about it? thanks, Marcus.

Posted by Marcus Pontes on April 16, 2008 at 02:23 AM PDT #

Hi Marcus, ADF and TopLink are two different technologies, but TopLink supports Proxy Authentication as well. More can be found in the TopLink Developer's Guide: http://download.oracle.com/docs/cd/B32110_01/web.1013/b28218/daun.htm#CHDFACHD Best regards, Benjamin

Posted by Benjamin De Boe on April 20, 2008 at 09:21 PM PDT #

Hi Benjamin, First of all thank you for your great article, it gaves me nice ideas about how to manage my proxy connection. I've been trying to use it with my current project (EJB 3.0+Visual JSF+NetBeans 6.5), and I've got a problem that I dont know how to manage. Right now I've overload the OracleDataSource function public Connection getConnection(String arg0, String arg1) throws SQLException So it uses similar code to the one that you provide, and I also get the session user in a similar way: Map session = FacesContext.getCurrentInstance().getExternalContext().getSessionMap(); String user = (String)session.get("username"); So right now I got it working with the proxy authentication (I just checked using a select user as usuario from dual) The problem is that I don't understand very good the workflow, because if I define a connection pool of minimum 8 opened connection (It's the values by default of glassfish), it creates 8 connections with the same proxy user in a row. How can I force not to create all the connection once that the first user just connect? Maybe it's a little bit mess what I tried to explain, let me know if you understand the problem. Thank you in advance.

Posted by KILE on October 05, 2008 at 06:56 PM PDT #

KILE, thanks for your feedback. I haven't been using this type of technology lately, but I get the impression the behaviour you see is pretty much as it should be. As you're overriding this low level of connectivity (the OracleDataSource), you're making sure every access to a database connection will execute your bit of code. This means that the OC4Js connection pooling mechanisms, which are responsible for opening and maintaining connections for all applications in the pool, will also trigger your code. Hence, as soon as the connection pool is initiated, it will open those 8 connections (which you specified as the minimum) and as your code is executed, all of these will have the same proxy user. Essentially this is not a bad thing, because the connections are now all open and ready to be used. They indeed have a session initiated by that same first user, but as soon as someone else asks for a connection, your code will be called again and a new session will be opened on the physical connection, with the name of that new proxy user. It might look a bit weird, but the net result is what you wanted by specifying a minimum of 8 opened connections, I'd say. Does this answer help you? Best regards, Benjamin

Posted by Benjamin on October 23, 2008 at 10:52 PM PDT #

Hi Benjamin, Are you aware of any implementations, which would involve middle tier /database distributed name proxy authentication integrated with Oracle Enterprise User Security? Thank you, Janusz Muszak

Posted by Janusz Muszak on June 29, 2009 at 04:53 AM PDT #

How to get the ORA error if connection fail ?

Posted by Frankie on September 12, 2009 at 09:53 AM PDT #

Janusz, we certainly do have customers using OES in their production environments. For example, the New York State Department for Environmental Conservation uses OES together with Proxy Authentication and Virtual Private Database in a comprehensive security architecture. But that is just one example, as we have other customers using OES and VPD for their public web sites, making sure only authorized users stored in the LDAP can access the database. Best regards, Benjamin

Posted by benjamin.de.boe on September 28, 2009 at 06:07 PM PDT #

Frankie, I'm not sure what you mean by "if connection fail", but it should be in the application log files in your regular OC4J log directory. If you want to be able to catch and interpret the error from within the code yourself, you could use the getErrorCode() function of the java.sql.SQLException class. That should provide you with the actual ORA error code Best regards, Benjamin

Posted by benjamin.de.boe on September 28, 2009 at 06:10 PM PDT #

Hi Benjamin, I try to understand this concept and its usecase, pease kindly help me to clarify things : 1) I would like to use Oracle Audit Vault in our enterprise application environment, this is one of the drive to use Proxy Authentication, is this correct ? 2) Have you seen the implement of this Proxy Authentication and ADF BC together in production system ? Thank you very much, Krist

Posted by Krist on October 27, 2009 at 12:53 PM PDT #

Hi Krist, on your first question, I'm not an expert on Audit Volt, but I'd think using it together with Proxy Authentication and Enterprise User Security will certainly help you with getting a complete audit trail for any actions in the database, annotated with the actual LDAP user rather then just the DB user. As for the second one, see my previous post (replying to Janusz) for some references to production systems. Best regards, Benjamin

Posted by benjamin.de.boe on November 16, 2009 at 09:12 PM PST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Java EE Consultants - JHeadstart, ADF, JSF

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today