You’ve been Hacked! [T-SQL Tuesday #63 – Security]

2015-02-10 - Audit, Cryptography, General, Security, T-SQL Tuesday

T-SQL Tuesday #63

T-SQL Tuesday #63 is hosted by @sqlstudent144 (b|t).
This month's topic is "How do you manage security?".

You've been Hacked!

I took a few minutes to collect the largest breaches that happened over the last 10 years:

  • 2004 – AOL – 94,000,000
  • 2005 – Cardsystems Solutions Inc – 40,000,000
  • 2006 – Veterans Administration – 26,500,000
  • 2007 – T.J. Maxx – 90,000,000
  • 2008 – Countrywide Financial – 17,000,000
  • 2008 – Bank of New York Mellon – 12,500,000
  • 2009 – Heartland Payment Systems – 130,000,000
  • 2011 – Sony – 100,000,000
  • 2011 – Citibank – 360,000
  • 2011 – Tricare – 5,000,000
  • 2012 – Global Payments Inc. – 1,500,000
  • 2013 – Evernote – 50,000,000
  • 2013 – Adobe – 36,000,000
  • 2013 – Target – 70,000,000
  • 2014 – Ebay – 145,000,000
  • 2014 – JP Morgan Chase – 76,000,000
  • 2014 – Home Depot – 56,000,000
  • 2014 – Community Health Services – 4,500,000
  • 2015 – Anthem – 80,000,000

The number behind each name reflects the count of customer accounts that were affected by the breach. If you sum these numbers together you get to 1,034,360,000.

And that includes only the larger incidents. For a more complete list of breaches look at the Infographic over at

The type of data the attackers are after has changed over time. Ten years ago, people were looking to make money by getting access to your email and misusing it for spam. Leaked usernames and passwords were common at the time. Later credit card information was targeted, to allow the bad guys to buy stuff in your name. Over the last few months, we have seen attackers targeting end user SSNs. That allows them to file false TAX returns and collect refunds. It also allows them to open new credit cards in the victims' names and make money that way.

The attack methods get more sophisticated, but so get the amount of damage that each affected victim might incur. But, even a hijacked email account that was taking over with a stolen password can be detrimental for the victim. The average cost of a breach for a company currently hovers around $156 per affected record (customer). However, for each affected customer, the damage might be significantly higher.

Wake Up!

Just over One Billion Accounts. That makes it extremely likely that you are a victim yourself. (I have been affected three times so far Target, Home Depot and Anthem.) This is not some theoretical stuff anymore that happens somewhere out in the dark corners of internet. This is real. This is affecting you.

I honestly feel like I should not have to say anything else. However, it seems that someone has to say it. So let me attempt to:


You would think that people have noticed the incidents over the last few months. Yet, it seems that many CEOs take this position:

This is actually one of the most common excuses for not investing into security.

But enough of the ranting. Assuming you work in a company that is not ignoring the problem, what can we as database people do to increase security? (If your company is still blind to the problem, it might be time to update your resume.)

A Good Security Strategy resembles an Onion.

The Database

Security is not, and cannot be, a one-place solution. Installing an expensive application firewall might put you ahead of the competition when it comes to network security, but not all threads originate on the outside. Encryption of the database can protect your data against some attacks but will leave it totally open to others.

Instead of focusing all your security energy in one area, you have to diversify your strategy. Your security strategy should look like an onion. There are many layers in your system. Each one provides a potential attack surface. Each one needs to be protected, independent of other security measures.

Simple Measures

When designing your database security strategy, there are three things that you have to do:

  • Follow the Least Privilege Principle as closely as you can.
  • Encrypt sensitive data.
  • Audit

Least Privilege Principle

The least privilege principle instructs us to grant everyone just as much access, as they need to do their job. This applies to the account the application uses to access the database. Making that account a member of db_owner is in almost all cases a very bad idea.

But it also applies to the "administrative staff". If the junior DBA's job is to backup databases, he does not need to be a sysadmin. And even if your job requires sysadmin privileges, you probably do not need them all the time. Use a less privileged login for you day to day work and switch to a sysadmin login, when it is really necessary.


Encryption protects the data at rest. If the backup tape gets lost on the way to offsite storage, encrypted data is protected. If the defect SAN hard drive is sold instead of shredded, encrypted data is protected.

If implemented correctly, encryption can even protect the data from the prying eyes of the DBA. Most DBAs do not need to be able to look up Bill Gate's SSN to do their job.


Most data breaches take a long time. The reason is that the attacker often cannot "download" the entire database at once. Instead, they have to issue millions of queries to piece the data together. For example, the just published Anthem breach is rumored to have been in progress since April 2014.

A good audit log together with regular reviews of the same can detect suspicious activity like an unusually high number of queries or even errors originating from a single IP address. Any unusual large volume query activity is likely caused by an attack.

10 Common Database Vulnerabilities

The above three areas are not everything you have to pay attention to when designing your database security strategy. But they are a solid start. If you want to know more about common database vulnerabilities and how to protect your data from them, I recommend you get my free poster:

The 10 Most Common Database Vulnerabilities.

The poster lists the 10 most common database vulnerabilities and describes briefly what to do prevent that vulnerability. Each vulnerability links to a more detailed explanation.

If you hang this poster on your wall, it serves as a great reminder, that security is important, and cannot be an after thought.

Get yours now. It's free!

Categories: Audit, Cryptography, General, Security, T-SQL Tuesday
Tags: , , , , , , , , ,

2 Responses to You’ve been Hacked! [T-SQL Tuesday #63 – Security]

  1. Pingback: TSQL Tuesday #63 – How do you manage security: Rollup | SQL Studies

  2. Pingback: TSQL Tuesday #63 – How do you manage security: Rollup - SQL Server - SQL Server - Toad World

Leave a Reply