Many social networking sites these days (most of which I could consider in the startup phase) have the concept of private and public accounts. In an attempt to appease those that may not want their daily activities indexed by the world, they set a simple flag that indicates the status of the account.

This is inherently broken.

Why do I make that statement? Because when there is no enforcement of that “private” data other than a database flag, it should be considered public. Let’s take one of our favorite examples, Twitter. A vulnerability in Twitter was recently announced (and subsequently patched) that revealed protected account’s messages. The flaw here is simply that Twitter didn’t really consider this use case when writing the SQL query and forgot to account for protected accounts with a small SQL statement that probably looked like “AND protected = 1“.

So why is this a problem?

Because the application server always connects to the database server as the same user and it is the responsibility of the developers to not forget to account for protected accounts. Unfortunately, we as humans are fallible and while this practice continues, mistakes will be made.

How can we fix this?

There are several approaches, but I’ll talk about a couple. One relies on process, the other on technology.

  1. Threat modeling, data flow and data classification

    This really should be done anyway, but there’s a process known as threat modeling where application developers, business owners and security personnel review an application and identify the data that flows through it, potential avenues of attack and the classification of said data, among various other things. Every application should have this concept of data flow and classification (public vs. private) and verify new code against this model during the development process.

  2. Build identity into the data layer

    More mature databases such as Microsoft SQL and Oracle have various concepts of row-level security where options exist to further limit access to data based on a combination of data classification labels and restrictive views. Combined with impersonation, this approach actually relies on the database to enforce privileges, rather than the developer. While I know views and the potential for row-level security exist on MySQL, I don’t think they’re considered or used very often.

The moral of the story is that your data may only be as “private” as the security awareness of the developer coding your SQL queries.

Oh, another topic I should talk about sometime is security through obscurity or other ineffective means. That link I posted to sqlpass.org? That “login” box is simply a CSS overlay that can be removed in about three steps using one of my favorite tools, Firebug.

Update: Somebody asked where they could find more resources about Threat Modeling. Microsoft is actually one of the best places for this and the Application Threat Modeling page and Threat Modeling blog provide hours of endless entertainment. They also have a fantastic Threat Modeling Tool that greatly helps the process. Free, no less! And they are apparently releasing an update soon. I’ve had the pleasure to work with Microsoft in the past and they are very dedicated to the Secure Development Lifecycle. (*I’ll ignore the peanut gallery’s comments about the recent out-of-band patch, but that actually is a good example of how well their process works*)