RE: Auditing

From: Brijesh Satdev <bsatdev_at_rolta.com>
Date: Fri, 22 Jun 2001 04:22:45 -0700
Message-ID: <F001.00333662.20010622042221@fatcity.com>

Hi
all,

In
what follows is the oracle documentation for auding by triggers:

Auditing Through Database Triggers

You
can use triggers to supplement the built-in auditing features of ORACLE. Although you can write triggers to record information similar to that recorded by the AUDIT command, do so only when you need more detailed audit information. For example, you can use triggers to provide value-based auditing on a per-row basis for tables.

Note:
In some fields, the ORACLE AUDIT command is considered a security audit facility, while triggers can provide a financial audit facility.

When
deciding whether to create a trigger to audit database activity, consider the advantages that the standard ORACLE database auditing features provide compared to auditing by triggers:

· Standard auditing options cover DML and DDL statements regarding all types of schema objects and structures. In contrast, triggers can audit only DML statements issued against tables.

· All database audit information is recorded centrally and automatically using the auditing features of ORACLE.

· Auditing features enabled using the standard ORACLE features are easier to declare and maintain and less prone to errors than are auditing functions defined through triggers.

· Any changes to existing auditing options can also be audited to guard against malicious database activity.

· Using the database auditing features, you can generate records once every time an audited statement is issued (BY ACCESS) or once for every session that issues an audited statement (BY SESSION). Triggers cannot audit by session; an audit record is generated each time a trigger-audited table is referenced.

· Database auditing can audit unsuccessful data access. In comparison, any audit information generated by a trigger is rolled back if the triggering statement is rolled back.

· Connections and disconnections, as well as session activity (such as physical I/Os, logical I/Os, and deadlocks), can be recorded by standard database auditing.

When
using triggers to provide sophisticated auditing, normally use AFTER triggers. By using AFTER triggers, you record auditing information after the triggering statement is subjected to any applicable integrity constraints, preventing cases where audit processing is carried out unnecessarily for statements that generate exceptions to integrity constraints.

When
you should use AFTER row vs. AFTER statement triggers depends on the information being audited. For example, row triggers provide value-based auditing on a per-row basis for tables. Triggers can also allow the user to supply a "reason code" for issuing the audited SQL statement, which can be useful in both row and statement-level auditing situations.

The
following trigger audits modifications to the EMP table on a per-row basis. It requires that a "reason code" be stored in a global package variable before the update. The trigger demonstrates:

· how triggers can provide value-based auditing

· how to use public package variables

Comments within the code explain the functionality of the trigger.



CREATE
TRIGGER audit_employee



AFTER
INSERT OR DELETE OR UPDATE ON emp



FOR
EACH ROW



BEGIN



/*
AUDITPACKAGE is a package with a public package



 variable REASON. REASON could be set by the



 application by a command such as EXECUTE



 AUDITPACKAGE.SET_REASON(reason_string). Note that a



 package variable has state for the duration of a



 session and that each session has a separate copy of



 all package variables. */



IF
auditpackage.reason IS NULL THEN



 raise_application_error(-20201,'Must specify reason with ',




'AUDITPACKAGE.SET_REASON(reason_string)');



END
IF;





/*
If the above conditional evaluates to TRUE, the



 user-specified error number and message is raised,



 the trigger stops execution, and the effects of the



 triggering statement are rolled back. Otherwise, a



 new row is inserted into the pre-defined auditing



 table named AUDIT_EMPLOYEE containing the existing



 and new values of the EMP table and the reason code



 defined by the REASON variable of AUDITPACKAGE. Note



 that the "old" values are NULL if triggering



 statement is an INSERT and the "new" values are NULL



 if the triggering statement is a DELETE. */



INSERT
INTO audit_employee VALUES



 (:old.ssn, :old.name, :old.job_classification, :old.sal,



 :new.ssn, :new.name, :new.job_classification, :new.sal,



 auditpackage.reason, user, sysdate );



END;

Optionally, you can also set the reason code back to NULL if you want to force the reason code to be set for every update. The following AFTER statement trigger sets the reason code back to NULL after the triggering statement is executed:



CREATE
TRIGGER audit_employee_reset



AFTER
INSERT OR DELETE OR UPDATE ON emp



BEGIN




auditpackage.set_reason(NULL);



END;

The
previous two triggers are both fired by the same type of SQL statement. However, the AFTER row trigger is fired once for each row of the table affected by the triggering statement, while the AFTER statement trigger is fired only once after the triggering statement execution is completed. 


Regards,
Brij.

-----Original Message-----From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Nirmal Kumar Muthu KumaranSent: Friday, June 22, 2001 4:12 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Auditing
 Hi Norrell,
 I just tested ur case.
 Can you tell me, how it's possible ,
 when doing auditing, oracle will refer to the sys.aud$ table itself, correct?.

  If i droped it (even there is a
  synonym on that name),
  Oracle giving an error, it's not able   to find the sys.aud$ table.
  Nirmal,

-----Original Message----- <FONT

    face=Arial size=1>From:   <FONT face=Arial 
    size=1>Norrell, Brian [SMTP:BNorrell_at_QuadraMed.com] <FONT 
    face=Arial size=1>Sent:   <FONT face=Arial 
    size=1>Thursday, June 21, 2001 6:33 PM <FONT face=Arial 
    size=1>To:     <FONT face=Arial 
    size=1>Multiple recipients of list ORACLE-L <FONT face=Arial 
    size=1>Subject:        <FONT 
    face=Arial size=1>RE: Auditing

    aud$ is one of the few system tables that can     have an owner other than sys. You can     create it under another schema (like system) and give sys a private     synonym pointing to system.aud$. Then put     the trigger on the alternate aud$
    create table system.aud$ as select * from     sys.aud$; drop table sys.aud$;
    create synonym sys.aud$ for system.aud$;     create trigger x on system.aud$ as ...

Brian Norrell Manager, MPI Development QuadraMed 511 E John Carpenter Frwy, Su 500 Irving, TX 75062
 (972) 831-6600
 -----Original Message----- Sent: Thursday, June 21, 2001 6:00 AM To: Multiple recipients of list ORACLE-L This will not work as you can't create triggers on sys objects. So tell me the way how i should create triggers on aud$.
 -----Original Message----- Sharma Sent: Thursday, June 21, 2001 2:56 PM To: Multiple
 recipients of list ORACLE-L
 i think u can use triggers such kind of auditing. create aud$ table with one more column, and try updating the column as soon user fires some sql.
 this is just a thought. could find another opinion.
 saurabh -----
 Original Message ----- To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Thursday, June 21, 2001 11:15 AM > Hi all there, > I am auditing a database where 400 users are concurrently accessing the > database.
 > Now in the table AUD$ we have all the records of the user transactions.But > is there any way to store what sort of query he is running so that i can see > the actual text of query which was fired 3 days before. > Regards, > Anand > > -- > Please see the official ORACLE-L FAQ: <A href="http://www.orafaq.com" target=_blank>http://www.orafaq.com > -- > Author: Anand
 > INET:
 karhadkar_at_rolta.com > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 >
 San Diego, California -- Public Internet access / Mailing Lists >

    > To REMOVE yourself from this mailing list,     send an E-Mail message > to:
    ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in     > the message BODY, include a line
    containing: UNSUB ORACLE-L > (or the     name of mailing list you want to be removed from). You may     > also send the HELP command for other     information (like subscribing).

Please see the official ORACLE-L FAQ: <A href="http://www.orafaq.com" target=_blank>http://www.orafaq.com -- Author: Saurabh Sharma INET: ssharma1_at_fcsltd.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Please see the official ORACLE-L FAQ: <A href="http://www.orafaq.com" target=_blank>http://www.orafaq.com -- Author: Anand INET: karhadkar_at_rolta.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: <A href="http://www.orafaq.com" target=_blank>http://www.orafaq.com
Author: Norrell, Brian INET: BNorrell_at_QuadraMed.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

Received on Fri Jun 22 2001 - 06:22:45 CDT