Tag: security

I wrote a question of the day recently that seemed to catch many people. The question had to do with mapping users when a login isn’t specified in the call. The behavior is to auto match existing logins with the same name. About 60% of the people answering the next day got it right, but a third missed it, expecting an error to be thrown.

One of the commenters was surprised that more people didn’t know this. I’d hope people knew this, though to be fair, I bet lots of people manage security through SSMS or a GUI and never write security code. I know I did for years early on. However I really think that the third of the people that got this wrong in its behavior, are actually right about how SQL Server security should work.

We do not want ambiguity when we configure security. We should be sure that rights granted (or removed) are exactly those that we expect. A strong security system should not tolerate any unexpected behaviors.

Security should require specificity.

Steve Jones

The Voice of the DBA Podcast

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

It’s probably no surprise to you that the black boxes for ships are vulnerable to hacking. These are the Voyage Data Recorders (VDR) that should capture telemetry, audio recordings, and more. These devices are really computers now, connected to the onboard networks used for satellite communications and physically accessible in many vessels.

It was surprising to hear that some of these VDRs are running Windows XP. While I get that there is some ease of development in using Window systems, that OS wasn’t what I’d call robust and stable for stressful and rugged environments. Some systems use real time OSes, which seems like a better compromise, but Linux might be the best choice for a system both tolerant to a variety of conditions as well as one that might be easy to build applications for.

However no matter what the choice, I’d hope that the developers building software for these systems would treat them with the importance they deserve. While lives aren’t at stake from these applications, liability is. These systems are used in legal proceedings, so the data they collect, in an autonomous fashion, should be protected to ensure its integrity.

Apparently that doesn’t happen, as there are incidents of these devices being hacked an data erased, corrupted, interrupted, or even accessed by those on the ships. That’s not surprising as it seems people always find ways to take advantage of the computer systems they physically control. Ultimately I’d hope that we might constantly transmit some of this data off the ship to ensure there are backup copies, but that brings to mind the problems of securing data in transit, preventing access or disclosure and more.

However these systems might provide a good testbed for researchers looking to better build and architect auditing systems. This is a challenging environment, with high stakes, and if we can develop ways to ensure auditing data is intact when we have lost physical control of the device for long periods of time, perhaps we can find ways to build this same auditing into other platforms.

Steve Jones

The Voice of the DBA Podcast

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

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