Help needed for BEFORE DELETE Trigger

[rchenna]

Here is the scenario:

I have a web application. Users need to login to access this application. Oracle 10g is the back end database for this application.

Application User -> Joe
Database schema owner -> focal_prod

Joe logged into the web application and trying to delete an employee from EMP table. I need to audit this action and create a record in EMP_AUDIT table. I need to keep Joe (application user) name in this record under DELETED_BY column. How do I pass Joe name to the database trigger?

Thanks in advance,
Rao

[PAVB]

here is a tip...

select user from dual;

[rchenna]

Your SQL statement will give focal_prod. I would like to get Joe

[PAVB]

Your SQL statement will give focal_prod. I would like to get Joe

huh?

It returns the current user --e.g. Oracle account used to log into the database.

If all your users are login in into the database using a generic account like focal_prod... you might be in troubles, that's bad, veeeery bad mojo.

On the other hand, if your application is handling login I would assume the application is validating such a username against a table, all you have to do is capture the app username and move it along. I know, a little more complicated but as I said "you might be in troubles".

[rchenna]

Your comment
--
all you have to do is capture the app username and move it along.
--
This is what I am unable to implement. How to I pass this app username to database trigger?

[PAVB]

Can't you just create an Oracle account matching each Application account and let the each user connect to the database using that account instead of relying in a generic account?

Generic accounts look like a great and easy solution until you get serious about security or auditing.

[pando]

you could resolve it using a context, just that before you start doing anything you have to set the context for your session so you can reference it during the session

create table myuser (username varchar2(30));

insert into myuser values ('LSC');

create or replace package app_sec_pkg
is
procedure set_app_user;
end;
/

create or replace package body app_sec_pkg
is
	procedure set_app_user
	is
		l_username	myuser.username%type;
	begin
		select	username
		into	l_username
		from	myuser
		where	username = sys_context('USERENV', 'SESSION_USER');
		dbms_session.set_context('APPENV_CTX', 'APPUSER', l_username);
	end set_app_user;
end;
/

create context appenv_ctx using app_sec_pkg;

connect lsc/scott
exec app_sec_pkg.set_app_user

select  sys_context('APPENV_CTX', 'APPUSER') from dual;

SYS_CONTEXT('APPENV_CTX','APPUSER')
--------------------------------------------------------------------------------
LSC

myuser would be your security table

sys_context('USERENV', 'SESSION_USER') should be changed but for the sake of an example I have used it as such. In your case username should be supplied by your application, but the idea is to use a context and set attributes such as APPUSER and you can refer to it during your sessions by using sys_context('APPENV_CTX', 'APPUSER') calls in your trigger for example