csreeni
asked on
Trigger to capture SQL statement and user info
Gurus,
I have this urgent req.
I need to come up with a wrapper to audit DML activities on specific tables ( I may be calling package/procedure on each of those table's triger) by some users after checking their profile.
Its been very long time I have written pl/sql code, Can you guys help me come up with some thing in short time.
I need database trigger , which calls the wrapper for DML activity of specific table.
It should be an autonomous transaction.
I need to log the username, timestamp, terminal, machine, type of DML operation , and SQL statement that user issued.
Appreciate your help sincererly,
Sri
I have this urgent req.
I need to come up with a wrapper to audit DML activities on specific tables ( I may be calling package/procedure on each of those table's triger) by some users after checking their profile.
Its been very long time I have written pl/sql code, Can you guys help me come up with some thing in short time.
I need database trigger , which calls the wrapper for DML activity of specific table.
It should be an autonomous transaction.
I need to log the username, timestamp, terminal, machine, type of DML operation , and SQL statement that user issued.
Appreciate your help sincererly,
Sri
In a sepatrate case it's possible based on statement based trigger using V$session, V$sql identifying session_id.
To solve it systematically I'd recommend you to set on auditing.
To solve it systematically I'd recommend you to set on auditing.
Check out auditing:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/audit.htm#1108
If you don't wish to go to that level, you should be able to come up with your own using triggers and PL/SQL. Just beware that your own can always be bypassed by any privledged/knowledgable user (then again, so can auditing).
This doesn't capture the SQL but this can be added with a little more work (just don't have time right now to get this part).
-------------------------- ---------- ---------- --
drop table myAudit;
create table myAudit(
username varchar2(100),
whichtable varchar2(100),
DMLtimestamp varchar2(100),
terminal varchar2(100),
ipaddress varchar2(100),
osuser varchar2(100),
DMLoperation varchar2(100)
)
/
drop table tab1;
create table tab1 (
col1 varchar2(100)
)
/
create or replace trigger tab1_trig
after insert or update or delete on tab1
for each row
declare
PRAGMA AUTONOMOUS_TRANSACTION;
DMLop varchar2(6);
begin
if inserting then DMLop := 'Insert';
elsif deleting then DMLop := 'Delete';
elsif updating then DMLop := 'Update';
else DMLop := '???';
end if;
insert into myAudit values(
sys_context('userenv', 'session_user'),
'TAB1',
systimestamp,
sys_context('userenv', 'terminal'),
sys_context('userenv', 'ip_address'),
sys_context('userenv', 'os_user'),
DMLop
);
commit;
end;
/
show errors
insert into tab1 values('a');
insert into tab1 values('b');
update tab1 set col1 ='Z' where col1='b';
delete from tab1 where col1='Z';
commit;
select * from myAudit;
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/audit.htm#1108
If you don't wish to go to that level, you should be able to come up with your own using triggers and PL/SQL. Just beware that your own can always be bypassed by any privledged/knowledgable user (then again, so can auditing).
This doesn't capture the SQL but this can be added with a little more work (just don't have time right now to get this part).
--------------------------
drop table myAudit;
create table myAudit(
username varchar2(100),
whichtable varchar2(100),
DMLtimestamp varchar2(100),
terminal varchar2(100),
ipaddress varchar2(100),
osuser varchar2(100),
DMLoperation varchar2(100)
)
/
drop table tab1;
create table tab1 (
col1 varchar2(100)
)
/
create or replace trigger tab1_trig
after insert or update or delete on tab1
for each row
declare
PRAGMA AUTONOMOUS_TRANSACTION;
DMLop varchar2(6);
begin
if inserting then DMLop := 'Insert';
elsif deleting then DMLop := 'Delete';
elsif updating then DMLop := 'Update';
else DMLop := '???';
end if;
insert into myAudit values(
sys_context('userenv', 'session_user'),
'TAB1',
systimestamp,
sys_context('userenv', 'terminal'),
sys_context('userenv', 'ip_address'),
sys_context('userenv', 'os_user'),
DMLop
);
commit;
end;
/
show errors
insert into tab1 values('a');
insert into tab1 values('b');
update tab1 set col1 ='Z' where col1='b';
delete from tab1 where col1='Z';
commit;
select * from myAudit;
ASKER
we set up auditing for DDL activities, but DML on some critical tables need to be captured. We had to opt for triggers, as auditing can't be done at object and user level and can't capture the actual statement. Capturing the statement part looks to be tricky.
I can start with what slight suggested. Try to get session id as jan suggested.
If any could help me with that, its great. I am also short of time on this -:)
I can start with what slight suggested. Try to get session id as jan suggested.
If any could help me with that, its great. I am also short of time on this -:)
Not sure a simple trigger will work for you.
To get the entire SQL you can use:
select sql_text from v$sqltext
where address = (select sql_address from v$session where AuDsid = SYS_CONTEXT('USERENV','SES SIONID'))
order by piece ;
Where I think it will break down is whoever fires the trigger will need select on the V$ views. Also, BIND variables don't show up in v$sqltext.
To get the entire SQL you can use:
select sql_text from v$sqltext
where address = (select sql_address from v$session where AuDsid = SYS_CONTEXT('USERENV','SES
order by piece ;
Where I think it will break down is whoever fires the trigger will need select on the V$ views. Also, BIND variables don't show up in v$sqltext.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
hi
the best ever way to capture these is auditing but as per u require the wrapper for auditing i think
create a table with all details and with dml trigger u can insert the values into the table with PRAGMA AUTONOMOUS_TRANSACTION ...
the slightwv has given the perfect answer ..... just make it as PRAGMA AUTONOMOUS_TRANSACTION thats all
regards
the best ever way to capture these is auditing but as per u require the wrapper for auditing i think
create a table with all details and with dml trigger u can insert the values into the table with PRAGMA AUTONOMOUS_TRANSACTION ...
the slightwv has given the perfect answer ..... just make it as PRAGMA AUTONOMOUS_TRANSACTION thats all
regards
create table myAudit(
username varchar2(100),
whichtable varchar2(100),
DMLtimestamp varchar2(100),
terminal varchar2(100),
ipaddress varchar2(100),
osuser varchar2(100),
DMLoperation varchar2(100)
)
/
drop table tab1;
create table tab1 (
col1 varchar2(100)
)
/
create or replace trigger tab1_trig
after insert or update or delete on tab1
for each row
declare
PRAGMA AUTONOMOUS_TRANSACTION;
DMLop varchar2(6);
begin
if inserting then DMLop := 'Insert';
elsif deleting then DMLop := 'Delete';
elsif updating then DMLop := 'Update';
else DMLop := '???';
end if;
insert into myAudit values(
sys_context('userenv', 'session_user'),
'TAB1',
systimestamp,
sys_context('userenv', 'terminal'),
sys_context('userenv', 'ip_address'),
sys_context('userenv', 'os_user'),
DMLop
);
commit;
end;
/
show errors
insert into tab1 values('a');
insert into tab1 values('b');
update tab1 set col1 ='Z' where col1='b';
delete from tab1 where col1='Z';
commit;
select * from myAudit;
username varchar2(100),
whichtable varchar2(100),
DMLtimestamp varchar2(100),
terminal varchar2(100),
ipaddress varchar2(100),
osuser varchar2(100),
DMLoperation varchar2(100)
)
/
drop table tab1;
create table tab1 (
col1 varchar2(100)
)
/
create or replace trigger tab1_trig
after insert or update or delete on tab1
for each row
declare
PRAGMA AUTONOMOUS_TRANSACTION;
DMLop varchar2(6);
begin
if inserting then DMLop := 'Insert';
elsif deleting then DMLop := 'Delete';
elsif updating then DMLop := 'Update';
else DMLop := '???';
end if;
insert into myAudit values(
sys_context('userenv', 'session_user'),
'TAB1',
systimestamp,
sys_context('userenv', 'terminal'),
sys_context('userenv', 'ip_address'),
sys_context('userenv', 'os_user'),
DMLop
);
commit;
end;
/
show errors
insert into tab1 values('a');
insert into tab1 values('b');
update tab1 set col1 ='Z' where col1='b';
delete from tab1 where col1='Z';
commit;
select * from myAudit;
Hi
1. enable the oracle auditing option to view the sql and session information
1. enable the oracle auditing option to view the sql and session information
ASKER
Is there any function that gives me the name of triggering table.... in our case tab1
I tried dbms_stanadard.dictionary_ obj_name
-- It works fine when I do the operation as owner, but if I issue command on user.tab1
then its not logging any thing.
tried ora_dict_obj_name too.....
Any ideas!
Thx
I tried dbms_stanadard.dictionary_
-- It works fine when I do the operation as owner, but if I issue command on user.tab1
then its not logging any thing.
tried ora_dict_obj_name too.....
Any ideas!
Thx
I believe you will have to hard code the table name in the trigger itself. Can you explain whay you would need this since the trigger is table specific? I'm not aware of any system level trigger that will work for you.
ASKER
I am calling a standard package/procedure in trigger. I may have trigger on multiple tables, calling this same package.
All you need to do is hardcode the parameter to the package in the trigger.
for example:
create or replace procedure bubba(table_name in varchar2) ...
create or replace trigger tab1_trigger
...
bubba('TAB1');
...
create or replace trigger tab2_trigger
...
bubba('TAB2');
...
for example:
create or replace procedure bubba(table_name in varchar2) ...
create or replace trigger tab1_trigger
...
bubba('TAB1');
...
create or replace trigger tab2_trigger
...
bubba('TAB2');
...
ASKER
Thank you very much...
What you are asking for is a TRACE!
Set trace parameter's to on (true) and use tkprof to analyze the trace file.