Audit Trail in Oracle

Discussion in 'Oracle' started by shabbir, May 19, 2005.

  1. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,375
    Likes Received:
    388
    Trophy Points:
    83
    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:
    Create User <UserName> Identified by ;
    grant him necessary priviledges
    Code:
     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:
    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:
    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:
    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
    	)
    /
    
     
  2. pradeep

    pradeep Team Leader

    Joined:
    Apr 4, 2005
    Messages:
    1,645
    Likes Received:
    87
    Trophy Points:
    0
    Occupation:
    Programmer
    Location:
    Kolkata, India
    Home Page:
    http://blog.pradeep.net.in
    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.
     
  3. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,375
    Likes Received:
    388
    Trophy Points:
    83
    By just using the dual table you can get the details about the Meta data about the user like his username and log that.
     
  4. jessemeister

    jessemeister New Member

    Joined:
    Dec 17, 2007
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    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
     
  5. lead.smart34

    lead.smart34 New Member

    Joined:
    Feb 14, 2008
    Messages:
    77
    Likes Received:
    0
    Trophy Points:
    0
    www.oracle-base.com/articles/8i/Auditing.php
     
  6. game

    game New Member

    Joined:
    Jan 8, 2009
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    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.
     
  7. Night club

    Night club New Member

    Joined:
    Oct 22, 2010
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    0
    The auditor held in main orecle all the form and feel its correct way,
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice