Voice of the DBA

Tag: security

  • Data Breach Danger

    Recently a court in the US ruled that there was no imminent danger from a data breach at a Texas hospital. This is good news and bad news for the world, and I’m a little torn about how I feel. On one hand, it’s good for us as data professionals that we aren’t necessarily going to be liable for the immediate effects from lost data. While the losses aren’t always our fault, we certainly could feel pressure from management if companies faced immediately legal or financial penalties.

    However it’s bad news because I think there’s little else that data breaches do than cause harm to those whose information is lost. It can be incredibly hard to link a specific breach to a specific identity theft incident, and I see this as a way of allowing companies to escape liability for their poor security practices.

    In reality, however, I have no solution to propose. As a data professional, I try to keep data safe, but it’s very, very difficult. One small hole in your technical infrastructure or human employees and you can lose a ton of data very, very quickly. I know it’s not lost, but copied, however you have lost control of it.

    We will face more and more security incidents, and as those tasked with protecting data, I’m not sure what we can do, or should do. However, I do think that organizations can’t take all responsibility, nor can they take no responsibility. The balance of how to deal with losses and issues is certainly something I hope we work out a a society.

    Soon.

    Steve Jones

    The Voice of the DBA Podcast

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

  • SQL Injection, Still?

    It seems as though SQL Injection issues are still around. Attacks from SQL Injection are on the uptick as we begin moving through 2015. As noted in that piece, the constraints put on software developers usually mean that testing and security are the first things to let go when time becomes short. That’s sad, and in some sense, I think this means that we aren’t teaching secure coding early enough to developers, and certainly not often enough.

    I ran across a piece from Kevin Kline that asks why this keeps happening. After all, as Kevin shows, much SQL Injection is easy to prevent. The coding patterns and tools we use are simple to write. There are lots of articles out there that show a variety of techniques you can cut and paste into your code. However there are two big problems that prevent us from eradicating SQL Injection: aging code and bad habits.

    There’s no shortage of code that comes from frameworks and application templates, not to mention naive or ignorant developers. Lots of this code is vulnerable to SQL Injection. Since so many of these existing applications work, there is no great pressure to go back and change them to be more secure. Since data theft may not even be noticed, there are plenty of companies (and technologists) that have no idea their systems are vulnerable.

    The other problem is bad habits. Far too many developers and DBAs have spent years writing insecure code. When they prototype, mock, or quickly knock out code, they often rely on their experience to get work done quickly. And they do this poorly if they aren’t writing in a pattern that prevents SQL Injection. They haven’t updated their templates, tools, or their knowledge in a way that ensures all their future code will be secure.

    Ultimately we as an industry need to take SQL Injection seriously and write better code. Whether you use an ORM, a framework, or anything other shortcut to build applications, if you don’t create secure software, you’re part of the problem.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Detecting Encryption

    I ran across an article recently from MSSQLTips by my friend, Brian Kelley. It talks about the ways you can detect encryption in use in your database. Brian’s approach, which is one I agree with, is that you can look for symmetric keys, asymmetric keys, and certificates in the system tables. The tables you query are:

    • sys.symmetric_keys
    • sys.asymmetric_keys
    • sys.certificates

    That’s a good way to detect SQL Server encryption in use, but not encryption in general. One of the things I’ve advocated for applications that contain sensitive data and need to be protected from the DBA is to have the application create temporary keys or use .NET libraries to encrypt data. In that case, SQL Server just sees data, and doesn’t detect encryption.

    Brian offers a solution that is to examine any columns containing these data types

    • binary
    • varbinary
    • image

    That’s a good start, but how do you detect that this string is encrypted?

    504b 0304 1400 0000 0800 1a86 4640 0d41 …

    That’s actually not encrypted; it’s the start of a zip file. However it could be a jpg, a tiff or some other binary format. The only way I thought of was mirrored in this Stack Overflow note: you’d have to compare known file types and look for a pattern in a header of some sort that doesn’t match. It wouldn’t be sure you didn’t have encryption, but you might make some educated guess if no file type that might fit the data matches.

    There was also a link in the comments to a Stack Exchange discussion on the same topic. It’s similar, though I saw the use of the KEY_NAME() function in there. I hadn’t used it, perhaps because of the poor documentation of encryption in SQL Server. I also found a KEY_ID() function that works similarly, returning the ID for the name of a key.

  • T-SQL Tuesday #63 – Security

    tsqltuesdayIt’s T-SQL Tuesday time again and this month we look at security. Kenneth Fisher has chosen this as his topic for February and you can read his invite here. There are lots of choices on what you write about, and I’m looking forward to reading what people choose.

    You can join in, by writing a post today and publishing it with a link in Kenneth’s invite. Or you can write later and just put your own thoughts down on the subject.

    T-SQL Tuesday is the idea of Adam Machanic (b/t) , and it’s a monthly party where everyone writes on a specific topic. The first Tuesday of the month usually has a new invitation issues, and you have to watch for it. I’d recommend putting a reminder in your calendar. The second Tuesday of the month is when we publish posts.

    If you’d like to host, contact Adam.

    Security Across Environments

    At one point in my career, I worked with a startup company. We had a number of experienced people working in development, and we wanted to set up a series of environments early on to perform agile development. When I arrived, the application we built had been running for about 4 months, and we were looking to improve our data handling and development processes.

    At the time, we had a production server and a development server. There were accounts for the web application and the initial security had been to grant security on tables as appropriate for the web application. Any tables that existed for administrative use were limited to sysadmin access.

    This wasn’t a bad plan, but as we implemented a test environment, I knew this would be an issue. We didn’t want to give testers (or developers), access to the production AD account that was used by the web application. We also didn’t want any problems during deployment.

    Moving to Roles

    It can be hard to change security around on an existing application. Fortunately we had limited numbers of objects and applications accessing our SQL Server database, so I could easily determine if refactoring was going to break anything.

    My first refactor was to create two groups in each environment. I used code similar to this in Development, QA, and Production

    CREATE ROLE WebAppUser;

    CREATE ROLE WebAppAdmin;

    By creating these roles in each environment, we had a consistent place to set security for objects. We proceeded to assign generic read/write/execute security to objects to these roles as needed. The WebAppAdmin role accesses all objects (essentially as datareader/datawriter). to grant rights to the WebAppUser role for specific objects, we scripted out the rights assigned to the current WebUser user and then granted those rights to the role.

    The last step was adding the WebUser to WebAppUser. Once this was done, we essentially had duplicated permissions for the user, WebUser, through the user account and the role.

    Our test procedure for the change was to begin removing the rights granted to the user in the QA environment. Once we verified the web application still functioned, we made one final change.

    In the Development environment, we created a new user, WebAppUserDev and put this user in the WebAppUser role. We then changed IIS to use this user account. From this point forward, development was separated from production. The Windows admin changed the WebUser password and development was locked out of production. We did the same thing in QA and created a new account there.

    Once everything was done, we removed all direct object rights from the WebUser account in production. This was a scary day, as we were counting on our role having all the correct rights. Fortunately our process had worked, and the application continued to function.

    That was a lot of work, but a refactoring that doesn’t break anything can take some time. There will be multiple steps and it can take days or weeks to implement.

    Moving Forward

    From this point forward, development proceeded without problems. All of our object code now included one, or both, of these lines at the bottom of the script.

    GRANT EXECUTE on MySP to WebAppUser;

    GRANT EXECUTE on MySP to WebAppAdmin;

    We migrated object code between environments, but not security. Security for each environment was handled separately, with separate accounts added to these roles. Our deployments became much easier.

    When we needed a new role (for client auditors that could access specific tables), we added the role as a part of our deployment and assigned security to the role. The role deployment was handled separately from application deployment with a new role being deployed in a script, but a different user added in production by the sysadmin to AD and the role. Later deployments had permissions grants for the role that were the same in development, QA, and production.

    When we added a Staging environment, it was as simple as restoring the production database, deleting the orphaned WebAppUser user from SQL Server and creating a new user for that environment.

    Consistency

    One of the big issues with deployments not proceeding smoothly comes when one environment is not set up the same as others, and the scripts run in one environment do not execute on another. When users are included in all environments, you have a security hole, but when different users get different security, you create scripts that must be edited, and potentially mid-edited.

    The more you can abstract away portions of your application, whether this is through roles, synonyms, linked servers, or other items that can be named consistently, but configured differently in separate environments, the smoother your deployments will be.

    There are certainly challenges with deploying new items across environments, but that’s a discussion for another day. For security purposes, I think that roles are an important way to ensure that security is maintained, but deployments are not impacted.