Voice of the DBA

Tag: security

  • The Need for DevSecOps

    One of the things that happens with many companies that start adopting DevOps is that they release new features constantly. They publish their lists of changes, and they try to attract customers and grow their businesses. They may make some mistakes, but they fix those quickly and keep pushing forward. That’s the idea, and it works well.

    However, many of the developers (and most managers), don’t think about the security side of their changes. This piece looks at the way hackers and criminals view DevOps, often using release notes and feature changes as a target to focus their efforts. In this way, they exploit holes and vulnerabilities in software to attack data storage. The examples include S3 buckets of storage and Elasticsearch, which is notoriously poorly secured by many people.

    I’m sure there are hacks that also expose relational data stores and NoSQL stores, but those are often more secure and harder to directly attack. Certainly, hackers do get credentials and can query data, it’s more often I hear about data breaches from other sources than direct relational database access. SQL Injection is definitely still an issue, and I hope that more and more developers are learning patterns that avoid these vulnerabilities.

    DevOps works. I think it’s great. However, it’s not enough to trust developers to build features without including static code analysis, pen testing, and other security evaluations before you release code. Often developers can build features just as fast with good patterns as bad. Use automation to catch bad patterns and force developers to learn new ones.

    Also, avoid letting developers implement data stores out of convenience and speed. Ensure that strong security practices, long passwords, service accounts, secrets, and more are implemented from the start. It’s easy to shortcut these, but it’s also harder to explain to customers and investors why we didn’t do better.

    If you’re a developer, the main thing to keep in mind is that you will often be the scapegoat for these issues. Upper management might not support you and pressure you to move faster, but when there are issues, they’ll also be quick to blame you and let you go first. Push back and ensure that you have the tools and the process to evaluate if you are building problematic code. Always use long passwords, and document what you do for others to follow.

    And if your boss insists on cutting corners, get that in writing. It might not save your job, but that documentation has served me well in the past when issues come to light.

    Steve Jones

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

  • Rogue Colleagues

    The economy might be good or bad for you right now. Some of that depends on where you live, what your employment situation is like, what your habits dictate about how you live life, and more. No matter what your situation, likely there are people around you that complain about the world and others who think things are fine. There are likely more of the former than the latter, but that’s because humans tend to complain out loud more than they praise.

    When people think there is an economic downtown for themselves, they may be more likely to engage in malicious activities. While I don’t think most data professionals will start to hack other systems, or even their own employer’s systems, there is evidence to support the idea that some might be susceptible to recruitment by bad actors. This piece references some research and warns security groups to be wary.

    There is no shortage of books, or television and movie scripts that might show creative ways to access information, but how can you tell if a colleague makes a simple mistake or they are a bad actor? Clicking on a phishing email could be either one. Not removing anonymous access to an S3 bucket could be either. Losing their credentials through social engineering is something that happens every day. Who’s to say that this happened purposefully?

    I don’t want to second guess the people I work with making mistakes, but I also think these possibilities are why we want to use our computer systems with strong auditing and multiple groups reviewing logs. We might not necessarily stop all activity, but we can often detect it quickly and mitigate the issues. It’s also why DevOps and automated deployments with logging are a good idea. They can limit the problems from both accidents and malicious actors.

    My employer has started to do more education around security and how individuals can avoid accidentally causing issues. We use a lot of automation, and more all the time, that ensures once we know how we ought to patch and update systems, we can do it regularly and confidently. Repeatable, reliable deployments of changes are what we aim for.

    We know they’ll be some mistakes, but we also know that we can quickly identify issues (MTTD) and fix them (MTTR). Even if we get a bad patch from a vendor, we can quickly deploy a “fix” if we get one, or even reinstall and re-patch to lower levels, if needed.

    DevOps, GitOps, and other xxOps aren’t just about getting new features out quickly. They also include the ability to fix problems when the need arises. They don’t prevent rogue actors from causing issues, but they should help you detect and recover quicker than you might expect.

    Steve Jones

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

  • Removing Weak Security from SQL Server

    I was checking some arguments in the RESTORE command for SQL Server and saw that the MEDIAPASSWORD option was deprecated and marked as being removed at some point. That made sense, and I assumed that PASSWORD was the option to be used moving forward. However, that option is also marked as deprecated.

    What should we do?

    Well, we need to better secure our backup files for sure. Disk encryption and limits on AD/directory permissions ought to be set. Of course, we need to use care when handling or moving these files, especially when they cross the secure boundary in production systems to other dev/test/etc. networks.

    The security section of the document explains the reasoning here. The protection provided is weak and isn’t intended to protect data. You can still read data in the backup file. This is mainly to prevent an incorrect restore when using tools, meaning the human picking the wrong file. This isn’t to protect your data.

    I suspect most people dealing with SQL Server backups that use either of these options don’t know this. They think the password secures their data. I know because I’ve seen people use this to send a backup file through email or file transfer to another party. However, if you’ve ever opened a backup file in XVI32 or another editor, you will see that your data is in plain text. If you’ve never done that, give it a try today and search for strings that you know are stored in the database.

    Some security is better than no security and layers of security that build on each other are useful. However, depending on weak security isn’t good. It leads people to count on something that doesn’t work and ignore more serious issues.

    I’m glad that Microsoft is (supposedly) removing these options. I understand that backwards compatibility and preventing existing scripts from failing are important. At the same time, we need to move forward. I’d like these password items to become a no-op, and not cause errors, but I would hope that their use in scripts would also generate a message to the user that these options don’t work and need to remove removed. Perhaps with a direct message and a note in the error log, we’d start to see people embracing other security practices that will provide more protection.

    Steve Jones

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

  • Which Schema is Queried First?–#SQLNewBlogger

    I had to test something for a customer, and as a part of this there as a need to have a different default schema for a user. I wrote about that, but one of the things that occurred to me is the need to know how security works with schemas and the priority of queries.

    The Scenario

    A user in a database needed to access certain objects, which were going to be located in a separate schema. The previous post looked at the setup of the user, but what happens with queries. Let’s set up a few tables that will help us learn.

    I’ll create two tables, one in each schema.

    CREATE TABLE dbo.Location (locationname VARCHAR(20))
GO
INSERT dbo.Location (locationname) VALUES ('dbo schema')
GO
CREATE TABLE webapi.Location (locationname VARCHAR(20))
GO
INSERT webapi.Location (locationname) VALUES ('webapi schema')
GO

    Let’s see what happens when we query the tables. If I write a query that qualified objects, I get what expected. I’ll login as APIUser and then run this code.

    SELECT * FROM dbo.location
SELECT * FROM WebAPI.location

    When I do this, I get the results expected.

    2023-06-27 09_48_54-SQLQuery3.sql - ARISTOTLE.test (ApiUser (56))_ - Microsoft SQL Server Management

    If I don’t qualify the table, do you know what happens? You should, and this post shows the results that the default schema is queried first.

    2023-06-27 09_50_13-SQLQuery3.sql - ARISTOTLE.test (ApiUser (56))_ - Microsoft SQL Server Management

    If I didn’t have a WebAPI.Location table, the dbo.location result would be shown. It is important that you understand SQL Server security if you manage instances or write queries, as you could otherwise feel you need to grant far too many permissions.

    SQL New Blogger

    This was a minor part of something else I was doing. I noted this in the previous post, but also realized the ALTER was a good second post and verifying the permission hierarchy was a good third post. I could have added this to the first one, but I like separating and focusing posts. Better for SEO if you care, but better for your workload and producing most posts.

    Outside of the work I was doing, the sketch of these notes took about 2 minutes, and then the entire post was a little longer, with more code to write. This was about a 15 minute post.

    You can do this.