6

Filesystem intrusions can be detecting using tools such as Snort but it is more difficult to detect intrusions into a database, such as deletion of rows, modification of tables, etc. What is the best way to monitor this to detect unwanted changes in the DB?

I am using MySQL so anything that is not database-agnostic should ideally be aimed at MySQL.

Brent Ozar
  • 4,425
  • 17
  • 21
davidmytton
  • 666
  • 3
  • 7
  • 17

8 Answers8

2

It depends how do you connect to your database. If you are using a web application, Snort (and other NIDS) will be able to detect SQL injections and other attacks that happen over HTTP.

The issue is if you are using SSL or encrypted connections to your db, your NIDS will be blind to the traffic.

That's why log analysis is very important. The only way your db talks back to you is through the logs and many DBAs are not familiar with it. I really don't understand why everyone accepts web logging as familiar, but neglect db logging (I will rant more about it another time).

To enable mysql log: http://www.ossec.net/wiki/index.php/SQL_Logging#MySQL_Logging

I also use the open source OSSEC to monitor my MySQL logs and it works great.

sucuri
  • 2,817
  • 1
  • 22
  • 22
1

I don't use MySQL, so I can't speak to any specific features of the platform.

It sounds like you want an audit trail of some kind. Speaking in a general RDBMS sense, you might be able to use triggers to get you some of the functionality you're looking for. I don't think you're going to get an audit-trail of schema modification unless MySQL represents the schema as tables that can, in turn, have triggers placed on them.

Of course, all that trigger nonsense is moot if someone gets "root" level access to the database and just unhooks the triggers before they start monkeying with the data. At that point, all bets are off. (...and that doesn't even start to deal with someone getting "root" level access to the OS hosting the database... byte-level manipuation of the database files, mounting them up on a database instance that has had security features "hacked" out of it, etc... smile)

Evan Anderson
  • 141,071
  • 19
  • 191
  • 328
1

If you really want to track every change to your tables, you'll have to do something crazy like enable the MySQL query log and scan for the bad stuff using something like Simple Event Correlator. Don't do this though, because it will kill your server's performance.

Honestly, your best bet is to prevent unwanted changes in the first place by using MySQL permissions.

Mike Conigliaro
  • 3,105
  • 2
  • 24
  • 24
1

Snort can still be helpful. The catch is knowing where your database traffic should come from. If it's coming from a source other than what's approved, you can block it, obviously, within MySQL. However, you can also set up alerts in your IDS to see this sort of thing.

With respect to an attack coming from an authorized IP address, that's a bit of a challenge. The key question there is what should a connection be allowed to do and what shouldn't it? And that goes back to setting permissions properly. If a legitimate user connects from a legitimate IP and needs DELETE permissions and wants to be malicious, there's not a whole lot you can do about it during the actual modification. A suggestion was given for auditing, but it hurts your performance. I'm not sure you have an effective control if users can directly access the database and make changes. All database platforms, not just MySQL, struggle with this. You have a trusted user making an authorized change. There's only so much you can do.

K. Brian Kelley
  • 9,004
  • 31
  • 33
1

There are commercial products designed for this. I think we looked at DbProtect (www.appsecinc.com) and it was major dollars to implement, but we ended up not doing it. I also have seen Guardium (www.guardium.com). Both claim to support some version of MySQL.

bobwood
  • 213
  • 1
  • 2
  • 5
0

SQL Server 2005 introduced DDL triggers, these can be fired off whenever someone runs Data Definition Language code like altering a table, adding an index, or dropping a view.

gtapscott
  • 66
  • 2
0

I do not believe that there are any suitable methods to do this in MySQL. You should make sure that a security checksum of sorts is applied to all legitimate modifications, using a secret key of some sort, so if the row data differs from the checksum, you know an unauthorised modification has been made.

As for how you checksum, and how you keep the key secret, that's a whole other story, which you may feel free to email me about, if you figure it out from or via this site (I'm new here and not set up properly yet).

Realistically, only off-site backups will help you guard against deletion of rows, and note that using mysqldump is the only 'officially' supported method of doing so.

Although copying the underlying (MyISAM) files works in the vast majority of cases for that table type, it does fall down on occasion, so I wouldn't trust it alone for anything mission critical.

0

We implemented DbProtect on a SQL Server database. It wasn't too hard and it allowed some pretty granular policies for auditing. However as mentioned by bobwood, it's not cheap.

David Yu
  • 1,032
  • 7
  • 14