In the Amis Technology corner Lucas jellema is discussing the problem with Oracle’s USER: [Standard for Database Development - Getting rid of USER from PL/SQL and SQL - no longer is USER equivalent to End User][]. Many applications use USER to determine who’s logged on to the database. Security is based on USER and roles. But this only works if every user actually logs in using his own account.
Many web application, including j2ee application, use connection pooling and proxy users. This means that all users of your applications are connected to the database using the same database user. This user is often called the [Big Application User][].
Lucas’s solution is to remove all use of USER in your database code, by setting the username in your application context. We’ve run into the same problem, and we’ve applied the same solution for the project i’m currently working on. If you’re still using an Oracle 8 database, i believe this is the best solution.
However, i think Oracle 10 offers a better solution: [Proxy Users][]. You can use Oracle’s proxy users to connect to Oracle through one Big Application User and still pretend to be someone else. The big advantage: select USER in the database works as expected. Auditing still works, grants and roles still work.
For example, suppose your application always connects to oracle using WEBAPP_USER. This WEBAPP_USER now informs Oracle that the actual user is SCOTT. Previously “select USER from DUAL” would return WEBAPP_USER, but using proxy users in Oracle this query will return SCOTT.
More info: [AskTom: Proxy Users!][], [How-To Configure and Use Proxy-authentication with OC4J 10g (10.1.3) Data Sources][], Oracle Jdbc Faq: [What is proxy authentication?][].
Update: See my next post for an example: [Oracle Proxy Users by example][].
[Standard for Database Development - Getting rid of USER from PL/SQL and SQL - no longer is USER equivalent to End User]: http://technology.amis.nl/blog/index.php?p=777 [Big Application User]: http://download-west.oracle.com/docs/cd/B14117_01/network.101/b10773/apdvntro.htm#1006171 [Proxy Users]: http://download-west.oracle.com/docs/cd/B14117_01/network.101/b10773/admnauth.htm#1006366 [AskTom: Proxy Users!]: http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:21575905259251 [How-To Configure and Use Proxy-authentication with OC4J 10g (10.1.3) Data Sources]: http://www.oracle.com/technology/tech/java/oc4j/1013/howtos/how-to-ds-proxy/doc/how-to-ds-proxy.html [What is proxy authentication?]: http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq_0.htm#05_14 [Oracle Proxy Users by example]: http://www.it-eye.nl/weblog/2005/09/12/oracle-proxy-users-by-example/