Script to Monitor and Report Audit Trail Records in sys.aud$

 

Script Description:

This script monitors the major audit trail records and failed login attempts, it can be easily deployed and customized. So far, it has been tested on Linux Environment.

Script Category: Auditing & Security

How it works:

You have to schedule this script to run in the OS scheduler "crontab", you can decide how fast you should receive a notification whenever a new "major" audit event get created. Then set the same schedule interval in minutes inside the script to determine the window of time which the script will fetch the data from sys.aud$.

e.g. If you schedule the script to run every 30 minutes in the crontab, you should set the Threshold "MINUTES" inside the scripts to 30.

The script uses sendmail as the only notification method, so you have to set the following parameter replacing the pattern youremail@yourcompany.com to your actual Email:

EMAIL="youremail@yourcompany.com"

In addition, the script has many parameters to help you narrow down the scope of audit actions monitoring, you can change the values in green color:

 HTMLENABLE=Y      # Enable HTML Email Format [Default Enabled].
 RECORDSNUM=1     # Send an Email if the sum of audit records >= the threshold [Default 1 record].
 REPORT_FAILED_LOGINS=Y    # Enable the reporting of failed login attempts. [Default Enabled].
 REPORT_AUDIT_RECORDS=Y  # Enable the reporting of audit records [Default Enabled].
 EXCLUDE_DBUSERS="'dba_bundleexp7'"  # Exclude DB user from reporting their activities [In lowercase]. e.g. EXCLUDE_DBUSERS="'sys','scott'"
 EXCLUDE_OSUSERS="'user1'"    # Exclude OS user from reporting their activities [In lowercase]. e.g. EXCLUDE_OSUSERS="'oracle','grid'"
 EXCLUDE_ACTIONS="'SELECT','SET ROLE','LOGON','LOGOFF','LOGOFF BY CLEANUP','EXPLAIN','PL/SQL EXECUTE','SYSTEM AUDIT'" # Exclude specific AUDIT EVENTS from reporting.

EXCLUDE_ACTIONS is an important parameter to help you exclude specific audit actions from getting reported. This reporting mechanism should cover only crucial activities that need immediate attention like DROP, TRUNCATE, ALTER, ... etc for example; minor activities like Log In, Log Off and SELECT shouldn't be in the scope of such monitoring mechanism; otherwise you will get your mailbox filled unnecessarily.

I've already excluded common audit actions from the scope of getting reported like:

SELECT, SET ROLE, LOGON, LOGOFF, LOGOFF BY CLEANUP, EXPLAIN, PL/SQL EXECUTE, SYSTEM AUDIT

Let's suppose you want to exclude DML's from getting reported; modify the parameter as follows:

EXCLUDE_ACTIONS="'SELECT','SET ROLE','LOGON','LOGOFF','LOGOFF BY CLEANUP','EXPLAIN','PL/SQL EXECUTE','SYSTEM AUDIT','INSERT','UPDATE','DELETE','SESSION REC'"

Ensure that you enclose each audit action between "single quotation" '' and separating between them using "comma" ,

To display all the available actions names that you can use for that parameter, run this statement:

SQL> select distinct action_name from dba_audit_trail order by 1;  

Feel free to add more actions to be excluded to EXCLUDE_ACTIONS parameter, and I encourage you to do so.

Precautions:

The last and the most important remaining point here is indexing sys.aud$ table:

Your audit trail table sys.aud$ may have millions/billions of rows, querying this table frequently can degrade your database performance and most probably it can bring the server on its knees; especially if it has humble resources!

If reporting audit records is important for you, then you must create the following index before start using this script and to avoid any performance degradation it can cause by this script: (Create it with online option to avoid getting the DB hung)

SQL> CREATE INDEX sys.idx_ntimestamp# ON sys.aud$(ntimestamp#) ONLINE;
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname => 'SYS', tabname => 'AUD$', cascade => TRUE, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

When the script run, it will check if sys.aud$ (ntimestamp#) is indexed, if it's not; it will display a warning message along with the recommended index creation statement to execute.

It is worth to mention that Oracle doesn't recommend creating any indexes on sys.aud$ table as it may degrade the performance of record insertions to sys.aud$, in addition to other minor reasons mentioned in this Note: The Effect Of Creating Index On Table Sys.Aud$ (Doc ID 1329731.1)

From my experience; I use this script along with the creation of the above index on very busy OLTP databases without noticing any performance degradation. Creating an index on sys.aud$ table is like creating an index on any other "busy table"; Yes it will add an extra overhead (to maintain the index) when audit records are inserted, but still Oracle can handle this efficiently.

Indeed, you are the right one to decide whether adding an index to sys.aud$ table will degrade the performance or not; based on the daily volume of audit data that get inserted to sys.aud$, along with the hardware resources of your DB server.

One last thing, you may ask why I coded the script to select directly from sys.aud$ instead of using DBA_AUDIT_TRAIL; the answer is that I've noticed in many cases that selecting from DBA_AUDIT_TRAIL will force the optimizer to not use the above mentioned indexes leading to an expensive full table scans, this is why I avoided using it.

Please feel free to share your thoughts and suggestions.

To download the script:

https://www.dropbox.com/s/vja3hptpzy7a3in/monitor_audit_records.sh?dl=0

After you click on that link, click on the down arrow at the top-right side of the page:

GitHub Version: