This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Feb 18, 2013 7:25 AM by user15801 RSS

SYS.AUD$ empty on table auditing

user15801 Newbie
Currently Being Moderated
Hi:

I am trying to audit a particular table on 11g (11.2.0.3.0), but I may be missing something. Here is what I've done so far:

Changed init file to

audit_trail = DB

in sqlplus:

SQL> audit INSERT, UPDATE, DELETE ON ap.ap_supplier_sites_all;

-----the audit parameters are as follow

SQL> show parameter audit

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u02/oracle/OMACRP2/db/tech_st/11.2.0/rdbms/audit
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB

What am I missing?

Thanks a lot and cheers,
AJ
  • 1. Re: SYS.AUD$ empty on table auditing
    vlethakula Expert
    Currently Being Moderated
    check in dba_audit_trail

    audit INSERT, UPDATE, DELETE ON ap.ap_supplier_sites_all by access;
  • 2. Re: SYS.AUD$ empty on table auditing
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    The audit trail is stored in the base table SYS.AUD$.
    It's contents can be viewed in the following views:
    · DBA_AUDIT_TRAIL
    · DBA_OBJ_AUDIT_OPTS
    · DBA_PRIV_AUDIT_OPTS
    · DBA_STMT_AUDIT_OPTS
    · DBA_AUDIT_EXISTS
    · DBA_AUDIT_OBJECT
    · DBA_AUDIT_SESSION
    · DBA_AUDIT_STATEMENT
    · AUDIT_ACTIONS
    · DBA_AUDIT_POLICIES
    · DBA_AUDIT_POLICY_COLUMNS
    · DBA_COMMON_AUDIT_TRAIL
    · DBA_FGA_AUDIT_TRAIL (FGA_LOG$)
    · DBA_REPAUDIT_ATTRIBUTE
    · DBA_REPAUDIT_COLUMN
    Check Oracle Documentation
    http://docs.oracle.com/cd/B10501_01/server.920/a96521/audit.htm
  • 3. Re: SYS.AUD$ empty on table auditing
    TSharma-Oracle Guru
    Currently Being Moderated
    If you changed this parameter in init file, you must have to restart your database in order that parameter to be in affect. Restart your database and you should be good.

    You do not need to restart the database if you change the auditing of objects. You only need to restart the database if you made a universal change, such as turning on or off all auditing or changing the destination of the audit trail operating system files.
  • 4. Re: SYS.AUD$ empty on table auditing
    user15801 Newbie
    Currently Being Moderated
    Thanks guys, I'll reply in order:

    == vlethakula ==

    dba_audit_trail
    dba_fga_audit_trail
    fga_log$

    are all empty, even after using your audit instruction

    == Osama_mustafa ==

    I haven't checked in all the tables you mentioned but I did check in these

    select * from DBA_AUDIT_TRAIL;
    select * from DBA_AUDIT_SESSION;
    select * from DBA_AUDIT_EXISTS;
    select * from DBA_AUDIT_OBJECT;
    select * from DBA_AUDIT_POLICIES;
    select * from DBA_AUDIT_POLICY_COLUMNS;
    select * from DBA_AUDIT_STATEMENT;

    and they are all empty.

    == TSharma ==
    Sorry, I forgot to mention it, but I have already bounced the DB after making the change to the init file.

    Thanks.
  • 5. Re: SYS.AUD$ empty on table auditing
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Pl post OS and EBS versions.

    Has any DML been executed on this table after auditing was turned on ?

    HTH
    Srini
  • 6. Re: SYS.AUD$ empty on table auditing
    TSharma-Oracle Guru
    Currently Being Moderated
    Can you post the output of this statement?

    select * from dba_stmt_audit_opts
    union
    select * from dba_priv_audit_opts;
  • 7. Re: SYS.AUD$ empty on table auditing
    user15801 Newbie
    Currently Being Moderated
    Srini - we are on RHL 5, EBS R12 (thought the application is currently down) and DB is 11g (11.2.0.3)

    Yes, I executed the below

    update ap.ap_supplier_sites_all
    set city = 'SUPER_FREEEEEMONT'
    where vendor_id = 1010;

    and committed it.

    TSharma -

    SQL> select * from dba_stmt_audit_opts
    union
    select * from dba_priv_audit_opts;

    no rows selected


    Thanks again and cheers.
  • 8. Re: SYS.AUD$ empty on table auditing
    TSharma-Oracle Guru
    Currently Being Moderated
    This is interesting. There are no rows in dba_stmt_audit_options. It means options are not set.I hate to ask but can you re run this statement and post the result.

    audit INSERT, UPDATE, DELETE ON ap.ap_supplier_sites_all by access;

    also, run this and check if you see any record in this view.

    select * from dba_obj_audit_opts
    union
    select * from dba_priv_audit_opts;
  • 9. Re: SYS.AUD$ empty on table auditing
    krishan Explorer
    Currently Being Moderated
    Set resource_limit=true at db end.

    Its a dynamic parameter.
  • 10. Re: SYS.AUD$ empty on table auditing
    user15801 Newbie
    Currently Being Moderated
    SQL> audit INSERT, UPDATE, DELETE ON ap.ap_supplier_sites_all by access;

    Audit succeeded.

    SQL> update ap.ap_supplier_sites_all
    set city = 'SUPER_FREMONT'
    where vendor_id = 1010;

    1 row updated.

    SQL> commit;

    Commit complete.

    SQL> select * from dba_priv_audit_opts;

    no rows selected

    SQL> select * from dba_obj_audit_opts;

    OWNER OBJECT_NAME
    ------------------------------ ------------------------------
    OBJECT_TYPE ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE
    ----------------------- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
    REA WRI FBK
    --- --- ---
    AP AP_SUPPLIER_SITES_ALL
    TABLE -/- -/- -/- A/A -/- -/- A/A -/- -/- -/- A/A -/- -/- -/-
    -/- -/- -/-

    SQL> SELECT * FROM DBA_AUDIT_TRAIL;

    no rows selected

    SQL>
  • 11. Re: SYS.AUD$ empty on table auditing
    user15801 Newbie
    Currently Being Moderated
    Even after setting the resource_limit=true, the result is the same:

    SQL> alter system set resource_limit=true;

    System altered.

    SQL> update ap.ap_supplier_sites_all
    set city = 'FREMONT'
    where vendor_id = 1010; 2 3

    1 row updated.

    SQL> commit;

    Commit complete.

    SQL> SELECT * FROM DBA_AUDIT_TRAIL;

    no rows selected

    SQL> select * from SYS.AUD$;


    Thanks and cheers.
  • 12. Re: SYS.AUD$ empty on table auditing
    TSharma-Oracle Guru
    Currently Being Moderated
    you weren't doing this "as sysdba" were you? you know "as sysdba", if audited is audited to the OS audit trail. Please confirm.
    If yes, do it from any user other than sys.
  • 13. Re: SYS.AUD$ empty on table auditing
    user15801 Newbie
    Currently Being Moderated
    hahahaha, yes!!!!! That was it, :-). I missed that.

    One last question, how can I retrieve the before and after values, who made the change and at what time?

    Thanks and cheers.
  • 14. Re: SYS.AUD$ empty on table auditing
    TSharma-Oracle Guru
    Currently Being Moderated
    I am glad you got the solution. This was a tricky one. :) For other details you want,you can go with Fine grained auditing OR you can write before update or after update trigger to catch those values.
    http://www.oracle.com/technetwork/database/security/index-083815.html

    Please mark this Post answered if you find your solution.
1 2 Previous Next

Legend

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