[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 241
  • Last Modified:

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
0
vsarma50
Asked:
vsarma50
  • 3
  • 2
1 Solution
 
MarioAlcaideCommented:
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
0
 
vsarma50Author Commented:
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.

0
 
MarioAlcaideCommented:
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
0
 
vsarma50Author Commented:
Hi,
I could able to do this with a function as I need to keep track of the table along with data what data is tried to access. so it goes some thing like this. as I get multiple rows for every query iam just grouping with the access date and deleting the duplicate rows.

thanks for all the responses.

create or replace function testaudit(table_name  in varchar2)
return number
is
datetemp VARCHAR2(100);
  pragma autonomous_transaction;
begin
select to_CHAR(sysdate,'dd-mon-yyyy hh12:mi:ss') into datetemp from dual;

Insert into testaudit_log(USER_NAME,TABLE_NAME,empnum_and_name,ACCESSED_DATE)
VALUES
(nvl(v('APP_USER'),user),
SUBSTR(table_name,1,14),
SUBSTR(TABLE_NAME,17),
DATETEMP);

delete from testaudit_log
    where rowid in ( select rid
                         from ( select rowid rid,
                                       row_number() over
                                         (partition by accessed_date order by rowid) rn
                                  from testaudit_log
                              )
                       where rn <> 1 );
  commit;
  return 0;
end;


0
 
vsarma50Author Commented:
this is what my requirement and it worked.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now