Voice of the DBA

Tag: security

  • T-SQL Tuesday #183: Improving Permission Management

    This is my (late) answer to my own invitation for T-SQL Tuesday #183. I was very busy a few weeks ago when the invite when out (glad it was scheduled) and I never got this done. This post looks at how I managed permissions in the past.

    Large Enterprises

    Many of us would like to think that large enterprises have standards and they’ve learned about best practices. My experiences in 3 of them were that so often large enterprises were small ones that grew, often with lots of tech debt and busy staff. Even when there are limitations to ensuring good security, often we can’t just fix things because we might break something.

    In one large company (5000+ employees) I found that many of the database servers I managed had permissions set in a variety of manners. Often this included lots of individual permissions granted to logins and users, which was a mess.

    Even when AD groups were in place for departments, they weren’t used as logins in SQL Server since not everyone in a department needed to access the database.

    Cleaning Up

    I hated managing permissions by user. When I started and got a ticket to add a new person to a database for access, I watched someone train me by looking up another similar user in the database, scripting out their permissions, and then search/replace to change the user or login name.

    Not a bad solution, but one that doesn’t scale well over time. What I started to implement was to create an AD group (for Windows users) or a database role (for SQL Users) and then add the login/user to this group or role and assign the permissions from my “other” user to the role. I’d also often move the user to the new role and revoke their specific permissions.

    While this seemed like more work at first, this quickly started to scale well as we had a way to add new users to roles that matched the access they needed.

    Slowly over time we moved a lot of access to AD, which allowed us to remove the burden of disabling users in SQL Server. Plus, we could easily see which access a variety of users had by looking at a role rather than checking multiple accounts. Auditors liked that and it helped us pass various audit checks over time.

    Use roles and groups. They’re not hard and they make things cleaner and easier over time.

  • Is Ransomware Fading?

    There were less ransomware payments in the second half of 2024, according to research from cryptocurrency tracing firms. There were certainly some high profile attacks, but this matches with my impressions from talking with lots of fellow data professionals around the world. This article talks about the trend, and speculates that law enforcement actions may have had some effect.

    My guess is that this is likely a temporary decrease as there are no shortage of criminals and so many tools are available on the Internet, especially the dark web. The idea of writing a piece of software to encrypt files isn’t complex, and we’ve had people writing viruses for years. I suspect there are no shortage of smaller criminal organizations and individuals that will step in to continue attacks in the future. Whether that will be a lot of attacks or a few, I don’t know.

    One of the other problems is that so many organizations are loathe to disclose they’ve been hacked if they don’t have to do so. Lots of them would prefer to just pay a ransom and get back to work. I don’t know how many IT pros agree with that, though often the employees just want to get past the attack as quickly as possible and restore their systems. I know that they often worry about future attacks, but I also wonder if many IT pros know how to check their systems to be sure the malicious software is gone.

    Securing your environment is hard, especially when most users (and IT people) want convenience. Many infrastructure people want to log in with a single account and get things done. Or they want an easy way to switch accounts when necessary, which isn’t always convenient. Some of us are used to the runas command, but I’ve met many people who aren’t.

    I do like that much of the world is moving to using managed identities or service accounts for processes, known accounts for CI/CD that can handle deploying code while each of us just approves the deployment with our own credentials rather than directly moving bits. I am glad to see more and more people without rights to log into production, only to submit batches to a system to run and get results sent back. All of those are good things which can prevent an infection from a website or email from spreading to production systems.

    However, we still have lots of interconnections between systems for important data stored outside of relational systems. Even storage explorer type access for Delta/parquet files can be a problem if you have that. Databases are safer from ransomware, assuming you can lock down all OS/file system access. Maybe we ought to store more data in databases, even those crazy Excel/Word/etc. documents as binary files.

    I’m OK with that, as long as we have a separate instance for those files. I have no desire to see more binary files stored in my OLTP database, or even on the same instance.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

    Note, podcasts are only available for a limited time online.

  • Get a PAT in Azure DevOps

    I have a presentation recently on Continuous Integration Using Local Agents in Azure DevOps and one of the things I do in there is get a PAT for Azure DevOps to use in configuring an agent. This post shows how to do this, as I found this wasn’t obvious when I was searching out information.

    Azure DevOps Organizations

    In Azure DevOps, at the top level you have an organization. Here’s my way0utwest org.

    2024-12_0298

    In the lower left corner, there is this, which is somewhat hidden. As you can see, it’s next to the bottom projects in my org, just above my Windows bar.

    2024-12_0299

    If I click this, I have lots of settings. I have lots of different things. One would think this would be a place where I could create a PAT for my agent to connect with.

    2024-12_0300

    It’s not.

    If I am setting up an Agent and need a PAT, I need to do to the upper right, under my user settings. This is the second from the right (as of Jan 2025) icon. If I click this, I see PATs.

    2024-12_0301

    Once I click Personal access tokens, I get to this screen. You can see my PATs and a “New” buton in the upper right. Click this to create a new token.

    2024-12_0302

    You get this screen. Apparently this token is only used to register the agent. I’ve usually granted this full access, but I haven’t dived too deep into what happens if I only limit rights. I’ll try that soon. As with any security issue, make sure you know the implications of settings.

    2024-12_0303

    Once you create this, you get a notice that you need to copy this now or lose it. There’s a handy copy button, so use it.

    2024-12_0304

    Summary

    This is a quick post on creating tokens in Azure DevOps. As with any security change, make sure you know what the implications are for creating and using these.

  • Insiders Selling Data

    There is a report that the lowest-paid employees at various banks are selling customer data, which is sad and also not unexpected. Those who have access to sensitive information can be tempted to part with it for a quick profit. I’ve seen this in the past in restaurants and retail stores. Sometimes with credit card or other data, but also with knowledge (or keys) that would let someone burglarize the premises.

    There’s a great quote in the article that says: “The more employees there are inside a company with access to sensitive customer information, the higher the risk that access is going to be abused.” I think that’s very true in most companies, which is why governance and data controls are becoming more important in some organizations. I wish it were a global trend at all companies, but far too many don’t think there is a high risk, or that their employees will abuse their privileges.

    I’d like to think most IT people wouldn’t be tempted to sell or disclose info, but I’m not sure that’s true. While many IT staffers are paid well, well is relative. Some might not think they’re compensated enough or they might feel pressure from their own financial stresses to compromise that data of others.

    Privileged access is just that: privileged. While it can be a pain to have a separate account for certain access, the intention is that you are reminded that this account can access things that others should not see. In my career, I’ve tried to remember this and be careful with my access, though admittedly, I’ve done plenty of non-privileged things with a privileged account, being lucky enough that nothing bad happened.

    Security continues to be a problem in so many places, and with the vast amounts of data we collect, and the growing desire for many organizations to let AI LLMs view that data, I suspect we’ll likely have more problems in the future not less. While we might not have as many humans that decide to sell our data, I suspect we’ll have many more AIs that can be tricked into disclosing it.

    Steve Jones