Voice of the DBA

Tag: data classification

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

  • Data Classification in SQL Server 2019

    One of the areas that Redgate is working on is making data classification easier. Microsoft added some capabilities to SSMS 17.5 and Redgate has an EAP out for the next version of our data catalog tool.

    Azure SQL Database has had some advanced options they were building into the database engine, and we get our first look in the on-premises version with SQL Server 2019 CTP 2.0.

    The ADD SENSITIVITY CLASSIFICATION and DROP SENSITIVITY CLASSIFICATION DDL is now available, and here are few examples of how this works.

    Let’s look at a database that has some potential data to classify. I’ve got a sample database with a few tables. In fact, if I look at the data classification suggestions in SSMS, I see 7 columns.

    2018-10-23 11_56_48-Data Classification - SimpleTalkDev_Steve - Microsoft SQL Server Management Stud

    I can accept any of these, but if I do, these are written to extended properties, which isn’t the best way of storing this data.

    However, the ADD SENSITIVITY CLASSIFICATION syntax works well. If I take that dbo.Contacts.Email column and decide this is Confidential according to the GDPR, I can do this:

    ADD SENSITIVITY CLASSIFICATION TO
      dbo.Contacts.Email
      WITH (LABEL = 'Confidential - GDPR')

    If I then query my meta data table, I’ll see this:

    2018-10-23 12_01_04-SQLQuery4.sql - Plato_SQL2019.SimpleTalkDev_Steve (PLATO_Steve (61))_ - Microsof

    There are other items I can add, such as the information type and then IDs for the label and type. I can, however, update that data like this:

    ADD SENSITIVITY CLASSIFICATION TO
       dbo.Contacts.Email
       WITH (INFORMATION_TYPE = 'Contact', INFORMATION_TYPE_ID = '5BFAE3B8-4549-4989-BEB6-F9BF6434DAD1')

    Note I still haven’t given the Label_ID a value, but that’s OK. This allows me to add human readable metadata to columns, as well as add IDs that I might get from some external auditing system.

    2018-10-23 12_04_01-SQLQuery4.sql - Plato_SQL2019.SimpleTalkDev_Steve (PLATO_Steve (61))_ - Microsof

    This feels primitive, but it’s slightly better than extended properties, and it’s somewhat built into the engine, so we can code this as a part of development and ensure classification is added to our sensitive data.

    If this is an area you’re interested in, we’d love to have you try the Redgate tool and give us feedback. We’re working on this problem and trying to find ways that are both useful and sustainable over time.

  • The Uninteresting But Necessary Work

    There has been a rising tide of data legislation in the last few years that asks for organizations, especially private companies, to better protect their data. The GDPR is one of the most well known, taking effect with regards to enforcement earlier this year, and I’ve been doing quite a bit of work in relation to this law. There are plenty of other laws, such as California’s CCPA, Australia’s NBD, Japan’s APPI, and more that we ought to be aware of as data professionals. These laws affect personal data about people in a variety of ways, and they can affect how we process and use portions of the data we store.

    It’s not as simple as it might sound to change our data handling practices. In fact, it might not be that easy for many of us to do this now unless we’ve actually done something in advance: we need to have classified our data. We need to understand the impact of the various columns in our tables, the exports of flat files or reports, and even the development processes that make copies of our production databases.

    I’ll be honest, classification work is mind-numbingly boring and uninteresting. This almost feels like busy work to me, especially once we get past the obvious tax IDs and birthdays of people. When we start examining other data, the task feels like it ought to be delegated to junior staff, but many of them lack the experience to make the decisions. What can be more frustrating is that most of them lack the status to get others in the organization to respond to questions, which means the task ultimately falls on more senior people. This also means they often do it once and then forget it, leading to out of date information.

    We don’t like doing classification, but we need to do it. Without having some mechanism that allows us to determine if data can be moved or used in another system/database/report/etc., we end up just ping-ponging around. We assume all data is sensitive and try to lock it all down. That leads to complaints, as well as staff working to circumvent the rules until they appear meaningless. At this point we might give up on controlling data and just trust people. That leads to audit problems, potential data loss from security incidents, and plenty of embarrassment about why we didn’t implement some simple controls.

    Then the cycle starts again.

    Classifying data is simple in some ways, but not easy to ensure the data is available, up to date, and easy to find for any size team. I’ve seen simple solutions that rely on spreadsheets. I’ve seen complex software packages that are expensive and cumbersome to implement with other applications. Microsoft has started to help with a few changes in SSMS, but this doesn’t seem like a long term solution, though SQL Server 2019 might help. Redgate has spent some time on this as well, thinking about the issue and we have an early access program now.  All of these are partial solutions that might work for some organizations, but not all.

    Ultimately, this is something like security, that we ought to be building into our systems from day one. Every proof-of-concept or prototype ought to be classifying data from the beginning. We won’t be perfect, and won’t get every label correct, but if we’re always thinking about the data, we can always correct our label and more tightly or loosely decide to handle data. I’d also like to think that if we conservatively label the data early, we’re unlikely to get into positions where we are mishandling data in a way that makes it more likely that we accidentally lose data.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 6.0MB) podcast or subscribe to the feed at iTunes and Libsyn.