RE: Auditing

[Brijesh Satdev]

Title: RE: Auditing

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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Nirmal Kumar Muthu Kumaran
Sent: Friday, June 22, 2001 4:12 PM
To: Multiple recipients of list ORACLE-L
Subject: 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-----
From:   Norrell, Brian [SMTP:[EMAIL PROTECTED]]
Sent:   Thursday, June 21, 2001 6:33 PM
To:     Multiple recipients of list ORACLE-L
Subject:        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 <[EMAIL PROTECTED]>
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: http://www.orafaq.com
> --
> Author: Anand
>   INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: http://www.orafaq.com
--
Author: Saurabh Sharma
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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: http://www.orafaq.com
--
Author: Anand
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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: http://www.orafaq.com
--
Author: Norrell, Brian
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).