3

I would like to submit a question about creating an audit trail for specific tables on Oracle 11g database. We would like to track user's changes, on some specific tables, that records has been changed from the application (front end) side. Of course the first idea we came up with was to create manually audit tables and set triggers to track:

  • who is changing the data
  • what type of operation is it (I, U, D)
  • time of the operation

But I read that Oracle has in-build mechanism that can handle audit trails, but at this moment I don't know any details how is it working. So the main question is: "What is the best / most elegant, with easy and clear access to the data, way to perform audit tracking?"

DoeUS
  • 113
  • 13

1 Answers1

3

  • First of all your auditing should be on by setting your audit_trail parameter to a valid value like DB, DB_EXTENDED, XML, XML_EXTENDED or OS with scope=spfile and restart database.

  • Next, you need to use audit commands for what you want to track, such as :

    audit drop user by access;
audit drop any procedure by access;
audit drop any table by access;
audit audit system by access;
audit grant any privilege by access;
audit insert, update, delete on myschema.mytable by access;

To track sessions by audit-trail it's advisible to use 

Dbms_Session.Set_Identifier( :i_client );

to set client info, during connection phase of your program unit.

  • Dbms_Session.Set_Identifier sets the session's client id to the given value. This value can be used to identify sessions in v$session by means of v$session.client_identifier. It can also be used to identify sessions by means of sys_context('USERENV','CLIENT_IDENTIFIER').

  • This procedure is executable by PUBLIC.

    In addition ;

    Dbms_Application_Info.Set_Client_Info( :i_client );
Dbms_Application_Info.Set_Module( :i_modul,null ); 
-- :i_modul is the name of your module or program unit from which 
--  you are connecting to db .

    methods are also useful to monitor from gv$session view's client_info and module columns.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55