This discussion is archived
3 Replies Latest reply: Sep 28, 2012 5:29 AM by user13684468 RSS

Problems with standart Audit

user13684468 Newbie
Currently Being Moderated
Hi
I have such problem.
On my test 11.2 DB I switched on Standart Oracle Audit:
audit_trail=XML, audit_file_dest=/ora2/oracle/audit/TDB
Reboot DB.
I have test user TESTAUD with "GRANT CREATE TABLE TO TESTAUD;"
Then I issued from user SYS:
SQL> audit create table;
And from user TESTAUD:
SQL> create table .....bla bla bla;
Then I looked into view DBA_COMMON_AUDIT_TRAIL. It was nothiing except actions 100 (LOGIN), 101 (LOGOFF) about user TESTAUD.

As it's test DB, I reboot it again. Looked into DBA_COMMON_AUDIT_TRAIL, and what a mirracle - there was 1row with statement_type=CREATE TABLE!!!

Then I repeat my experiment.
From user TESTAUD I created new table. Then wait 10minutes. No more rows in DBA_COMMON_AUDIT_TRAIL. I reboot DB. And looked in view. And there were 2 rows with "CREATE TABLE"!!!

What's the matter????

Do I do something wrong?
  • 1. Re: Problems with standart Audit
    Harm Joris ten Napel Pro
    Currently Being Moderated
    hi,

    it looks like the auditing took place but the xml file may not have been immediately flushed to disk,
    something that would then happen after a reboot as you observed, when the audit file is not
    yet flushed other sessions checking DBA_COMMON_AUDIT_TRAIL (which for the XML part
    will dynamically list the files in the audit_file_dest) will not see it.

    If you want audit info to be flushed to the XML files immediately, issue:

    ALTER SYSTEM SET
    EVENT='46349 trace name context forever, level 1'
    COMMENT='re-enable flush of audit files' SCOPE=SPFILE;

    Reference: note 1358183.1      Audit Files Are Not Immediately Flushed To Disk

    Please note we are currently actively promoting the new support communities also, so if you want to get some
    feedback from your peers as well as the attention of Oracle support engineers, please go to

    https://communities.oracle.com/portal/server.pt/community/database_security_products/338

    This will put you in the Database Products Security community , but you can select others also from the left,

    Greetings,

    Harm ten Napel
    Oracle Support
  • 2. Re: Problems with standart Audit
    user13684468 Newbie
    Currently Being Moderated
    Thanks for reply, I'll check this.
  • 3. Re: Problems with standart Audit
    user13684468 Newbie
    Currently Being Moderated
    Thanks for your suggestion!
    It really helped!
    I set parameter and it works now!
    Thank you!

Legend

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