Voice of the DBA

Tag: security

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

  • Maybe It Is DNS

    There are jokes about how often DNS is a problem. There are stickers, shirts, and mugs you can buy. There’s a classic haiku, which leads into a rant about the struggles of sysadmins. I will admit that I know a little about DNS, but I find myself confused at times and usually seek assistance from others when something doesn’t work. The old days of a single MX record and a few A records to map names to IPs has grown into a much more complex system.

    DNS usually works. In fact, it’s lower on my list of things to check these days, though I do often get a quick DNS check when I ping an address. There are some failures, and these days when large companies have issues, it can affect a lot of people. Akamai had an issue and knocked off a large chunk of the Internet. When Facebook had an issue recently, it not only affected customers, but the engineers that were trying to fix things. Some couldn’t even get into buildings. I guess relying on a single domain might be a bad idea.

    Most of us rely on DNS in our organizations, which means that a rising level of DNS based attacks is disconcerting. These are still behind the issues we have with ransomware and DDoS attacks, but when DNS doesn’t work, a lot of stuff shuts down. I could see a lot of people that work inside a company’s domain for most of their work being unable to accomplish anything.

    I tend to use a number of domains for my daily work. Evernote and Dropbox are essential, but if one were down, I could still do things with local copies. Red-gate.com is certainly important, but as long as SQL Server Central is up, I can do something different. Actually, if both were down, I could still write or create questions. I’m more bothered by slowness in my connection or machine than losing any particular service.

    I haven’t every been too worried about DNS security, but I left sysadmin work when it was a simpler time. These days, I suspect many organizations might want to be sure they are paying attention. If they use a lot of services connected to one domain, I might think about having a secondary domain connected to a few. If I were in charge, a sqlservercentraladmin.com might be one I’d set up.

    Steve Jones

  • How Often Do You Patch?

    I saw two things recently. First, CU25 for SQL Server 2017 was released. I realized that I was CU8 locally, so I had work to do. Second, I noticed that SQL Server 2016 fell out of mainstream support on 13 Jul, 2021. Amazing that it’s been around for 5 years already and has moved into Extended support. We’ll still get security updates, but nothing will be fixed from that version, which it seems many people are running.

    Microsoft also announced they will put out a final Service Pack for SQL Server 2016. I am glad to see that, as I’ve often wanted to know there is a final patch that is released for each version, as I can know when I’m done patching.

    Today I’m wondering if you track this at all. Do you actively look to ensure your instances are patched? Are you on a schedule of some sort, maybe matching Microsoft’s every-other-month pace, or perhaps just a few times a year? Or is this more ad hoc?  When I go visit companies, I often find instances at all different levels, often without any reasoning as to why systems aren’t consistently patched.

    I used to try and ensure we patched every quarter, though certainly, I faced resistance from some business owners of systems over the worry that a patch might break something. Valid concerns back then, and still somewhat valid today, though if a patch gets through a few months without reports of issues, it’s probably safe for most systems. Still, make sure you test, especially for business critical workloads.

    These days, with so many hacking attempts, ransomware distribution channels, and no end to phishing, it makes sense to stay on top of patches and make sure you are up to date or at least planning to do so. Some of the high-profile hacks, like the Equifax one, have occurred on systems where patches were available but not applied. I don’t know that many of the patches are closing holes in SQL Server, but there are definitely issues with Windows where you would want to ensure your host OS was patched. Maybe that’s something you ought to check on today and ensure you have a plan to apply those updates.

    Steve Jones

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