Voice of the DBA

Tag: security

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

  • Security Bug or Handy Feature

    There are plenty of times that I want to share something with another person. This could be a link, a slice of data, or maybe the view of some page I’ve seen. Many applications make this easy, either on the Internet, or inside a company in Slack or Teams.

    However, there are lots of times that I might want to share some data publicly, allowing anyone to get to it. That’s a place where no shortage of data breaches have taken place, a problem we still grapple with today. It’s not a “make-a-decision-once” problem either. I could set up some data for access to specific people using permissions. Then someone later changes to anonymous public access, mistaking some dataset that is sensitive for one that can be exposed. Likewise, I could have a dataset that I have set to be publicly available change, with sensitive data added later. The individuals adding the data might not be aware that this particular set is being shared without any controls.

    I ran across this piece about PowerApps, noting that datasets can be configured for anonymous access if a list doesn’t have table permissions enabled. That’s an issue, as often enabling something looks like work. Many people often seek to avoid work and just complete a task as quickly and easily as possible. In many cases, they may not bother with enabling table permissions. Fortunately Microsoft later enabled permissions by default, but there are still cases of data owners exposing sensitive data.

    Misconfiguring access is a big problem overall, and the best solution I see is to ensure data is classified and tagged. We can then build applications that have policies set on the way they handle data. If data were classified as PII or sensitive, then an app (like Power Apps), could refuse to allow anonymous access to be configured. Classifying data, however, is a tedious, boring, awful job. Even if this is easy to do across time, it’s not a job anyone wants. However, this is part of the data lifecycle, and I don’t know that we will get better data security and limit the exposure of data until we have a way to easily classify and tag data that allows applications to make decisions on how to use this data.

    My employer makes a product to help here, and I’ve been pleasantly surprised that more and more customers are looking at doing this. However, we then need to ensure that applications can use these classifications  in an actionable way to protect data.

    Microsoft has talked about enhancing that the TDS protocol and their software to read and use read classification data, which is lightly gathered in SQL Server. They have a Purview product, which also helps, but the best solution, in my mind, is an open API. This would allow for connections to any classification service. Developers and admins could submit classifications for database structures or even files in real time (and hopefully programmatically). Applications could access this data and then determine what controls should be applied. Ideally, they would also refuse access if data wasn’t classified.

    I don’t know that we’ll get here, but I do see some movement from a variety of vendors here, usually limited to the database space. Hopefully this will continue to grow over time and prevent some of the silly data breaches we have where someone mis-configures a data store and allows anyone to access it.

    Steve Jones

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