The objective of this post (The Matrix Quote in the title not withstanding) is to help the Database and the Security Team to enable log collection, processing and monitoring of Security Events of Interest with regards to a Business Critical Application Database. I have posted some articles on Overall Log Collection/Log Management here and here.
The key thing to note about DB Log Management is that the log is Non-Syslog Standard and has to have a DB connector/collector/agent/parser to collect, format and process the data in the DB for Auditing and Security Correlation purposes. Several SIEM solutions have the ability to pull audit data collected by Oracle DB regarding “Exactly at What Time, What User was responsible for executing What commands on the database”.
There are many different Oracle Audit facilities available for configuration:
- Oracle audit
- System triggers
- Update, delete, and insert triggers
- Fine-grained audit
- System logs
The best method to use is the Oracle Audit Facility with the “db_extended” option to capture Command History.
This will help in tracking the exact commands executed by the attacker. This will help in the Forensic Investigation of the attacks. Events like, privilege misuse, elevation of privilege attacks, SQL Injection, Brute Force etc can be prevented with proactive monitoring, alerting and response capabilities.
- Create a Unique Tablespace for the Audit Table: The first process is to create a separate tablespace just for auditing. The Oracle audit table is stored in “sys” table space. Because Oracle generates a lot of audit messages, this fills up the audit table, which can cause the database to crash with over flow. To avoid this problem, we must move the Oracle audit table into its own table space with its own data files separate from the core Oracle tables.
- Enable Auditing on the Oracle Database The auditing can be enabled either for the entire DB or for specific Tables. Please refer to the Diagram below showing the Typical Auditing Options recommended. For business critical applications, we recommend auditing SELECTS, UPDATES, and INSERTS to critical tables, such as salary info, credit card info, patient info, financial data, intellectual property, and so on.
For High Performance Databases where auditing cannot be enabled on all the tables, we can configure the “user.table_name” with the name of the table for which we would want to enable auditing for that action (as shown highlighted in yellow in the figure). We can also configure “user_name” with the names of users whose specific actions we want to audit (as shown highlighted in yellow in the figure).
Below is a table showing the some sample requirements for Database Auditing. This is just indicative and may vary from environment to environment depending on the Business Needs of your organization.
|GRANT PROCEDURE||BY ACCESS||BY ACCESS|
|SYSTEM GRANT||BY ACCESS||BY ACCESS|
|ALTER SEQUENCE||BY ACCESS||BY ACCESS|
|GRANT TABLE||BY ACCESS||BY ACCESS|
|ALTER TABLE||BY ACCESS||BY ACCESS|
|TRIGGER||BY ACCESS||BY ACCESS|
|PROCEDURE||BY ACCESS||BY ACCESS|
|ROLE||BY ACCESS||BY ACCESS|
|PUBLIC DATABASE LINK||BY ACCESS||BY ACCESS|
|DATABASE LINK||BY ACCESS||BY ACCESS|
|SEQUENCE||BY ACCESS||BY ACCESS|
|VIEW||BY ACCESS||BY ACCESS|
|PUBLIC SYNONYM||BY ACCESS||BY ACCESS|
|SYNONYM||BY ACCESS||BY ACCESS|
|ALTER USER||BY ACCESS||BY ACCESS|
|TYPE||BY ACCESS||BY ACCESS|
|USER||BY ACCESS||BY ACCESS|
|TABLE||BY ACCESS||BY ACCESS|
|CREATE SESSION||BY ACCESS||BY ACCESS|
Audit Logging in the Oracle Database will cost Disk/Database space. This data can be purged over a regular schedule to keep the audit table clutter free and performing faster. Since the Audit data is being collected into a SIEM solution, retention should not be done at DB and instead at the SIEM.
In order to perform the housekeeping, the recommendation is as follows:
1. Create a Truncate Procedure
Create or replace procedure clean_audit is
— select sysdate from dual;
EXECUTE IMMEDIATE ‘truncate table aud$’;
2. Schedule the Truncate Procedure
delete from dba_jobs where substr(what,1,6)=’CLEAN_’;
— it only need to run one time.
BEGIN DBMS_JOB.SUBMIT (
job => jobno,
what => ‘CLEAN_AUDIT;’,
next_date => trunc(SYSDATE+1)+2/24,
interval => ‘/*2:00AM*/ trunc(SYSDATE+1)+2/24’
DBMS_OUTPUT.PUT_LINE( ‘Job number ‘||jobno||’ setup.’ );