Attaching an identifier to an Oracle session

Question

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?

Answer 1

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.

Answer 2

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