Voice of the DBA

Category: Editorial

  • Why Use the Principle of Least Privilege?

    This editorial was originally published on April 12, 2011. It is being re-run as Steve is away on vacation.

    SQL Injection is not the fault of the SQL Server. Brian Kelley pointed that out, and reminded me that SQL Injection isn’t an case of malformed SQL. It’s legitimate code, including SQL commands that we might use from any query connection, especially an administrative one. We regularly issue update and delete commands from our applications, and SQL Injection takes advantage of this to issue an update the we might not be expecting.

    Would you expect this input handwritten injection from an application? Or this table guessing attempt? You wouldn’t, but they can come through data entry in your application if the input isn’t well sanitized. Someone setting all your prices to $0.01 or all of your customers to “W3 0wnz U!” isn’t what you want to happen. You can’t necessarily prevent all of these patterns  or check for every permutation, but you can prevent things like ‘shutdown’ or ‘drop table’ from being run by your application. Even adding a new user to the database system isn’t something I would want to allow.

    Education is the key here. As Andy Leonard (blog | @AndyLeonard) would say, design patterns are important. When developers have an understanding of the issue, many of these things will be avoided. Having standard ways to begin building an application, checking for bad input, and setting up database users and permissions easily, should make this easy for anyone that wants to code against a database. We still have work to do here to build better frameworks, and ORM tools that require elevated permissions to the database are not the answer. They might become the answer, but they aren’t a better solution right now.

    Grant Fritchey wrote a nice piece about developers and DBAS, noting the need that we both have the same goals, but need to learn to communicate better.  This is one area where we ought to make an effort to communicate better, pass along education about security issues, and work to make life easier for developers to work with a database.

    That also means teaching them to work with the minimum privileges needed in order to make an application work, just in case someone plans on submitting some input you didn’t expect.

    Steve Jones

  • An Impressive SQL Server

    This editorial was originally published on May 6, 2011. It is being re-run for the Christmas holiday.

    Merry Christmas and Happy Holidays!

    Recently I heard about one SQL Server with 1TB of RAM and another one that was handling over 10,000 transactions a second. I read a note about a 1.1PB database, built on SQL Server to hold data from telescopes. That particular data warehouse is producing 1.4TB a day. I know it’s mostly binary data, but that’s a lot of 1s and 0s being stored in SQL Server.

    Over the years I’ve heard regular complaints and concerns from executives about SQL Server scalability. Those complaints have dwindled, but I think there are people in the relational database world that still question SQL Server’s ability to handle high volumes or data sets.

    This Friday, I wanted to see what might change their mind, or maybe what might make you view SQL Server differently. While I suspect most of you are SQL Server fans, that doesn’t mean that you don’t have concerns at times about how far you can push SQL Server. The question this week is:

    What company would impress you if SQL Server backed their main systems?

    This isn’t scientific, and I’m not defining which systems impress you. It could be their web site, their supply chain management, their largest data warehouse, or anything else. What company talking about using SQL Server in one of their systems would impress you? And which system would you like to see backed by SQL Server?

    When I think about a lot of data, high volumes and rates, I think of a few things. The stock market, sports, and Amazon. If Amazon switched their website and ordering systems to SQL Server, I’d be impressed. If Major League Baseball, or the National Football League were to run all their statistical systems on SQL Server, I’d be impressed. With all the fantasy sports fans out there, the gathering and processing live data for real time calculations of performance, would be impressive.

    I’ve left out other companies. Wal-Mart using SQL Server for supply chain management would be impressive. UPS managing packages on SQL Server would be something. I’m sure have your own ideas, and let us know this week.

    Steve Jones

  • Correct Old Mistakes

    I ran across this piece on the VTech hack that recently occurred. It’s almost a classic example of what not to do in data storage. You can read the piece, and also look at Troy Hunt’s analysis, but clearly we can see that poor encryption, unencrypted communications, plain text storage of passwords, and more. What’s especially disconcerting is that we have kids’ information disclosed, plenty of which could be problematic years down the road as these kids grow up.

    Apart from all of the technology issues, there are certainly responsibility issues. I expect that VTech will deny knowledge of issues, and certainly limit the amount of time they admit to knowing about the security issues. After all, they’re a corporation and if they can deny liability it could certainly limit the number of actions taken against them. However I’m hoping that the developers and operational people that manage this technology realize they made mistakes while building these systems.

    There’s a certain immaturity that’s prevalent in the analysis of this system. I’m guessing that developers were under pressure to get websites up and running, in concert with product launches, and that plenty of code was shared among their various sites and web domains. However I would hope that the current developers at VTech would have learned more about building robust applications, and would be looking to rewrite and rebuild their systems to be more secure with more current technologies. I hope that any of you running Flash based systems, or using MD5, or any other well known, poor security practices, would be pressing your management to correct those deficiencies and giving them more secure solutions. You might also give them a copy of the article linked above.

    Likely most companies out there, I’m guessing VTech’s management don’t want to spend money to rebuild systems that work, regardless of security flaws. Likely developers that have learned how to better code public facing sites don’t have the time to spend rewriting old code when they have new systems to develop. However I think that the old code that lives out there, that poorly built code that most of us have written in our past, would get updated over time, as part of the cost of doing business. This is especially true for anyone using encryption, where upgrades should be regular and mandated as Moore’s law and better mathematics consistently eliminate the security provided by older algorithms.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.5MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • Hacking Data

    This editorial was originally published on Mar 23, 2011. It is being re-run as Steve is away on vacation.

    I saw a note recently that researchers had successfully hacked a car using only an MP3 file on a CD. They were able to lock the doors and kill the engine in a car. That doesn’t necessarily sound too scary until you consider that the ability to kill any percentage of car engines during rush hour traffic could have catastrophic results.

    How much of an issue is this? I don’t know, but as long as there is some interpreter that has to decode digital data and render it as audio, video, or even text, there is the chance that additional code could be added to hack the system and allow someone to take control. When I think about all the ways that we get digital data these days, it is truly a scary thought that we could have these security holes.

    Think about it, MP3s could be sourced at a retailer like Amazon. Adding code to a popular MP3 song could infect millions of people that burn the MP3 to a CD, or connect their iPod to the stereo. The advent of HD radio could invite hackers to target broadcast centers and alter those files. Navigation systems and traffic data streams could potentially be carrying digital viruses that infect our systems.

    However that same idea could be extended in other ways. The more knowledge someone has about your internal systems, and the more they are connected, the more likely that just the addition of data to streams could have unexpected events. Suppose someone understood the complex relationships between various ordering and supply chain systems. Is there a chance that they could send in a sequence of orders that would disrupt your systems? Could someone inject data that somehow starts a chain reaction of workflows across your enterprise?

    It seems unlikely to me, but then again, 5 years ago I would never have considered an MP3 file might allow someone to gain control of a modern car. Security is a tough business, and there will always be new, creative, unbelievably exploits that are discovered. The best defense I can think of is to share information and never assume your systems are invulnerable to a new attack.

    Steve Jones