Voice of the DBA

Tag: security

  • Republish: The Opportunistic Hacker

    Still in Ireland: The Opportunistic Hacker.

  • Getting All Users

    I saw someone that wanted to get all users from all databases on their instance. Seems like that ought to be simple, right?

    The user wanted to use sp_MSforeachdb to query users, but wanted a single result set for all databases.  Why you need this, I’m not sure. I guess some auditing report. Or maybe looking to clean up and remove unnecessary users?

    In any case, this turned out to be easy with a caveat. It’s not in one single statement..

    DECLARE @MyUsers TABLE ( dbname VARCHAR(200), principalname VARCHAR(200), principalSID VARBINARY(MAX))
      INSERT @MyUsers
    
EXEC sp_MSforeachdb 'select ''?'', name, sid from [?].sys.sysusers'

    SELECT top 1000
      *
      FROM @myusers

    Use the ? in the query to get the users from every db. Then store this in a table that you can pull back all the data.

    This worked for me, though since I have some database names with a hypen (-) in them, I needed the brackets to get this to work.

  • Taking Shortcuts

    Many of us work in situations where we feel pressure to get work done at a pace that’s faster than we might like to move. In some sense, this is the nature of life, where customers and clients always want something done immediately. Whether they’re ordering food or building a house, it seems that quite often the customer expects the creator to just work harder and faster, without making mistakes, and deliver the goods quickly.

    That may or may not work, but if does often result in issues in software. While we can fix them, there can sometimes be larger issues, especially where sensitive data is involved. There was an incident recently that reminded me of this, though fortunately, it appears the data loss was minimal in scope and sensitivity.

    The mobile app at a recent security conference leaked data. The builders of the app embedded security keys and passwords that allowed anyone that registered to download a database of attendees. Fortunately this was a limited set and it appears only names were exposed. However, it could have been much worse, especially if this were a typical non-normalized database that might contain all data about an attendee in one row.

    I don’t know the timeline here for development, and I certainly don’t know the requirements. I do know that embedding keys and passwords into application is a bad idea, and even worse when those applications are going to be installed on customer devices. These are fundamental rules, and I certainly hope that whoever worked on this application, and anyone reading about this story, knows not to do this again.

    No matter how rushed we are, it’s important that we follow some practices and include some seucrity in our systems. I’d argue that data security ought to be number one and built into the system from the start. As the GDPR asks, we should be ensuring this is included by design and default. As much as it might seem that new legislation is overreaching and burdensome, I’d argue that mistakes like this one are all too common when we feel pressure to get work done. We shouldn’t be making these mistakes, nor should be be pressured to ignore security for the sake of expediency.

    Steve Jones

  • Good Security Needs Layers

    How many of you have wanted to know who started or stopped a SQL instance? Probably a few of you, as disruption to the service can affect customers. Most of us are concerned with the changes made inside SQL Server to objects and data, and that’s what the auditing features inside SQL Server are watching. The problem is that the database platform is dependent on the host OS, and as such, some actions take place at that level. Auditing inside SQL Server isn’t setup to capture this information.

    Should you care? Well, restarts, or the stopping or a service are one way that a malicious actor could alter files, change the error log without you realizing it, or even copy files to other systems. All these actions might be outside of any auditing or event tracing you’ve set up. Good security needs multiple layers because the system you need to protect is often dependent on some other part of our infrastructure.

    Databases depend on the host OS and perhaps directory services. Your OS may depend on a hypervisor, and certainly needs patching, so it depends on human administrators. Many of our systems depend on networking and firewall configurations. There are other layers, but the more that we can ensure each layer is secure, the better off we are. Certainly our systems always depend on humans not giving away credentials or installing malware, but that is often something many of us can’t control.

    I ran across an article that explains how to use auditing at the Windows level to track this down, and ensure that there aren’t more unexplained restarts. You can implement this, but if you don’t have Windows administrative privileges, you’ll need to get help from someone that does. Likely a couple of you have been glad that there isn’t a great way to audit this from the OS, as you were the one performing a restart without permission. If that’s your MO, I expect you might not want to pass this piece along to your security staff or auditors. If that’s the way you work, though, I would advise you to change your habits.

    Steve Jones

    The Voice of the DBA Podcast

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