This discussion is archived
5 Replies Latest reply: Apr 4, 2013 1:40 PM by rp0428 RSS

Auditing question executing the TRUNCATE command.

user582263 Newbie
Currently Being Moderated
Hello.

We have some audit controls in place that interrogate SQL codes to determine whether an INSERT, UPDATE or DELETE command has been executed by a user. However, when a user executes a TRUNCATE command, an sudit record is not generated. Since I cannot find a specific code for Truncate, I was thinking that the code that caputes a DELETE command would work... but it doesn't appear that is the case.

We only have a very few users that can issue the TRUNCATE command, but I'd like to know if anyone knows of a specific code for TRUNCATE. Or if anyone has an idea how we could generate AUDIT records when a user does issue a TRUNCATE command.

Thanks!
  • 1. Re: Auditing question executing the TRUNCATE command.
    Kamran Agayev A. Oracle ACE Director
    Currently Being Moderated
    Did you test
    SQL>AUDIT TRUNCATE 
    - - - - - - - - - - - - - - - - - - - - -
    Kamran Agayev A. (10g OCP)
    http://kamranagayev.wordpress.com
  • 2. Re: Auditing question executing the TRUNCATE command.
    user097815 Journeyer
    Currently Being Moderated
    As a TRUNCATE statement deletes rows from a table, triggers associated with the table are not fired. Also, a TRUNCATE statement does not generate any audit information corresponding to DELETE statements if auditing is enabled. Instead, a single audit record is generated for the TRUNCATE statement being issued.

    you can take a look at this..
    refer to section called : Setting Auditing Options
    http://www.cs.uvm.edu/oracle9doc/server.901/a90117/audit.htm
  • 3. Re: Auditing question executing the TRUNCATE command.
    user097815 Journeyer
    Currently Being Moderated
    as kamran pointed...

    have u treid audit truncate;

    take a look at this blog...

    http://www.front-row-seat.com/articles/oracle_audit.html
  • 4. Re: Auditing question executing the TRUNCATE command.
    user12603 Newbie
    Currently Being Moderated
    There is certainly a lot of bad information floating around on this topic.

    "Audit table" will audit create table, drop table and truncate table - which is absolutely true. It is the ONLY way to reliably audit truncate table operations.

    Object auditing on a specific table (e.g. audit all on scott.mytab) never creates an audit record for truncation.

    You can audit "DROP ANY TABLE" which will create an audit record ONLY when someone with the DROP ANY TABLE system privilege attempts to truncate a table in another schema.

    Contrary to what some "experts" (including at least one ACE) think, "audit truncate table" is NOT valid syntax.

    "Audit truncate" does not return an error, but (as far as I can tell and I've actually tested it on 9.2.0.4, 10.2.0.1, 10.2.0.5 and 11.2.0.3) it does nothing. It creates no record in DBA_STMT_AUDIT_OPTS or DBA_PRIV_AUDIT_OPTS and it never produces a record in the audit trail. It is a half-baked auditing option that does not actually work - so, "audit truncate" should return an error!
  • 5. Re: Auditing question executing the TRUNCATE command.
    rp0428 Guru
    Currently Being Moderated
    DEAD THREAD!

    Please don't revive 4 year old threads. Let them rest in peace.

    It's good that you want to contribute to the forums but your contributions will be more useful if you respond to current unanswered threads.

Legend

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