asked on

dba_common_audit

I have a requirement in my application whcih is developed in APex 4.0.
when a user is logged in and access(select stataement) the table through the apex
want to track the audit. I tried with DBA_COMMON_AUDIT_TRAIL
but problem is it is always showing as APEX_PUBLIC_USER as a generic one.
but i want to capture who ever is logged into the system with his id.
how can we accomplish this. any trigger will do this. please advice.
thanks very much

MarioAlcaide

You should use a trigger with the after logon clause, like this:

CREATE OR REPLACE TRIGGER LOG_ON AFTER
LOGON ON DATABASE
BEGIN
execute immediate 'insert into bla bla bla';
END;

And insert your desired info

vsarma50

ASKER

I want to now the date and time and what is the user name , who did last dml operation done on a table.(select,insert,update,delete) but the trigger with logon option triggered from apex is taking the generic userid and how to caputre the table accesssed information.
for eg. if any user is trying to access employee table i want to track it in the audit table.
any suggestion please.

MarioAlcaide

Ok, then you can audit the desired operations on the table like this:

AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY fireid BY ACCESS;

For more info, you can check this link

http://www.oracle-base.com/articles/8i/Auditing.php

ASKER CERTIFIED SOLUTION

vsarma50

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

vsarma50

ASKER

this is what my requirement and it worked.