1 2 Previous Next 19 Replies Latest reply: Jun 19, 2013 10:26 AM by EdStevens RSS

    Audit delete and update on all tables of database

    Ankit Ashok Aggarwal

      Was going through this and found that “AUDIT DELETE ANY
      TABLE BY <username> BY ACCESS” or “AUDIT UPDATE ANY TABLE
      <username> BY ACCESS” enable audit for delete and updates for  given username/schema.

       

       

      I want to enable auditing on delete and update on my entire
      database.

       

       

      Please suggest the best possible solution? Why? And have we
      tested it in our any of existing setup?

       

       

      I am thinking of “Trigger after delete” but again this logic
      gets struck at individual tables. It do not work simply once and all for
      complete database/all users/all schemas

        • 1. Re: Audit delete and update on all tables of database
          JustinCave

          What is the business problem that you are trying to solve?  If the data captured by the AUDIT command is sufficient for your requirements, use the AUDIT command.  If you need something more than basic auditing provides-- if, for example, you need to maintain a history of the changes to a row over time, you probably need something else.  Without knowing exactly what you are trying to accomplish, no one can know what options are available let alone what option would be "best".

           

          Justin

          • 2. Re: Audit delete and update on all tables of database
            Ankit Ashok Aggarwal

            Business Requirement is \

            "Need to audit all delete and updates on all tables of database"

             

            Please suggest the solution to this ?

            • 3. Re: Audit delete and update on all tables of database
              Mahir M. Quluzade

              Hi,

               

              You can use DBMS_FGA for auditing all  tables for all DML.

               

              Please check : http://docs.oracle.com/cd/E11882_01/appdev.112/e10577/d_fga.htm

              http://www.morganslibrary.org/reference/pkgs/dbms_fga.html by damorgan

               

               

               

              Regards

              Mahir M. Quluzade

              • 4. Re: Audit delete and update on all tables of database
                Dizwell

                Yeah, I think you'll find that no "audit delete on database;' command exists because no-one in their right might would ever set it.

                 

                First thing they teach you on any auditing course: define your scope.

                And by that they mean, make your scope small enough that you capture useful information, but don't make it so large that you capture huge amounts of pointless data, too. Auditing is about getting the signal-to-noise ratio right, not just capturing every smidgen of data on the off-chance it might be useful to someone, someday.

                 

                The idea of any delete, anywhere, being audit-worthy is not sensible, I would have said. And the fact that there's no audit delete no database command sort-of suggests that others would think that too.

                 

                If you are determined to do it, though, use SQL to write your SQL:

                 

                select 'audit delete on '||owner||'.'||table_name||';' from dba_tables where owner not in ('SYS','SYSTEM', etc);

                 

                ...that sort of thing, anyway.

                 

                If my boss asked me for this, though, I would first suggest he was slightly batty, and when he insisted, I'd point out that the redo logs capture all delete and update commands already. You just need log miner to expose them, should they ever be needed.

                • 5. Re: Audit delete and update on all tables of database
                  Ankit Ashok Aggarwal

                  Hi Mahir,

                   

                  DBMS_FGA is addressing schema wise and object wise auditing.

                   

                  same can be done using audit command.

                   

                  my requirement is to audit all tables of database in one go (database level wise). I do not want to use schema wise or objet wise apparoach

                   

                  Is there any way out ??

                  • 6. Re: Audit delete and update on all tables of database
                    Ankit Ashok Aggarwal

                    Will before delete and before update triggers will work in this regards ??

                    • 7. Re: Audit delete and update on all tables of database
                      Uwehesse-Oracle

                      You should at first evaluate whether it is not just sufficient to analyze Archived Logs respectively Online Logs that are being created anyways without additional overhead. Check out Logminer for that purpose - doesn't that already fulfill the business requirement?.

                       

                      Auditing will always impose a certain overhead that may be avoided in this case completely.

                       

                      Kind regards

                      Uwe Hesse

                      • 8. Re: Audit delete and update on all tables of database
                        EdStevens

                        AnkitAshokAggarwal wrote:

                         

                        Will before delete and before update triggers will work in this regards ??

                        Why do you seem fixated on triggers?

                        It has already been explained that you need to define the scope of the problem.  That will, by definition, require some two-way discussion with the person who wants the audit.  You need to be prepared to explain to them -- with actual examples -- the futility of auditing "everything".  Why do they want to audit every delete on table X.  Same for table Y.  Same for table Z.  Simply saying "we want to audit everything" is folly.  Once you have the true scope of the problem, you need to thoroughly understand what can be achieved with oracle's own audit functions.  Only when you have done the proper analysis of both the nature and scope of the business problem as well as the capability of oracle's audit features will you be in a position to decide you need to re-invent the wheel by writing a bunch of triggers.

                        • 9. Re: Audit delete and update on all tables of database
                          mtefft

                          By the way, 'AUDIT DELETE ANY TABLE' and 'AUDIT UPDATE ANY TABLE' will not do what you think it will do. These commands audit exercises of the DELETE ANY TABLE privilege and 'UPDATE ANY TABLE' privilege, such as a DBA would have. It does not audit statements that do not use (and do not need) that privilege to run.

                           

                          I wholeheartedly agree with EdStevens that this 'requirement' does not yet make sense.

                          • 10. Re: Audit delete and update on all tables of database
                            Mark D Powell

                            Ankit, yes the command you listed will result in auding all delete statements issued on the database; however, auditing is overhead and auditing every single delete is likely a lot of overhead.  In most environments the majority of tables do not really merit auditing so the cost of auditing everything verse auditing only those truely important tables needs to be considered and discussed with whomever came up with the requirement.  Depending on how long the change data needs to be retained and how many tables are truely important the use of a history table populated by table level DML triggers might be a solution.

                            - -

                            Be sure your system tablespace has adequate space allocated to be able to support the audit trail.  You may need some type of audit trail extraction (purge and archive) process.  This would likely need to be ready at the same time you turn mass DML auditing on.

                            - -

                            HTH -- Mark D Powell --

                            • 11. Re: Audit delete and update on all tables of database
                              EdStevens

                              MarkDPowell wrote:

                               

                              Ankit, yes the command you listed will result in auding all delete statements issued on the database; however, auditing is overhead and auditing every single delete is likely a lot of overhead.  In most environments the majority of tables do not really merit auditing so the cost of auditing everything verse auditing only those truely important tables needs to be considered and discussed with whomever came up with the requirement.  Depending on how long the change data needs to be retained and how many tables are truely important the use of a history table populated by table level DML triggers might be a solution.

                              - -

                              Be sure your system tablespace has adequate space allocated to be able to support the audit trail.  You may need some type of audit trail extraction (purge and archive) process.  This would likely need to be ready at the same time you turn mass DML auditing on.

                              - -

                              HTH -- Mark D Powell --

                              Mark,

                               

                              Shouldn't the audit table be moved out of the system tablespace in the first place?

                              • 12. Re: Audit delete and update on all tables of database
                                Mark D Powell

                                No, the audit table should not be moved out of the system tablespace.  While Oracle support has a note with instructions on how to move the aud$ table to a different tablespace Oracle also states that this is an 'unsupported' action in that it may not be compatiable with patching/upgrade activity.

                                - -

                                I would leave sys.aud$ where Oracle buids it and just use DML to move the data to an archive table or extract it out of the database into OS files where you can compress the files, free of any extra cost charges.

                                - -

                                IMHO -- Mark D Powell --

                                • 13. Re: Audit delete and update on all tables of database
                                  977635

                                  I totally agree.  I used dbms_audit_mgmt to move my audit stuff into a new tablespace I call SYSAUD.

                                  Here is a good paper on managing your audit data:

                                   

                                  http://www.oracle.com/technetwork/database/audit-vault/learnmore/twp-security-auditperformance-166655.pdf

                                   

                                  http://www.oracle-base.com/articles/11g/auditing-enhancements-11gr2.php

                                   

                                  From my experience with auditing, you can accumulate an inordinate amount of data that is useless unless you have the proper tools to mine the data.

                                  In our case, we were required by law to audit everything (except select statements) had a script to purge audit data that was older than 45 days, and the audit data still took 300G of space (keeping only 45 days).  We also had a database forensics team that would process the audit data daily with programs they wrote, along with using a special log mining software that would read archive logs and compare with audit logs.

                                  • 14. Re: Audit delete and update on all tables of database
                                    EdStevens

                                    MarkDPowell wrote:

                                     

                                    No, the audit table should not be moved out of the system tablespace.  While Oracle support has a note with instructions on how to move the aud$ table to a different tablespace Oracle also states that this is an 'unsupported' action in that it may not be compatiable with patching/upgrade activity.

                                    - -

                                    I would leave sys.aud$ where Oracle buids it and just use DML to move the data to an archive table or extract it out of the database into OS files where you can compress the files, free of any extra cost charges.

                                    - -

                                    IMHO -- Mark D Powell --

                                    Have you seen the dbms_audit_management package?

                                    http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_audit_mgmt.htm#BABJECFB

                                     

                                    And more specifically, the

                                    SET_AUDIT_TRAIL_LOCATION Procedure, see http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_audit_mgmt.htm#BABDAHBG

                                    1 2 Previous Next