Voice of the DBA

Tag: security

  • No Backdoors

    Every once in awhile I hear about someone in law enforcement sure that tech people can build in a safe, secure way for data to be unencrypted by the company or vendor. The latest appears to be from Australia, where the Security Intelligence Organization wants tech companies to build this into products.

    Backdoors never work. Anytime an encryption key is stored, it could be stolen. We see this all the time. Keys are just data, and companies lose data all the time. At scale. Governments are certainly not immune from this. One of the reasons that Azure allows a BYOK (bring your own key) for encryption mechanisms is that many organizations don’t want to trust Microsoft to store their keys. I’m guessing Microsoft doesn’t want the liability, either.

    Many of the data protections an organization might implement are outside the scope of data professionals, but we certainly have responsibilities in this area. We should certainly manage our keys appropriately, and my recommendation for many people is that they have a separate repo and pipeline for the deployment of privileged objects, such as encryption keys. Your organization also ought to have a process (and test it) for key rotation. Keys expire relatively infrequently and the person who last rotated the key might not be available when you realize this process needs to be completed.

    It’s especially important everyone knows how this works for those emergency situations where something expires and none of you realize there’s a problem until a client calls.

    There are plenty of other security mechanisms we ought to be using. Secure your backup files and limit access to shares or even processes that move these files around. In general, limit access to those who need access to everything. Using organizational groups is the best way to do this, along with regular audits to ensure that those who change jobs are removed from groups that aren’t needed. I haven’t seen any organization that has a good process for knowing what positions need what access. I often only see people given new access for a role change, giving them the new roles that match some other employee. This is usually done without any previous access removed when it is no longer needed. The most senior people often have the most access, not because they need it, but because they keep getting new roles.

    Managing security with roles too granularly can become a nightmare, though ensure you use roles everywhere you can while trying to limit the numbers of roles. For most databases, we are giving access to nothing or everything, but there are reasons to limit access for certain data. You might consider two roles by default in most places: one for privileged users and another for everyone.  Easy to ensure new objects get grants to one or both roles and let access be managed by role membership.

    Keep it simple, but keep it secure.

    Steve Jones

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

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

  • Analyzing Breached Data

    A few of you out there might be data scientists who profile data regularly. Probably a fair number of you do import/export work and learn to check data values, perhaps with counts, distincts, or other aggregates. I don’t know if the performance tuners out there look at the skew of data or the details of what is in a query that needs improvement. However, all of you are likely familiar with data and trying to query it for some type of meaning.

    One of the largest data breaches occurred with National Public Data. Troy Hunt analyzed the breach as a part of his work with haveIbeenpwned. The piece is an interesting analysis of the data, trying to determine both it’s legitimacy as well as what is actually included in the breach. It’s a fascinating read and I encourage you to look at it not just from the data analysis side, but also to be aware of what data about you is being aggregated and sold by companies.

    The read is interesting as it is a bit of a detective story, digging through data in a folder, which is something I’ve had to do. I’ve had people in previous jobs just dump a bunch of data on me and ask me to load it into a database. Or a table. Often without them knowing what type of data it is, what formats, do files relate to each other? Are there multiple tables worth of data in a file? All questions I’ve had to ask myself (and answer), and similar to what Troy did to analyze the breach.

    Data is very important to many of us, in different ways, but I’m often amazed at how few people actually understand how to organize data and ensure others can track the metadata about their data (what their data represents). I’m guessing this is why every person that gets an extract of data to load into Excel formats it in different ways.

    In many cases, people want the ability to query data, but they prefer to just focus on one table that contains a lot of information. They don’t want to know how to “join” data together. I think this might be the reason we see so many views in databases, and why we have views built on views. Each new client of the database needs their own view structure.

    The world of data is a mess, even inside an organization. Once we start moving data between organizations, it’s truly a mess. We might bemoan all the inefficiencies and work we do to move, change, and re-load data as custom, human ETL machines, but there is one great thing about this tangled web. It provides for steady, secure jobs for many of us with no end of work in sight.

    Steve Jones

  • Prevent SQL Injection

    I would hope most of you reading this know what SQL Injection (SQi) is and how you can prevent it. Or at least what patterns cause problems. If not, here’s a short explanation that is worth reading. If you have more questions, ask in our forums.

    SQL Injection has been, and continues to be, a problem in many systems. In fact, I chatted with Mike Walsh recently after he’d published this post on an attack for one of his clients. He has some notes that explain how your database server might be vulnerable, as well as a description of a recent attack example. He also notes that many of you are responsible for protecting data, which is separate from other security mechanisms. You need to be sure you are protecting your data, even in vendor applications.

    I’ve seen similar issues in the past, both in homegrown and purchased applications, where text fields aren’t checked and SQL is built by concatenating user input with code. I’ve complained to vendors, though often a short repro helps them see the problem and I’ve found many companies will patch systems, albeit sometimes slowly.

    There are application firewalls that can help, and certainly limiting access to those users who need access is always good, but that’s not helpful when the application is something that many clients use.

    The best protection is education. If you don’t know what to do, or your developers don’t listen to you, perhaps engaging a consultant like Mike will help. I’m amazed at how often people listen to an outsider when they ignore the same advice from someone they work with. That might be especially true for managers who are more concerned with doing more new work rather than fixing something that’s not quite working well.

    Security is becoming a bigger issue in many organizations. Not because we might get fined, but often because our customers might decide to choose another service if we can’t protect their data. There are other choices these days for most of the services we provide, and many organizations are finding customers increasingly fickle and quick to leave. This might not be the case in business-to-business work, but it does happen.

    We often won’t be perfect in our security and even if we are, our systems will change and new vulnerabilities or attack vectors will appear. We can work on the problems we know and improve security over time. SQL Injection is fairly simple to prevent, but it takes some education, some practice, and some code review.

    All things good database professionals should be doing.

    Steve Jones

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

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

  • Least Privilege

    I assume that most of you know about the principle of least privilege. If not, please read this short blog from Brian Kelley and make sure you understand how you should approach security. In the modern world, we also ought to adapt our systems for the zero trust model, which includes the least privilege principle.

    However, I wonder how many of your organizations really follow these security guidelines internally. Are you strict about adding limited access and removing it when people change jobs/roles? If you use Windows Auth (or Entra), are your admins doing that or just adding in new roles? Do you scope down database access roles in granular ways or just stick with 1-2 roles for the most common things people do?

    Maybe more importantly, do you use roles or are these systems that still have explicit grants for users?

    Microsoft had a major hack recently from a test account that had administrative privileges. While there certainly might be a need for a test account to have privileged access, I’d hope that any test account created had a limited lifetime. I’ve created privileged database access accounts for vendors, but usually set a reminder to myself to disable the account after xx days. When I got smarter, I wrote a one-time job to do that and scheduled it. These days, I’d also file a ticket for my team noting that this needs disabling as well.

    Humans get lazy and often don’t think about the future. If you’ve never had an issue with a test account, why think something might happen? Why spend the time writing a note or a job when surely you’ll remember or deal with it later? Maybe more common, why disable a login when the user might need access longer? We don’t want to deal with another phone call and enabling the account. That’s an interruption to our work week.

    What has been humorous to me is that I’ve seen quite a few people who are very security conscious get annoyed when some automated system or process disables their account and forces them to make a call.

    It is annoying. However, these little things, the details, the adherence to good practices are what help ensure we have better security. When we take shortcuts (like not enabling MFA), when we skip steps, when we do small favors for others, we’re increasing risk. Most of the time that’s fine.

    Once in awhile it really comes back to cause problems. I’m not sure the savings are worth it.

    Steve Jones

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

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