How to check database activity without performance and scalability issues?

Question

I need to audit all database activity, regardless of whether it came from an application or someone publishes some sql using other means. Therefore, auditing should be done at the database level. This database is Oracle. I looked at this through Triggers, as well as through what is called Fine Grained Auditing, which Oracle provides. In both cases, we included audit on specific tables and specific columns. However, we found that performance really sucks when we use any of these methods.

Since auditing is an absolute must because of the rules regarding data privacy, I wonder what is the best way to do this without significant performance degradation. If someone has experience with Oracle, it will be useful, but if not just the general practices associated with checking database activity will also be in order.

Answer 1

I am not sure if this is a mature enough approach for the production of the system, but I have had quite a bit of success with a traffic monitoring database using a network traffic analyzer.

Send the raw data between the application and the database to another machine and decode and analyze it there.

I used PostgreSQL and decrypted the traffic and turned it into a database workflow that could be logged in, was relatively simple. I assume that it will work in any database where the package format is documented.

The highlight was that he did not add extra load to the database.

In addition, it was passive monitoring, it recorded all the activity, but cannot block any operations, so it may not be exactly what you are looking for.

Answer 2

There is no need to "collapse your own." Just enable auditing:

• Set the database parameter AUDIT_TRAIL = DB.
• Run the instance.
• Log in using SQLPlus.
• Enter operator

    audit all; 

  This includes auditing for many critical DDL operations, but DML and some other DDL statements are still not validated.
• To enable auditing in these other actions, try these statements:

    audit alter table;  - DDL audit
   audit select table, update table, insert table, delete table;  - DML audit 

Note. All actions "like sysdba" are ALWAYS checked for O / S. On Windows, this means the Windows event log. On UNIX, this is usually $ ORACLE_HOME / rdbms / audit.

Check out the Oracle 10g R2 Audit chapter of the SQL Database Reference.

The database audit trail can be viewed in the SYS.DBA_AUDIT_TRAIL view.

It should be noted that Oracle's internal audit will be high-performance by definition. It is designed specifically for this, and it is very difficult to imagine anything else competing with it for performance. In addition, there is a high degree of “small-scale” Oracle audit control. You can get it as exactly as you want it. Finally, the SYS.AUD $ table, along with its indexes, can be moved to a separate tablespace to prevent the SYSTEM tablespace from populating.

Regards, Opus

Answer 3

If you want to record copies of the modified records in the target system, you can do this using the Golden Gate Software and not have to do much to deplete the source resources. Also, you do not need to make any changes to the source database to implement this solution.

Golden Gate resets retry logs for transactions, referring to a list of tables of interest to you. These changes are recorded in the "Trail File" and can be applied to another scheme in the same database or sent to the target and applied there (ideal for reducing the load on the source system).

As soon as you receive the trail file in the target system, there are some settings, you can set the option for auditing, and if necessary, you can call two Golden Gate functions to get information about the transaction:

1) Set the replication parameter INSERTALLRECORDS to insert a new record into the target table for each change operation made to the original table. Remember that this can eat a lot of space, but if you need a comprehensive audit, this is probably expected.

2) If you do not already have CHANGED_BY_USERID and CHANGED_DATE attached to your records, you can use the Golden Gate functions on the target side to get this information for the current transaction. Check out the following features of the GG Reference Guide: GGHEADER ("USERID") GGHEADER ("TESHETAMR")

Thus, it is not free (requires licensing through Oracle), and it will take some effort to roll out, but probably a lot less effort / expense than implementing and maintaining your own solution that reduces your own, and you have the added advantage of sending data to a remote system so you can guarantee minimal impact on the source database.

Answer 4

if you use oracle, then there is a function called CDC (Capture data change), which is a more efficient audit solution.