views:

178

answers:

2

My Java application uses db sessions, via Hibernate.

What I want to do is somehow, when I create a session in my app, is to identify that session as an 'application' session. The reason is that I wish (via a before trigger) to restrict the updates that users can make to a table, while giving the application carte-blanche to do what it likes.

In other words, my trigger does something like this:

if(user is an application)
   allow update
else
   raise oracle error
end if

Anyone know the best way to do this?

+1  A: 

Inside your trigger write the following:

select 
  terminal, 
  username, 
  osuser, 
  program 
into 
  svTerminal, svUserName, svOSUser, svProgram

from
  v$session 
where 
  audsid=SYS_CONTEXT('USERENV','SESSIONID');

if svProgram <> 'MyApplicationTitle' then
  raise oracle error
end if;

You can also see what other variables are available if you run this query:

select * form v$session;

if the trigger doesn't compile you should grant select permission to the user that has the trigger for v$session. You do that by executing the following as system:

grant select on sys.v_$session to <username>;

Update: The underscore in v_$session is not a mistake. This is the real name inside sys where the grant can be given on. The v$session is an alias you can use in queries.

Petros
This is very easy to fool. You can rename for instance sqlplusw.exe to MyApplicationTitle and the trigger won't notice this.
tuinstoel
+3  A: 

It might be more simple to grant the privileges to a role and then have the application set that role.

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_10004.htm#sthref9521

David Aldridge