Go4Expert

Go4Expert (http://www.go4expert.com/)
-   Oracle (http://www.go4expert.com/articles/oracle/)
-   -   Audit Trail in Oracle (http://www.go4expert.com/articles/audit-trail-oracle-t307/)

shabbir 19May2005 15:33

Audit Trail in Oracle
 
I would be doing this in a new user and so first you need to have the administrative priviledges to create new user. By default the Scott user does not have the priviledges and you need to login using the System user.

Create New User In Oracle
Code: SQL

CREATE User <UserName> IDENTIFIED BY ;

grant him necessary priviledges
Code: SQL

GRANT CREATE session TO <username>;
 
 GRANT CREATE TABLE TO <username>;
 
 GRANT CREATE TRIGGER TO <username>;
 
 ALTER USER <username> QUOTA UNLIMITED ON

<tablespace> is normally 'SYSTEM'

Now create the tables

Code: SQL

CREATE TABLE Details (
    RegNo varchar (50) NOT NULL ,
    RegYear varchar (4) NOT NULL ,
    FullName varchar (50) NOT NULL ,
    FathersName varchar (50) NOT NULL ,
    Location varchar (250) NOT NULL ,
    Village varchar (250) NOT NULL ,
    PresentAdd varchar (500) NOT NULL ,
    CreatedBy varchar (50) NOT NULL ,
    DateCreated date NOT NULL ,
    UpdatedBy varchar (50) NULL ,
   DateUpdated date NULL,
   PRIMARY KEY (RegNo,RegYear)
 );
 
 CREATE TABLE LogDetails (
   LogType varchar (50) NULL ,
   RegNo varchar (50) NOT NULL ,
   RegYear varchar (4) NOT NULL ,
   FullName varchar (50) NOT NULL ,
   FathersName varchar (50) NOT NULL ,
   Location varchar (250) NOT NULL ,
   Village varchar (250) NOT NULL ,
   PresentAdd varchar (500) NOT NULL ,
   CreatedBy varchar (50) NOT NULL ,
   DateCreated date NOT NULL ,
   UpdatedBy varchar (50) NULL ,
   DateUpdated date NULL
 );

Now create the trigger

Update Trigger
Code: SQL

CREATE OR REPLACE TRIGGER AuditTrailUpdate
  AFTER UPDATE ON Details
  REFERENCING OLD AS myRow
  FOR EACH ROW
    INSERT INTO LogDetails(
    LogType,
    RegNo,
    RegYear,
    FullName,
    FathersName,
    Location,
    Village,
    PresentAdd,
    CreatedBy,
    DateCreated,
    UpdatedBy,
    DateUpdated
    )
    VALUES(
    'updated',
    :myRow.RegNo,
    :myRow.RegYear,
    :myRow.FullName,
    :myRow.FathersName,
    :myRow.Location,
    :myRow.Village,
    :myRow.PresentAdd,
    :myRow.CreatedBy,
    :myRow.DateCreated,
    :myRow.UpdatedBy,
    :myRow.DateUpdated
    )
/

Delete Trigger
Code: SQL

CREATE OR REPLACE TRIGGER AuditTrailDelete
  AFTER DELETE ON Details
  REFERENCING OLD AS myRow
  FOR EACH ROW
    INSERT INTO LogDetails(
    LogType,
    RegNo,
    RegYear,
    FullName,
    FathersName,
    Location,
    Village,
    PresentAdd,
    CreatedBy,
    DateCreated,
    UpdatedBy,
    DateUpdated
    )
    VALUES(
    'deleted',
    :myRow.RegNo,
    :myRow.RegYear,
    :myRow.FullName,
    :myRow.FathersName,
    :myRow.Location,
    :myRow.Village,
    :myRow.PresentAdd,
    :myRow.CreatedBy,
    :myRow.DateCreated,
    :myRow.UpdatedBy,
    :myRow.DateUpdated
    )
/


pradeep 16May2006 14:46

Re: Audit Trail in Oracle
 
Wonderful way to log table modifications. I wonder whether its possible to log the username which was used to do the modification, that would be really helpful.

shabbir 16May2006 22:31

Re: Audit Trail in Oracle
 
By just using the dual table you can get the details about the Meta data about the user like his username and log that.

jessemeister 17Dec2007 23:31

Re: Audit Trail in Oracle
 
Is there any way you can audit a user by creating a procedure instead when audit_trail is disabled?

How can one write a procedure with username, logon_time and once there is a username already there when run by the second time, not be displayed only new users who logged on to Banner? Can you please advice/help me on this. Thank you

lead.smart34 26Feb2008 17:48

Re: Audit Trail in Oracle
 
Quote:

Originally Posted by jessemeister
Is there any way you can audit a user by creating a procedure instead when audit_trail is disabled?

How can one write a procedure with username, logon_time and once there is a username already there when run by the second time, not be displayed only new users who logged on to Banner? Can you please advice/help me on this. Thank you

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

game 8Jan2009 21:33

Re: Audit Trail in Oracle
 
Hi!

Thanks for the info.
I was wondering if there is a way to pass external parameters to the triggers you showed in example.This is because I would like these external parameters (example some application id ) to appear in the audit trail as well.

Also if the above isn't possible , what other approach could I follow?

Thanks;
Labhesh.

Night club 22Oct2010 16:00

Re: Audit Trail in Oracle
 
The auditor held in main orecle all the form and feel its correct way,


All times are GMT +5.5. The time now is 09:07.