1 2 Previous Next 15 Replies Latest reply: Feb 18, 2013 9:25 AM by user12003282 RSS

    SYS.AUD$ empty on table auditing

    user12003282
      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
          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
            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
              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
                user12003282
                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
                  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
                    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
                      user12003282
                      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
                        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
                          Set resource_limit=true at db end.

                          Its a dynamic parameter.
                          • 10. Re: SYS.AUD$ empty on table auditing
                            user12003282
                            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
                              user12003282
                              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
                                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
                                  user12003282
                                  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
                                    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