Tag Archives: Log Management

Oracle: You have the sight now Neo!!!

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.


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

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


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.
jobno number;
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.’ );

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

 Save as PDF

High Log Volume – What to Filter and What to Keep?

What and How much to Collect concentrated on giving a starting point for log collection and usage. Here, I am going to talk about Log Filtering for Security Investigations and Analysis purpose. If you are collecting logs from a troubleshooting perspective, you can collect without filtering, but for Security analysis, we would need less of haystack so that we can have a better chance of finding the needle.
In organizations, where several thousands of devices are sending Syslog, managing them is really a nightmare. In such scenarios, more focus is required on Log filtering. Log Filtering for Security Infrastructure use is one of the most ignored aspects of Log Management but it is the most important aspect for a cleaner and efficient log management and Security Event Analysis.

Log Filtering can be done at the Source or at the Syslog Server Location.

  1. Filtering at Source is the best approach when it comes to Log Filtering. This provides for better control of your infrastructure as you know what is being logged and what is not. Indirectly, it also aids in less utilization of System Resources. But the downside is, this is the most difficult to implement. Network devices have very little capability of Source filtering. Firewalls have a lot of options when it comes to Logging, but Routers and Switches offer very less. UNIX offers basic filtering natively and with third party tools like rsyslog, syslog-ng offer granular filtering capabilities. As all would know, there is no native Syslog capability in Windows and it would typically need a third party client to fill in for this lack of capability. This works in favor of filtering because; generally Third Party tools give filtering options.
  2. Filtering at Destination is the most practical and easiest to implement when it comes to Log Filtering. Here, Log Management Solutions or SIEM Tools take care of filtering what is needed and what is not. However, your Source Devices will be generating tons of logs and will be using System resources as well as Bandwidth.

Based on your organizations Architecture, it will help to decide on what is easier to implement and manage. Both have their pros and cons and its up to the Security Teams to decide. Once the filtering approach is decided, it is time to move on to the “Filtering” itself. This is the hard part. Before you start filtering, start understanding the device families present in the Environment. Every Vendor logs events differently and every event log means differently as well. So, care should be taken that we don’t end up filtering logs for one version and in reality we have another.

Example Log Filtering for Cisco ASA:
Let’s take an example of a Cisco ASA Firewall. The Cisco Adaptive Security Appliance (CISCO ASA in short) Operating System generates several logs. Out of the several thousands of messages, the most important events from a Security perspective are the following events given in the table. These events are built in protection defaults for Cisco ASA appliances. Apart from the following events, we can pick and choose Traffic Based events for analysis purpose. If interested to receive the full list, please comment below and I will put up that list too.

%PIX|ASA-2-106016 Deny IP spoof from (IP_address) to IP_address on interface interface_name. – Spoofing Detection
%PIX|ASA-2-106017 Deny IP due to Land Attack from IP_address to IP_address – Spoofing Detection
%PIX|ASA-2-106020 Deny IP teardrop fragment (size = number, offset = number) from IP_address to IP_address – Teardrop Attack
%PIX|ASA-1-106021 Deny protocol reverse path check from source_address to dest_address on interface interface_name – Protocol Reverse Attack
%PIX|ASA-1-106022 Deny protocol connection spoof from source_address to dest_address on interface interface_name – Spoofing Detection
%PIX|ASA-3-109010 Auth from inside_address/inside_port to outside_address/outside_port failed (too many pending auths) on interface interface_name. (floodguard enable)
%PIX|ASA-4-109017 User at IP_address exceeded auth proxy connection limit (max) – DOS Attack Possible
%PIX|ASA-4-109022 exceeded HTTPS proxy process limit  – DOS Attack Possible
%PIX|ASA-5-111001 Begin configuration: IP_address writing to device – Privilege Use
%PIX|ASA-5-111003 IP_address Erase configuration – Privilege Use
%PIX|ASA-6-113006 User user locked out on exceeding number successive failed authentication attempts – Brute Force
%PIX|ASA-3-201002 Too many TCP connections on {static|xlate} global_address! econns nconns – DOS Attack Possible Behavior
%PIX|ASA-3-201004 Too many UDP connections on {static|xlate} global_address! udp connections limit – DOS Attack Possible Behavior
%PIX|ASA-4-209003 Fragment database limit of number exceeded: src = source_address, dest = dest_address, proto = protocol, id = number  – DOS Attack Possible Behavior
%PIX|ASA-4-209004 Invalid IP fragment, size = bytes exceeds maximum size = bytes: src = source_address, dest = dest_address, proto = protocol, id = number – Possible Intrusion Event
%PIX|ASA-4-209005 Discard IP fragment set with more than number elements: src = Too many elements are in a fragment set  – Possible Intrusion Event
%PIX|ASA-3-210011 Connection limit exceeded cnt/limit for dir packet from sip/sport to dip/dport on interface if_name  – DOS Attack Possible Behavior
%PIX|ASA-4-405001 Received ARP {request | response} collision from IP_address/MAC_address on interface interface_name to IP_address/MAC_address on interface interface_name – ARP Poisoning
%PIX|ASA-4-405002 Received mac mismatch collision from IP_address/MAC_address for authenticated host – ARP Spoofing
%PIX|ASA-2-410002 Dropped num DNS responses with mis-matched id in the past sec second(s): from src_ifc:sip/sport to dest_ifc:dip/dport – Possible DNS Attack Detected
%PIX|ASA-4-412002 Detected bridge table full while inserting MAC MAC_address on interface interface. Number of entries = num – Possible L2 Attack Detected
%ASA-4-424001 Packet denied protocol_string intf_in:src_ip/src_port intf_out:dst_ip/dst_port. [Ingress|Egress] interface is in a backup state – Possible Intrusion
%ASA-4-424002 Connection to the backup interface is denied: protocol_string intf:src_ip/src_port intf:dst_ip/dst_port – Possible Intrusion
%PIX|ASA-6-605004 Login denied from source-address/source-port to interface:destination/service for user “username” – Possible Intrusion
%PIX|ASA-5-304001 [email protected]_address Accessed {JAVA URL|URL} dest_address: url

If you carefully note the structure of the Event message itself you will notice that almost all of the Syslog Messages I have chosen have facilities that range from 1 to 5. These Event IDs can be used for analysis of different scenarios. Here, I have listed down some pointers on how to select these Events of Interest. Some approaches that we can use are:

  1. Focus on the Utility of an Event rather than the data it generates. The description of an Event ID will be misleading many a times, and hence looking at the Generated Data from the Event is important.
  2. Focus on what events will help you track back the actual Attacker/User/Service responsible for triggering the Event in the first place. This event independently or in tandem with another should be able to help you
  3. Look for what Event Collection is more suited to your environment in terms of Security Monitoring. For Example, if you have an Internet Firewall, Spoofing events don’t make sense to collect, whereas internal firewalls would need spoofing events.
  4. Look for Optimal Event ID Selection for auditing so that you get the right amount of data with One Event ID rather than many. This may be difficult in some cases with some devices, but still where ever possible this should be employed.
  5. Finally, see if your SIEM tools can parse these event properly for you to analyze

Now let us look at Juniper NetScreen Firewall Events using the same approach as we used for Cisco ASA.

Critical (00031) Message 〈string〉 detected an IP conflict (IP 〈IP address〉, MAC %m) on interface 〈string〉
Notification (00031)Message ARP detected IP conflict: IP address 〈ip〉 changed from interface 〈if_old〉 to interface 〈if_new〉
Notification (00051) Message Static ARP entry added to interface 〈string〉 with IP 〈IP address〉 and MAC %m
Notification (00052)Message Static ARP entry deleted from interface 〈string〉 with IP address 〈IPaddress〉 and MAC address %m
Emergency Message Ping of Death! From 〈src_ip〉 to 〈dst_ip〉, proto 1 (zone 〈zone_name〉, int 〈interface_name〉). Occurred 〈number〉 times.
Emergency Message SYN flood! From 〈src_ip〉:〈src_port〉 to 〈dst_ip〉:〈dst_port〉, proto TCP (zone 〈zone_name〉, int 〈interface_name〉). Occurred 〈number〉 times.
Emergency Message Teardrop attack! From 〈src_ip〉:〈src_port〉 to 〈dst_ip〉:〈dst_port〉, proto { TCP | UDP | 〈number1〉 } (zone 〈zone_name〉, int 〈interface_name〉). Occurred 〈number2〉 times.
Alert Message Address sweep! From 〈src_ip〉 to 〈dst_ip〉, proto 1 (zone 〈zone_name〉, int 〈interface_name〉). Occurred 〈number〉 times.
Alert Message ICMP flood! From 〈src_ip〉 to 〈dst_ip〉, proto 1 (zone 〈zone_name〉, int 〈interface_name〉). Occurred 〈number〉 times.
Alert Message IP spoofing! From 〈src_ip〉:〈src_port〉 to 〈dst_ip〉:〈dst_port〉, proto {TCP | UDP | 〈number1〉 } (zone 〈zone_name〉, int 〈interface_name〉). Occurred 〈number〉 times.
Alert Message Land attack! From 〈src_ip〉:〈src_port〉 to 〈dst_ip〉:〈dst_port〉, proto TCP (zone 〈zone_name〉, int 〈interface_name〉). Occurred 〈number〉 times.
Alert Message Port scan! From 〈src_ip〉:〈src_port〉 to 〈dst_ip〉:〈dst_port〉, proto { TCP| UDP | 〈number1〉 } (zone 〈zone_name〉, int 〈interface_name〉). Occurred 〈number2〉 times.
Alert Message Source Route IP option! From 〈src_ip〉:〈src_port〉 to 〈dst_ip〉:〈dst_port〉, proto { TCP | UDP | 〈number1〉 } (zone 〈zone_name〉, int 〈interface_name〉). Occurred 〈number2〉 times.
Alert Message UDP flood! From 〈src_ip〉:〈src_port〉 to 〈dst_ip〉:〈dst_port〉, proto UDP (zone 〈zone_name〉, int 〈interface_name〉). Occurred 〈number〉 times.
Alert Message WinNuke attack! From 〈src_ip〉:〈src_port〉 to 〈dst_ip〉:139, proto TCP (zone 〈zone_name〉, int 〈interface_name〉). Occurred 〈number〉 times.
Critical Message – Bad IP option! From 〈src_ip〉:〈src_port〉 to 〈dst_ip〉:〈dst_port〉, proto{ TCP | UDP | 〈number1〉 } (zone 〈zone_name〉, int 〈interface_name〉).Occurred 〈number2〉 times.
Critical Message EXE file blocked! From 〈src_ip〉:〈src_port〉 to 〈dst_ip〉:〈dst_port〉, proto { TCP | UDP | 〈number1〉 } (zone 〈zone_name〉, int 〈interface_name〉). Occurred 〈number2〉 times.
Critical Message FIN but no ACK bit! From 〈src_ip〉:〈src_port〉 to 〈dst_ip〉:〈dst_port〉, proto TCP (zone 〈zone_name〉, int 〈interface_name〉). Occurred 〈number〉 times.
Critical Message SYN and FIN bits! From 〈src_ip〉:〈src_port〉 to 〈dst_ip〉:〈dst_port〉,proto TCP (zone 〈zone_name〉, int 〈interface_name〉). Occurred 〈number〉 times.

If you see in the case of Juniper, Alert and Critical messages are sent to Syslog and these events are not always identifiable using Event IDs. From a Syslog facility all these would be still at Level 5. These events are also default Security Messages generated from Juniper. In addition to these messages, we would be collecting the Traffic Logs (Event ID 000257) from Juniper that helps in Security analysis for correlation.

This same approach can be used for Windows, Unix, Security Application Devices etc. For  more details of specific Device type filtering, please request in the comment section and I will post the filtering for the same.

If you effectively identify the Events of Interest for various devices and filter out the chaff, you would be able to harness the power of Syslog for your Security Investigation purposes. SIEM is getting bigger by the day in terms of data usage. The world is moving towards big data collection. But the key thing to note is, in spite of data growth, the quality of SIEM tools in log analysis has not been going North. Quality does not essentially come with Quantity. Filtering will always be needed no matter how much logs we collect.

Remember “Logs don’t lie”

What and How much to Collect – Enterprise Security Logging

One of the major challenges in Enterprise Log Management is “What to Collect and How much to Collect?”. The How to collect is the easy part with several Enterprise as well as open source tools available in the market that help with this. Source Device themselves have several log forwarding mechanisms and features which we can leverage to collect logs.
Most of the times, Log Collection and Management is driven by Standards, Compliance Requirements and other external drivers. Good Security Practice calls for Log Collection and Management to be driven by Enterprise Policy. So in order to implement a Log Management Program, the need to have a strong Enterprise Policy is paramount. This policy once defined should be implemented thoroughly right from the OS Build, Application Certification, IT Infrastructure Design to Solution Approval, Change Approval, and other Operations Process.

What to Collect:

“Never start Log Management Program without knowing your IT Infrastructure”. This is what I would suggest to anyone who wants to start Log Management Program. Knowledge of the network is important to decide on Log Collection Scope and Volume. Generally, we collect logs for several reasons ranging from Troubleshooting IT Issues to Forensic Investigation. My focus in this blog post is of course on Security Specific Log Collection. I would like to mention here that there is no right or wrong way in approaching Security Log Collection and hence what I am trying to catalogue here are my experiences of what has been useful in Security Investigations in my career.

  • Core Router– Though Router Logs are chatty, collection of Core Router Logs are important because, if your enterprise Core Router is over-run/compromised then your enterprise is wide open. These router logs can be heavily filtered to identify only DoS/DDoS attack scenarios as well as Exploit Scenarios (By exploiting a vulnerability and owning the router).
  • Network Firewall– Firewall Logs are the most helpful in identifying Security Incidents involving Malware, Compromised Hosts, Spam Relays and several other Network based Attacks and Compromises. Keep in mind that the logs don’t directly tell you that a Malware is found in the network, but it gives a great corroborating evidence (In terms of when and what connection was made) to any of your Security System Alerts.
  • Network IDS/IPS– Intrusion Detection Systems are a crucial part in the Security Incident Detection and Investigation process. The alerts received from the IDS/IPS gives a lot of information. This when combined with Firewall Logs help to identify the entire Attack Scenario.
  • Authentication Servers– In a Network there are several Authentication mechanisms for users to access IT Infrastructure. Most of them go off a Directory service or Database service. Most famous of the these Authentication servers are Active Directory/LDAP, RADIUS, TACACS, Internal User Database etc. From a Log collection perspective, in order to identify the User, his machine and the Authentication time stamp, we would need logs from Authentication Servers.
  • Endpoint Security Solutions– The last line of defense in any organization is the Security Systems put on the Servers or the End User machines. These tools can be of great help in a Security Investigation by providing information on whether the Host Defense system detected an intrusion or not.
  • Application Servers– Application servers are the main reason one has to protect the network. The application servers be it Web or Database are critical to an Enterprise. We would need to monitor these systems closely for any kind of breach of compromise by collecting logs from Operating System Level as well as Application Level
  • Misc Logs – In addition to the above critical log Sources, Enterprises may choose to collect Logs from other supporting IT Infrastructure as well. This however is an all-inclusive list of devices like Proxies, DHCP, DNS, Other Security Tools in the environment, Switches, WAN Devices etc

How much to Collect:

One of the oft ignored aspects of Log Management is Standardization of the Logging Infrastructure. This means that the format should be standardized so that all the Logs can be collected in a Centralized fashion and used for normalization, integration with SIEM Solutions, etc. Almost all the solutions support Syslog as a Logging Format. Network Devices and Unix Devices have Syslog daemons running natively and can be used to send logs to a Central Logging Infrastructure. As far as I know, most of the Security Appliances/Servers also have Syslog capability. Microsoft Windows servers don’t have the capability to send Event Logs through Syslog and hence we would need to use Third-Party tools like Snare, Syslog-NG, WinLogD, Kiwi etc.
Syslog has several levels as highlighted below in the diagram and one should carefully chose the level appropriate to the Organization.

From a Security Perspective, recommendation would be to choose either Level 5 or Level 6. Level 5 mostly covers all Security related incidents logging. Level 6 gives detailed incident logging but will be very voluminous. Level 6 enabled Syslog setting would need extensive filtering to ensure the Syslog Infrastructure is not overwhelmed in volume and subsequently Storage space.

To summarize, More and More information collection through Logs would be invaluable in Security Investigation. Many would say that Log Collection is a “Reactive Approach” to Security Management but remember “Logs Don’t Lie”
I would be writing a detailed Blog post very soon to give details on What Logs to keep and What Logs to Filter. I will try to do it by device type so that it will be easy to read.

Please visit the follow-up post – What Logs to Keep and What to Filter