This discussion is archived
3 Replies Latest reply: Sep 18, 2009 6:53 AM by 181444 RSS

maintneance of AUD$

EdStevens Guru
Currently Being Moderated
I know I've seen something on this before, but can't find it now. I have a daily job that deletes from aud$ anything more than 7 days old. Just a simple
begin
   delete from sys.aud$ where trunc(NTIMESTAMP# ) < trunc(sysdate -7);
end;
Of course that delete itself is audited, which is fine. But those audit records (the ones tracking DELETE FROM AUD$) are not being deleted after 7 days. AUD$ has records of DELETE FROM AUD$ dating back to 14-July. This isn't a big problem, but I'd like to be able to explain it to myself! I think I'm have a brain freeze ... ;-)
  • 1. Re: maintneance of AUD$
    damorgan Oracle ACE Director
    Currently Being Moderated
    What's to explain? Oracle clearly explains this in the docs:

    http://download.oracle.com/docs/cd/E11882_01/network.112/e10574/auditing.htm#DBSEG60621

    Note: DELETE, INSERT, UPDATE, and MERGE operations on SYS.AUD$ table are always audited. These audit records are not allowed to be deleted.
  • 2. Re: maintneance of AUD$
    EdStevens Guru
    Currently Being Moderated
    damorgan wrote:
    What's to explain? Oracle clearly explains this in the docs:

    http://download.oracle.com/docs/cd/E11882_01/network.112/e10574/auditing.htm#DBSEG60621

    Note: DELETE, INSERT, UPDATE, and MERGE operations on SYS.AUD$ table are always audited. These audit records are not allowed to be deleted.
    I knew it had to be there, and thought I had read it before, but for some reason I just wasn't seeing it to confirm my doubts! Like I said ... brain freeze.

    Thanks for getting me back on track.
  • 3. Re: maintneance of AUD$
    181444 Expert
    Currently Being Moderated
    Even thought the thread is marked as answered I would just like to point out that the reference states "When standard auditing is enabled (that is, you set AUDIT_TRAIL to DB or DB,EXTENDED), Oracle Database audits all data manipulation language (DML) operations, such as INSERT, UPDATE, MERGE, and DELETE on the SYS.AUD$ and SYS.FGA_LOG$ tables by non-SYS users."

    If you run the delete as user sys then I would expect no audit record to be recorded in sys.aud$ for the action. That is how our 9.2 system works. I do not think we configured auditing on any of our 10g systems so that I can verify our clean scripts work the same way. I will have to look into that as a separate issue.

    User sys activity can be logged to the aud directory but unless you specify auditing the sysdba activity the information written is very restricted in nature.

    HTH -- Mark D Powell --

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points