What I do with this is to NOT use the USER psuedo column directly but rather use an application context. This is not java specific, this works with any middle tier solution that logs in using a common username.



I would create a package and context:


create or replace context my_ctx using my_pkg;

create or replace package my_pkg
as
procedure set_ctx_val( p_name in varchar2, p_val in varchar2 );
end;
/

create or replace package body my_pkg
as

procedure set_ctx_val( p_name in varchar2, p_val in varchar2 )
as
begin
dbms_session.set_context( 'my_ctx', p_name, p_val );
end;
end;
/

Now, where I would normally use the USER psuedo column -- I will use:


nvl(sys_context( 'my_ctx', 'username' ),USER)


That is -- if the sys_context value of USER is set -- I use it, else I use the USER psuedo column.

Now, I can grant access on this pacakge MY_PKG to the finite set of "application schemas" that need to log in -- your middle tier user for example. Your middle tier must *know* who is talking to it (they have logged into your application in some fashion) so your middle tier can then call:

begin my_pkg.set_ctx_val( 'username', ? ); end;

It is doing a "set user" call if you will. Your fine grained access control will now see a value in sys_context( 'my_ctx', 'username' ) and will use that. Since you control access to this package, allowing only priveleged users to execute it -- this is "safe" (as long as you trust that account, which you have to since everyone is logging in with it).

You should go one step further and make sure the predicate:

if ( nvl(sys_context('my_ctx', 'username', user) = 'USER_INTRANET' ) then
return '1=0';
end;

is returned IF the user in the database is your middle tier user and the context has not been set.