Voice of the DBA

Tag: security

  • The Challenge of Edge Security

    We know that our organizations will adopt and use more devices over time. Given the growth of cheap computing, frameworks for managing devices, and the desire for more data, I expect some of those devices will collect data, or even contain databases. Azure SQL Edge use is growing, and we will see more devices that contain it (or another database platform), which means we have a larger attack surface area for that data.

    There was a recent report on a vulnerability in edge devices used by AT&T that was detected as part of an attack. The attack used a known vulnerability based on default credentials. The vulnerability was fixed, but the patch required manual work. From various reports, it is unclear whether devices have been patched. It’s also unclear if customer data was accessed. Here is one such report, but there are others, all with similar information.

    When developers build something, whether a device or just software, we often set up easy ways for us to access the system to test features and functionality. Certainly when software is deployed to users, there is often a default credential that is supplied. I don’t know if this is good or bad, and if the management of random credentials for each device might result in better or worse security. Strong passwords might lull customers into feeling that they don’t need to change anything.

    I do think that the installation of any software ought to require a strong password. Once one is entered, and defaults ought to be permanently removed or changed. Leaving around defaults for maintenance or ease of updates is a sure way to get hacked. If we’ve learned anything in the age of computing it ought to be that anything you deploy in the wild will be taken apart and analyzed by someone. Hard-coded values or default accounts will become known.

    The bigger problem might be that patching is still a problem and even more of a problem when it’s not easy. I know that the SQL Server update system is fairly easy, but not dead simple. Many people still don’t apply patches. Heck, even when updates are built into something like Windows, people try to avoid patching their systems.

    For those of us that work with databases, we may or may not control the update process. We can, however, ensure that those that do are aware of when patches are available, how far behind the system is, and where to get the patch. That information, and a little pressure, will become increasingly important as we deploy and work with data on more edge devices.

    Steve Jones

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

  • No-so-smart Contracts

    Perhaps the best quote I’ve seen in a long time: “These kinds of attacks are common in smart contracts because many developers do not put in the legwork to define security properties for their code…” I’m sure that this would apply to many kinds of software, not just smart contracts.

    This is from an article on a hacker that stole money by altering a smart contract. In this case, tokens used to replace parts of the contract overwrote other tokens, which allowed a smart hacker to change prices and make more money. Or steal it, with a contract change, I don’t know that theft is actually the correct term.

    The wider issue here is poor developer practices, and really, not listening to the results of security audits and making changes in code. Maybe they listened to the audits and hadn’t completed the work. There were some critical issues, and some remediation, but not enough in this case.

    Building security into software is hard. The threat landscape changes and hackers are incredibly creative. It is hard for developers to keep up, but it is important, especially where there are finances involved. There are tools to perform security assessments and automated pen-testing. Everyone ought to use these, and more importantly, management should take security more seriously. If they don’t, they deserve some sort of penalty.

    The problem for many of us is that we can raise issues, but we are powerless to do anything. We can change jobs, but that’s not practical all the time. We can continue to raise awareness, but that can be detrimental to our careers. After all, management will get tired of us repeating ourselves at some point.

    Mostly what we get to do is worry. We worry that the company will get penalized, which can affect our employment. We can worry that management will blame us for an issue they didn’t allow us to fix or give us the tools to detect. We can worry management will blame us for not knowing about an issue as well.

    I believe we ought to have more focus on security, but I’m not sure what that means or how to achieve this in a practical sense. I don’t even know how we’re set up regulations and penalties for such a complex situation.

    Mostly I’m just sad for the state of software security.

    Steve Jones

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

  • Getting Beyond Passwords

    Most of us that work with SQL Server likely use either the Windows authentication or a user name and password when connecting to an instance in SSMS or ADS. It’s how we’ve operated for years, and likely will for some time to come. If you connect to Azure cloud resources, perhaps you use some multi-factor authentication (MFA), but that’s a minority of us.

    If this article is a picture of the real world, far too few people are using authentication beyond passwords for many services. While plenty are using fingerprints, patterns, or face recognition on a mobile device, that’s usually the extent to which they actually go beyond a password. I’ve actually started to see people using PINs on laptops instead of a password, which feels like a step backward.

    Recently I saw someone suggest MFA for SQL Server. I would hope that we would get not only more complex authentication for the platform, perhaps even two-person authentication. but I’m not holding out hope. I think the integration with AD is likely to require more steps than most administrators want to take. For now, I expect that any sort of on-premises SQL Server security is going to remain the same. For cloud databases, I do think that we will see other options as they become available.

    I personally don’t think we’ll ever get beyond all passwords. There are just too many situations where someone might not have a smart device they can access. Too many unlinked services and organizations that might not want to authenticate to GitHub, Google, Facebook, or any other large service. I certainly can’t see email moving beyond passwords entirely. We might get a login with some other service, but a password will still be a last resort.

    While I’ve gotten comfortable with quite a few different authentication mechanisms on a daily basis, I do think that the entire structure is still complex. While I often authenticate with some sort of MFA, it’s a mix of copy/pasting codes or pressing authorize buttons. That’s if I actually remember which service I used to authenticate to a particular service.

    Ultimately, I find unlocking a safe and copy/pasting passwords to be the simplest method, and I find myself often choosing to create accounts with email and passwords. Easier than me trying to track where I might have used Google v Microsoft for authentication.

    Steve Jones

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

  • Row-Level Security Basics–#SQLNewBlogger

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

    I realized recently that I hadn’t really blogged about Row-Level Security, so this post covers some of the things I know at a high level.

    What is Row-Level Security?

    This was a feature added to SQL Server in SQL Server 2016 that makes it easy to grant access to rows of data based on some characteristic of a user. At a high level, this means:

    • I have something that segregates rows of data, like a CustomerID as a column in an Orders table.
    • I want a customer to only be able to view their orders, those associated with their customer ID.
    • This has to work, even if they didn’t use a WHERE clause and did a SELECT *.
    • In this case, a user for CustomerID 4 would only see Orders that had CustomerID=4 in those rows.

    We used to be able to do this with views, but this was cumbersome, and it was obfuscation. There was no security mechanism that actually ensured a user logged in wouldn’t see other rows.

    Row-Level Security

    This was a first class security mechanism that uses security policies and functions to control access. The way this works is as follows.

    We create a function that is a table-valued function which takes a parameter(s) from a column(s) and returns a 1 if the user should view a row. In this case, we would use a WHERE clause in the query in the function that looks for Orders.CustomerID = @CustomerID.

    We bind this function in a security policy that binds the function to the table, and specifies the column (or columns) used as parameters to the function. We also specify the predicate involved. There are two types:

    • Filter predicates – limit read access
    • Blog predicates – limit write (insert/update/delete) access

    We give permissions to the function to users.

    Does it Work?

    Yes. It works very well from a security standpoint. Since we are tying this to users or logins, the performance of determining if the user or login has access can be slow. The IS_ROLEMEMBER() and similar functions are not super efficient and you can have performance issues across millions of rows.

    However, it works.

    I’ll write more in the future on the details.

    SQL New Blogger

    I was watching a presentation recently on this topic. I’ve written about this for SQL Server Central, but when I checked, I hadn’t really done much blogging on it.

    Here I’m re-using knowledge, but in a basic way. I took 15 minutes to write a high level description. I’ll do a few more posts that demo setting this up for reads, one for writes, maybe one to get around how this might have a hole for security purposes. At least 3 more posts.

    You could learn this and blog 3-4 times about what you learn and how to set up it up situations.