Voice of the DBA

Tag: security

  • Half Baked Features

    I gave a talk recently on some of the data protection features being added in SQL Server 2016, along with a few that have been available for some time. I think the talk went well, but I point out lots of potential issues with the features themselves. I had a question from someone that noted Always Encrypted, Dynamic Data Masking and more aren’t really full developed, are they?

    The question threw me, not because I agree, but because I think that there is a complex answer. Encryption and data security features are easy to use. The features really are easy to implement, especially encryption. Most encryption is just another function call for the developer, which is something almost all of us can write. The mechanics of using these features are fairly easy.

    However the architecture, the decisions on how to manage keys and where to deploy features, those are hard. We deal with those relatively infrequently, but when we must make those decisions, we should carefully consider the ways in which our systems might be attacked or mis-used.

    Some of the restrictions that I see in various features are unavoidable. At least, I can’t see a way to avoid them. In some sense, things like a binary collation are almost required because of the nature of how encryption must operate on data. Perhaps there are ways to mitigate issues, but I’m not sure. No security mechanism is perfect and all encryption can be broken. However I think the way these features work is good enough in many situations.

    There are some things, such as allowing Dynamic Data Masking on Always Encrypted columns, which can be implemented. However, the changes are more complex, and involve not just SQL Server changes, but probably also ADO.NET changes. Making the decision on how to actually implement these changes shouldn’t be taken lightly, and I’m happy to have a working feature that might be enhanced in later versions over not getting any options at all in this version.

    Steve Jones

    The Voice of the DBA Podcast

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

  • The Proliferation of Roles

    The best practice guidance for SQL Server security is to use roles for permissions, instead of granting rights to users. I’ve always followed this guidance in my career. I’ve learned that if one person needs access, sooner or later someone else will. Even if it’s an automated process, I’ll use a role so that I can build a test login to ensure I’ve configured things correctly.

    In small companies, or in relatively static applications, this makes perfect sense and few people argue with the rule. That is, until they get some requirement that only one account will ever be used. Then they want to just grant rights to a user. After all, why add the role for one person. See my thought above. Sooner or later, someone else will want access.

    However I also have had people complain that if each new required position or process needs their own role, sooner or later we’ll have this proliferation of dozens of roles.

    True.

    I worked in a large Fortune 100 company and we had thousands of groups in our AD forest, and easily dozens of roles in many databases. While that might seem complex and confusing, it wasn’t bad. We named roles to match AD groups or job functions, and adding in new users was simple as we usually mapped them to the same roles as a previous user. If a new system or person needs access, usually their access is the same as some other account.

    However, I know they can be complex, so I’m wondering if you have any tips, tricks, hints, or even gotchas for using roles. I’d also be curious if you think there are cases where roles don’t make sense.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Am I a sysadmin?–#SQLNewBlogger

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    I was doing some security testing and wondered if I was a sysadmin. There are a few ways to check this, but I thought there should be a function to tell me.

    There’s this code, of course:

    SELECT
    ServerRole = rp.name,
    PrincipalName = SP.name
    FROM sys.server_role_members rm
    Inner JOIN sys.server_principals rp
    ON rm.role_principal_id = rp.principal_id
    Inner JOIN sys.server_principals SP
    ON rm.member_principal_id = SP.principal_id
    where sp.name = SUSER_SNAME()
    and rp.name = ‘sysadmin’

    That lets me know if my login is a sysadmin. However, there is a function that you can use. IS_SRVROLEMEMBER() is a function that you can use, passing in a server role as a parameter. The code I’d use to check on sysadmin membership is this:

    SELECT IS_SRVROLEMEMBER(‘sysadmin’);

    If I run this, I get a 1 if I’m a member, or a 0 if I’m not.

    2016-04-12 11_38_34-Settings

    Using this function in your code allows you to make decisions based on role membership for the users involved, and perhaps alert them of needs for certain rights.

    SQLNewBlogger

    This was a quick one, really about 10 minutes to organize and write. Most of the time was writing the code to join system tables. If you tackle this subject, talk about how you  might use this, or where this type of check could come in handy in your code (maybe before taking some action).

  • The Pen Test

    We need something like the Air Force project seeking network vulnerabilities for our database systems. While there are tools that can help from the outside, but we need SQL Server specific tools. Why don’t we have a Best Practices Analyzer for SQL Server 2014 (or 2016)? It seems as though this is one of those tools that we should have available for checking against instances and determining if there are obvious vulnerabilities. Being able to add custom extensions for particular needs would also be nice.

    I guess if this is important to your organization, you’ll pay for a tool (or a pen test), but the problem is far, far too many organizations just don’t take security seriously. It seems that despite the huge costs incurred for not properly securing computer systems, the penalties aren’t nearly enough to get every company to patch their systems and write secure code. Most just hope they’re never attacked, or if they are, no one finds out.

    I’d really like to see some tools, or maybe just a best practice process that would allow system administrators to test their own systems. I found this post from PenTestLab, and I think it’s great. The article is a good start for what to look for and what issues might exist. However even better than this might be a series of automated items we can run that showcase to management, developers, and DBAs when they’ve made a mistake that potentially opens a system to attack. Maybe best of all would be getting software vendors to actually run these types of tools against their applications and provide some proof they’ve built secure code.

    I don’t hold out hope that security will improve anytime soon, but I do hope that more system administrators learn about what issues potentially exist and check their systems for potential vulnerabilities before someone else discovers the issues exist. Lastly, I hope they all share what they learn.

    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.