DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: auditing drop & alter table

  1. #1
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    does anyone know if database audit trail can audit drop table, alter any table?

    I have tried several times with no success at all, I did

    audit drop any table by user1

    not working

    audit drop any table

    not working

    audit create table by user1

    works

    well in one word, if I audit DDL only CREATE TABLE works, is this some limitation or sommething....? The documentation is not very helpful for auditing really

  2. #2
    Join Date
    Feb 2001
    Location
    Bombay,India
    Posts
    530
    Hi,
    Yes u cannot audit DROP TABLE.This is because when you create a table you are the owner of the table and so you have all the priveleges.If you want to audit CREATE,DROP and other DDL commands write a database trigger on it.The database trigger works fine on Oracle 8.1.5 and higher versions.
    In case of any help please be free to ask me at rohitsn@altavista.com

    Regards,
    Rohit Nirkhe ,Oracle DBA,OCP 8i
    rohitsn@altavista.com

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well I want to audit statement (statement auditing) drop, alter, truncate & alter table,

    according to 8.1.7 SQL Ref table 8-3 when you do

    audit table

    it audits DROP, TRUNCATE & ALTER

    From 8.1.7 Concept guide, Chapter 28

    "Statement Auditing:
    Statement auditing is the selective auditing of related groups of statements that fall into two categories:
    DDL statements, regarding a particular type of database structure or schema object, but not a specifically named structure or schema object (for example, AUDIT TABLE audits all CREATE and DROP TABLE statements)"



    [Edited by pando on 01-18-2002 at 03:12 AM]

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Pando, I think you are mixing two auditing concepts: one is "SQL statement auditing" and other is "priviledge usage auditing". In your last post you are quoting statement auditing, while in your first post you are complaining about priviledge auditing not working.

    Let's see:
    audit drop any table by user1

    not working
    This is example of priviledge usage auditing. An audit trail record will be recorded only if the user1 will use drop any table system priviledge when droping the table. That means that if he drops one of his own tables the audit trail will not be created, because he doesn't need "drop any table" priviledge for that. So did you test this by droping a table from some other user's schema? It should write an audit trail.

    audit drop any table

    not working
    Same as the abowe but this time valid for any user. Again audit trail is created only when table from someone elses schema is droped.

    audit create table by user1

    works
    This is another type of auditing - statement auditing. This time audit trail will be created whenever user1 uses "drop table" statement, no matter if the table is in his own's schema or not.

    I think you performed those DROPs and CREATEs on user's own schema, that is why it seemed that the first two are not working while the last one is.

    There is one odd thing with DROP TABLE and TRUNCATE TABLE statement auditing. You can't specify any of those two actions to be audited explicitely (ORA-956), like you can with CRATE TABLE. So you can't use "AUDIT DROP TABLE;" command. You can only set auditing for DROP and TRUNCATE by isuing "AUDIT TABLE;", which incorporates CREATE, TRUNCATE and DROP. So by setting "AUDIT TABLE;" audit trial will be created whenever someone isues CREATE TABLE, DROP TABLE or TRUNCATE TABLE statement.

    BTW, I just noticed one strange thing on my test DB (8.1.7.1.2). AUDIT_TRIAL is set to TRUE. When I started auditing logins/logoffs by isuing "AUDIT SESSION", every new session has been audited immediately. But if I use any other auditing command (like "AUDIT TABLE;"), the table statements ussage was not audited until I restarted the database. After that everything went normal and audit trail has started to reflect the changes. I found this to be true for every auditing statement, except for AUDIT SESSION. I haven't found anything regarding this unusual behaviour in the documentation, so I think this is a bug. Can anyone else reproduce this?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    it's working now, I changed audit_trail=db to audit_trail=true and connected as sys with sysdba to audit the statements and works

    I thought audit_trail has to be none, db or os?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width