Voice of the DBA

Tag: security

  • More Supply Chain Attacks

    The latest code supply chain attack isn’t a direct attack, but a failure of a system designed to be efficient. There is a Go (Go-lang) module that had a malicious module inserted into it years ago. Someone caught this and removed the module from GitHub, but Google had cached this and has kept it alive for the last three years.

    This attack worked because it relied on the Go Module Proxy services, which prioritized caching. Even when the source changed, the cache wasn’t invalidated or reloaded, which seems like a major ovesight or even larger design flaw, but I know everyone trying to maintain software archives at scale tends to cache a lot. After all, so many developers might load modules in their daily work, in CI, etc. that caching matters.

    However, the bigger issue is that criminals are getting more enterprising and aiming for supply chain attacks where possible. One would hope that any PRs (pull requests) are carefully examined, but the truth is that a lot of people might depend on their unit tests passing in a CI build. They are not necessarily looking to see if anyone has actually entered malicious code or even poorly written code. I can see some people worried more about code structure and naming (or tabs/spaces) than what the code does. Imagine seeing a change that looks innocuous at the top of a file, all tests pass, and you merge the change, but didn’t notice there was a few new functions added below in the file because they’re not obvious in the UI.

    Someone maintaining a popular code repo as a side project might be fooled here, but if this were in a corporate repository we might be even more susceptible. We have more reasons to trust the PR isn’t a problem if the code passes tests inside of an organization. After all, who things criminals might insert code into their corporate repo? Not many people do, but we’ve seen quite a few successful supply chain attacks in the past. Who knows how many more we don’t know about.

    Security is a hard business, and when it’s extended to the code we write, it might even be harder. I know there are security scanning solutions you can integrate into your codebase, but those detect what they know about and criminals keep finding new ways to attack us. Ultimately I think we depend on code maintainers carefully examining PRs from outside their circle of trusted individuals, and even then, things can slip through.

    Some days I think it’s truly a mad, mad world.

    Steve Jones

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

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

  • Lower Your Attack Surface Area

    It’s no surprise that our systems are under attack by all sorts of criminals. Some organized, some opportunistic, some just aiming for vandalism. We need to protect our digital systems to prevent issues, and a part of better protection is reducing the number of places that are vulnerable. Those places include databases.

    This article discusses the rising costs of data breaches and the increased frequency of attacks. It also examines the increasing number of regulations that are demanding proof of stricter security measures. It can be hard enough to defend production systems, let alone protecting dev/test environments. I see an increasing number of organizations that limit access to production systems, even to the point that this impedes some of the daily work habits of technology professionals, but that is probably a good thing. Too many of us are too lax when it comes to security.

    There are lots of approaches to getting better at security, but one of the easier ones is to avoid making copies of sensitive data. About half of you (I hope it’s not more) still use production restores in dev/test environments. That’s a tripling of the places your data could be attacked if you restore a database to a development server and a test server. If could be even worse if you make more copies.

    An easy solution in today’s world is to build a better test data management process for either anonymizing and obfuscating your sensitive data or generating synthetic data. Both have their challenges and I suspect that most organizations need a combination of both approaches to both protect their data as well as build better software for their customers. After all, a huge amount of bugs are data related, where developers have not tested their software against enough different data elements. Both synth data and anonymized data help here to produce enough different edge cases that your testing is thorough enough to increase quality.

    Of course, you need extensive testing, which means automation. Ideally an automated DevOps flow that subjects your software to increasingly complex tests as it moves through your pipeline to ensure it’s ready for release. This also means a good set of test data, not only for QA, but for automated tests. You need a test data management strategy.

    Securing digital systems is a complex task, but we ought to try and make it easier on ourselves by developing good habits in how we manage both access and by limiting the copies of sensitive data.

    Steve Jones

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

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

  • 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.