To continue with the INFOSEC posts I wanted to demonstrate how you can use Extrahop triggers to Monitor your Database connectivity and be able to tell when data is actually being stolen from your back end Databases. In many cases sensitive data (data that you will get sued, fined, embarrassed or fired in the event it is compromised) is located on Database servers. From an INFOSEC perspective, it is important that back end databases are only accessed by those systems that are supposed to. Let’s say we have a Web tiered application that connects to a CRM database that has all of my company’s leads. If I note that I see an IP Address that is on the users segment connecting to my back end database that is something I should be concerned about. Even if my organization has taken steps to layer then network so that only appropriate hosts can talk to one another, you still have the issue of someone potentially compromising one of the web servers then running queries and stealing data from the compromised web server. In order to prevent this I need two things, I need to know what type of SQL traffic is expected (it is actually rather rare to see “select *” in a well written application. Taking stock of the types of queries that are being run against your data and from whom/where is an important step to preventing data leakage due to SQL Injection or a trusted box getting compromised.
Outside of INFOSEC you also have the benefit of being able to see which queries are taking the longest time to run. If you have Splunk you can use some RegEx to actually parse out the performance by table (will show you a video) which could give you an indication that a table needs indexing. Using Triggers you can log and report on the following:
- Table Performance
- Processing time by Server
- Processing time by Client
- Total queries by Client
- Total queries by server
- Processing time by Query (which Queries take the longest time to complete)
Imagine doing an application upgrade or a schema update and being able to go to your stored procedures and see before and after performance without needing to run profiler. All of this data can be collected, parsed and reported on without a single agent being installed and without anyone touching an incumbent system.
The two triggers that you need for this are located in the Triggers section and they can be copied and pasted into your Extrahop Discovery Edition. Once you have loaded the triggers you can then see the SQL traffic traverse the span and using the Console trigger you can see the data in the Console.
Note the simple Query below:
General Punk busting:
In addition to being able to see the overall performance of each SQL Query you will be able to audit exactly what queries have been run against your critical databases and even critical tables. You see in the graphic below a user (myself) is attempting to select critical data from a fictional table called CreditCardData. Note the time I ran the query (the Splunk server is not synched up with my AD domain so I am off by a few seconds)
What I look for in the results:
The first thing I note is that we see the query for PII running and I see the IP Address. The important thing to ask yourself is, “Does that IP Address look right, is that my front-end E-Commerce server verifying payment information or is that some clown on the network. The next thing I ask myself is “does that query look like something that was compiled into a stored procedure or written into the application or is this someone who has compromised a trusted server and is running ad hoc queries?”
|SPLUNK QUERY: (Note the RegEx to parse out the Statement)
EH_DB_TRIGGER | rex field=_raw “Statement=(?<STMT>.[^:]+)\sProcessTime” | table _time ClientIP STMT
Another way to keep track of exactly who is accessing your SQL Servers is to keep an average ProcessTime by ClientIP and ServerIP.
What I look for in the results:
Question1: “Are all of the IP’s appropriate for SQL Queries?”
Question2: What is 192.168.1.98 doing to 192.168.1.205 that its queries are taking several times longer to process?
Question3: What is 192.168.1.205? Is that a proper database or has someone gone rouge.
EH_DB_TRIGGER | stats avg(ProcessTime) by ClientIP ServerIP
So let’s say we suspect 192.168.1.98 of possible malfeasance, I can now query the Extrahop data for every query that client has run for the last 24 hours. What we note from the query below is that this particular IP address has been engaging in some very undesirable behavior and by the time you have finished your tirade of obscenities you can call security had have them delivery him or her a cardboard box and escort them out of the building. Either way, you have adequate digital evidence for both termination or, if needed, prosecution as the log itself is fully intact on the Syslog server.
EH_DB_TRIGGER | search ClientIP=”192.168.1.98″ |rex field=_raw “Statement=(?<STMT>.[^:]+)\sProcessTime” | stats count(STMT) by ClientIP ServerIP STMT
In the past to get this type of data I have had to run the very invasive SQL Profiler. This tool can take up to 20% of your resources and you cannot run it on a long term basis. Using Extrahop’ s wire data you are able to collect all of this information (I have cross referenced it to SQL Profiler and in all cases, the metrics were EXACTLY the same) you can get access to very meaningful SQL data without impacting any systems. As always, this is completely agentless and required no reconfiguration of any SQL Server or any Client accessing the server. If you add 6 web servers to your web farm to accommodate extra front end capacity, you don’t have to worry about installing more agents, if they have an IP Address, you will see the data.
More importantly, while we have tools to detect viruses, malware and spyware it will not defend against a malicious employee or a trusted system that has become compromised. As part of the Human Algorithm, periodically inspecting the behavior of critical systems that house sensitive data is very important and should be a part of the overall INFOSEC strategy. Extrahop has better videos/documentation on monitoring SQL Performance but when used with triggers you can easily compare SQL performance and see changes in performance (better or worse) as they happen in real-time. If you are ever a Sys Admin that has taken a beating because a table needed indexing you know what I mean. Let’s be honest, they go after the systems folks first, network folks second then they look at the software. I am not indicting developers, there just hasn’t been a great deal of visibility until now. At my previous employer, we shared Extrahop metrics with Systems, Network, INFOSEC AND Developers. I think it is better to know that Application slowness is due to a single server in a back end database cluster before you double the amount of RAM, add more spindles/IOPS and upgrade the switch.
Also, please note that while this article uses SQL Server in the examples, Extrahop supports the major DB vendors (DB2, Oracle and MySQL) as well.
Thanks for reading