January 6, 2011 at 12:31 pm
If an application accesses the database though an account with ad-hoc query privillages, then how is a DBA to know there has been a breach? There would have to be some type of smart query profiling in place. Really, what is a "data breach", how to know when you see it? The hacker doen't always break things, sometimes they're just impersonating someone or cleverly leaking small amounts of data.
This is a lot simpler if the application account has no SELECT permission on anything, just public membership with EXEC granted on specific stored procedures. Also, clean out any unneeded SQL Server or domain accounts, so all you have left is the sysadmin and the application. Once done, it would be extremly difficult for even an internal user within the IT department to breach the database.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 11, 2011 at 3:44 am
Eric Russell 13013 (1/5/2011)
I'm not sure what useful information could be gleamed from database "transaction logs" in the event of a data breach, unless the hacker was modifying data.
True, though I've found that if they do modify data it can be very useful.
In my case, the one experience I've had with something like this was with a client where we managed the servers and the SQL installation, but they had a third party look after the website etc. During a christmas break, the badguys used a SQL injection attack (the big one that hit thousands of people in January 2008) to modify all the character columns in all the tables within the database by appending a javascript link to the end of the legitimate data.
After shutting the effected sites down we needed to determine when it had happened, since obviously no one had been using the site during Christmas, we had a possible window of about two weeks.
By restoring a copy of the database to a variety of dates, and then specific times during the day, I was able to pinpoint when the attack had started, and then use that to find the relevant information in the websites logs to see how it had happened, and which pages within the website that allowed it.
I think the key things with something like this are 1) don't panic, or allow management to force you to rush things. 2) if you can take immediate steps to remove risk without touching the server do so, eg stop website, disconnect from network etc, which will give you time to think and plan a proper response. 3) don't mess with the original data if you can help it, in my case I did all my testing on restored copies, but if it happened now in a VM environment I'd take a complete copy of the VM before doing anything.
January 11, 2011 at 8:51 am
In case of a database breach, I'd say that the Default Trace would be my first place to look. It would be a goldmine of information, assuming the DBA left the trace enabled, which it is by default. It's non invasive and will reveal who (at least which login), where (IP address with some additional effort), exactly when, and exactly what (T-SQL statements). The only thing it won't tell you is why, but after reviewing their entire session activity, I could make a good guess.
http://blogs.msdn.com/b/cindygross/archive/2009/12/16/sql-server-s-default-trace.aspx
Joining IP address with the Default Trace
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 16, 2011 at 7:42 pm
This is scare mongering. Exactly how is a "breach" detected? A system sophisticated enough to be able to detect illegitimate access is most likely set up properly to prevent it. This middle of the night scenario is pure fantasy.
February 21, 2011 at 10:23 am
rmaw-1095848 (2/16/2011)
This is scare mongering. Exactly how is a "breach" detected? A system sophisticated enough to be able to detect illegitimate access is most likely set up properly to prevent it. This middle of the night scenario is pure fantasy.
It's actually not. The easiest way for a breach to be detected is someone finding the release of data on the web somewhere. You don't necessarily know how a breach is detected, you just know one occurred. Not saying it's likely, but it can happen.
There are also services, like Counterpane, that can detect breaches, but don't prevent them. Prevention is harder than detection.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 21, 2011 at 10:44 am
Practically all database related breaches can be prevented by setting up user accounts with least privilege. Create a role that grants exec permission to only those stored procedures that the application needs to function. When adding a user or application account, grant them membership to nothing but this role. When developers or production support complain about "EXECUTE permission denied" errors, use the error logs, default trace, or SQL Profiler to identify what additional procedure they need access to, and then modify their exec list (if needed).
Don't let anyone tell you that the application needs SYSADMIN membersship, access to every table or even access to every stored procedure. They don't know what the hell they're talking about. It's better to let the application sit offline for a couple of hours until you fix the underlying problem than it is to leave the system open for a data breach.
Prevention is easier and less expensive than having to say "I'm sorry" to a few hundred thousand customers.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply