Log Management, Security Learning

Oracle: You have the sight now Neo!!!

Gallery 7 Comments

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”.

ORACLE AUDITING:
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.

REQUIREMENTS:

  1. 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.
  2. 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).

AUDIT REQUIREMENTS:
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.

AUDIT_OPTION                      SUCCESS    FAILURE
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

HOUSEKEEPING:

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
Begin
— select sysdate from dual;
EXECUTE IMMEDIATE ‘truncate table aud$’;
commit;
End;

2. Schedule the Truncate Procedure

delete from dba_jobs where substr(what,1,6)=’CLEAN_’;
commit;
— it only need to run one time.
DECLARE
jobno number;
BEGIN    DBMS_JOB.SUBMIT (
job => jobno,
what => ‘CLEAN_AUDIT;’,
next_date => trunc(SYSDATE+1)+2/24,
interval => ‘/*2:00AM*/ trunc(SYSDATE+1)+2/24’
);
COMMIT;
DBMS_OUTPUT.PUT_LINE( ‘Job number ‘||jobno||’ setup.’ );
END;

Now that you have covered Auditing for all User Connections to the Database, you would think that you have all your bases covered. But Oracle is always coming up with surprises you see. The surprise is that you are still vulnerable to Insider Attacks because SYS, SYSDBA activities are not tracked in the Database Auditing covered above.
Gotcha!!!
—
I know, I know. No system is perfect and every system has a GAP that can be exploited. Monitoring SYS and SYSDBA is very important because, a disgruntled user can go ahead and tamper your DB and even though you are collecting DB Audit logs, you would never come to know of it. So what do we do now?
Auditing SYS, SYSDBA Activity is not straightforward as User Activity Auditing. This is because SYS and SYSDBA events are logged only at the OS level.— This is where the problems increase manifold. Let us look at it in detail –
Problem 1: The Files are stored in Oracle Installation directory thereby easily accessible and are also from a permission wise owned under Oracle Install User Group so it can be easily tampered with by any user in the Oracle Install group.
Problem 2: We will have to monitor the Client machine also to track who is the User who logged into the OS in order to login as SYSDBA and SYS.
Problem 3: If you have disabled direct client login as SYS and SYSDBA, the only way would be to login to the DB machine itself and then login as SYS and SYSDBA. In this case, you will have to track the Machine login as well as DB login.
So, unless and until you address the problems identified above, DB Audit Tracking for Log Investigation and Incident Detection will not be complete. Once again, go through the articles on Overall Log Collection/Log Management here and here to get an idea of how you can solve the problems.
Oracle: You have the sight now Neo!!!

 [pdf]Save as PDF[/pdf]

7 thoughts on “Oracle: You have the sight now Neo!!!”

  2. Hi, I have recently found your . In my org. we are going to deploy RSA enVision soon. The scope covers Network, Systems (Win,UX), Apps (Java), DB (oracle) etc.

    For Windows systems, we use MS Exch (CAS/HT, MBX), AD (MP), FileServer. However I am not sure what level of logs or Auditing should I collect from these systems. Appreciate if you or any one can help me understanding the log levels to be collected from these systems.

    Regards
    Adnan

    Reply

    1. For Networks – Use Log Level 5
      For DB, the post you have seen should answer that question for you
      For Windows – Only Security Logs are more than enough. If you need File Attribute level logging, you may want to use additional configuration on RSA to parse those logs.
      For Unix – Configure the Syslog to log Authd logs, Cron logs, Process logs and other application logs like Apache, DB etc. Here also, if you are logging the file attributes, you may want to use additional config on RSA
      AD Logs – Security Logs only. However, make sure you filter them based on your needs – User Creation/Removal, Group Creation/Addition/Removal etc so that useful alerts can be generated.
      MS Exch – Message Tracking is useful however it is to noisy, I would recommend just take Top Value Stats for these systems Like Top Sender, Top Subject, Top External Mails etc
      DNS/DHCP logs are also necessary but DNS is notorious for huge volume logs, so you may want to just look for Blacklist Domain Queries to start with and then slowly build Intelligence on it.

      Reply

      1. Hi,

        Sorry for my late response and thank you so much for your detailed information.

        I have some more queries regarding Windows systems.

        1) MS Exch: We are using 2010 in our environment. So far we have decided to take the OS logs (Event, Systems, Security). Also we are taking the audit logs. However I want to know,

        a. Our Exch system admin alredy enabled audit in their systems. We are now working with RSA to send those logs to enVision. I need to know whether we should look for any specific level of Audit logs from Exch.

        b. What logs should I collect to get Top Sender, Top Subject, Top External mails etc.

        2) DNS: Yah it is true that DNS queries are really notorious. I have also got huge logs of DNS queries in my firewall. Hence like to omit those logs. Here,

        a. How can I omit those logs?

        b. How or which logs should I collect to get the black-listed domain queries.

        Appreciate if you can share me your expert opinion. Thanks again for your information.

        Regards,
        Adnan

        Reply

        1. 1. MS Exchange – The important things to look for are Admin Changes (Creating/Deleting/Modifying Policies, Filters, Mailbox Permissions etc) and All Mail “Traffic” Logs (RECEIVE/DELIVER events). The first one will help u build privilege misuse Use Cases and the next one will help you build SPAM use Cases.
          2. Filtering is the best way to omit logs not required. I am not sure how your log management system is setup, so I am not in a position to exactly tell you how and where to filter. But general rule of the thumb is to filter as close as possible to the source.
          3. For Black-Listed Domains, you need to use a custom script of integration with Abuse.ch and other BL domains to get data. With this data, you can look for traffic in an out to the domain – either using Firewall Logs or using DNS Logs or CLient Firewall Logs, IDS/IPS Logs etc or combination of each other.

          Reply

  3. Thanks a lot for the information. That’s really helpful. We have now planning with our Windows team to work on it. Thanks.

    However we are facing some issue with HP Unix integration. RSA prerequisite is to have Trusted mode and Enable Audit. Almost all cases, Trusted mode is not here (due to Oracle db complexity though from security aspects I believe this is important) and also Auditing is not enabled as this is a resource greedy process. All the systems are in production and hence Unix guys are a little confuse to test it. Appreciate it if you can give me some idea on where to start from.

    Thanks again for your help.

    Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.