Enable auditing on object table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Enable auditing on object table

  1. #1
    Join Date
    Aug 2007
    Posts
    62

    Enable auditing on object table

    Hi,

    I tried to enable auditing on one of the tables using the steps below:
    1. run command
      Code:
      alter system audit_trail=true scope=spfile
      and restart database

    1. run command
      Code:
      audit delete, insert, update on test_user.test_table

    1. run command 'select * from dba_obj_audit_opts;'
      Results:
      Code:
      OWNER                          OBJECT_NAME                    OBJECT_TYPE
      ------------------------------ ------------------------------ -----------------
      ALT       AUD       COM       DEL       GRA       IND       INS       LOC
      --------- --------- --------- --------- --------- --------- --------- ---------
      REN       SEL       UPD       REF EXE       CRE       REA       WRI
      --------- --------- --------- --- --------- --------- --------- ---------
      FBK
      ---------
      TEST_USER                        TEST_TABLE                         TABLE
      -/-       -/-       -/-       A/A       -/-       -/-       A/A       -/-
      -/-       -/-       A/A       -/- -/-       -/-       -/-       -/-
      -/-


    After which i run the following sql:
    Code:
    SQL> insert into test_table values (1234, 'test test');
    and exit sqlplus.

    When i login as SYSDBA and check AUD$ table, the column 'SQLTEXT' contains null and does not have the sql which was run. However, the AUD$ table register user TEST_USER ran an INSERT statement on TEST_TABLE.

    Is there a parameter which must be turned on to solve this?

    Thanks in advance!

  2. #2
    Join Date
    Apr 2007
    Posts
    13
    gxangel,

    You will need to set the audit_trail to DB_EXTENDED and to restart the instance because the AUDIT_TRAIL parameter it is not a dynamic parameter.

    Look this:

    AUDIT_TRAIL.
    This has four possible values:
    • NONE (or FALSE) Database auditing is disabled, no matter what auditing
    you attempt to configure.
    • OS Audit records will be written to the operating system’s audit trail: the
    Application Log on Windows, or the AUDIT_FILE_DEST directory on Unix.
    • DB (or TRUE) The audit records are written to a data dictionary table,
    SYS.AUD$. There are views that let you see the contents of this table.
    • DB_EXTENDED As DB, but including information on the SQL statements
    with bind variables that generated the audit records.


    I hope I have helped you.

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Did you also run this as sysdba

    insert into test_table values (1234, 'test test');

    regards
    Hrishy

  4. #4
    Join Date
    Aug 2007
    Posts
    62
    Hi,

    Thanks for your help!
    I've changed
    Code:
    AUDIT_TRAIL = DB_EXTENDED
    and it shows the sql run. However, the actual values which was entered was not shown. Instead, it shows
    Code:
    update test_table set name=:"SYS_B_0" where id = :"SYS_B_1"
    Is there a way to see the actual SQL ran?
    All these insert & update statements are executed using TEST_USER.

    Thanks!

  5. #5
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Whats the parameter cursor_sharing set to ?

    regards
    Hrishy

  6. #6
    Join Date
    Aug 2007
    Posts
    62
    Hi Hrishy,

    Code:
    SQL> show parameter cursor_sharing
    
    NAME                                 TYPE
    ------------------------------------ --------------------------------
    VALUE
    ------------------------------
    cursor_sharing                       string
    FORCE
    Thank you!

  7. #7
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    I am not sure but you might have to try and set that to cursor_sharing=exact and then try again.

    regards
    Hrishy

  8. #8
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    Quote Originally Posted by gxangel
    Hi,

    Thanks for your help!
    I've changed
    Code:
    AUDIT_TRAIL = DB_EXTENDED
    and it shows the sql run. However, the actual values which was entered was not shown. Instead, it shows
    Code:
    update test_table set name=:"SYS_B_0" where id = :"SYS_B_1"
    Is there a way to see the actual SQL ran?
    All these insert & update statements are executed using TEST_USER.

    Thanks!
    that is the actual SQL ran, you wont be able to see the values directly from the audit values

  9. #9
    Join Date
    Aug 2007
    Posts
    62
    Hi,

    I did
    Code:
    alter system set cursor_sharing=exact scope=spfile;
    and i can see the exact sql being run.

    Thanks alot Hrishy!
    PS: Is there any implications of changing the cursor_sharing parameter?

  10. #10
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Offcourse there are implications you should read the performance tuning guide and also look at why this was set in your particular environment ?

    regards
    Hrishy

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