Voice of the DBA

Author: way0utwest

Opportunity or Restriction?

I’ve been at the Redgate office all week, broadcasting SQL in the City and meeting with product teams. Quite a bit of our focus is on the GDPR law, with enforcement beginning in May. We’re building and enhancing tools to help you cope. That means I’ve been spending a significant amount of time trying to learn and comprehend the potential impact to data professionals, both to help guide customers, but also to ensure our software will help them ensure compliance in their data systems.

Over the last year I’ve seen lots of doom and gloom, everyone needs to fix their systems and ensure data can be deleted on demand concerns in the media over the GDPR. Ultimately I think there is work to be done for many companies in the EU or those that sell products and services to the EU, but we don’t quite know to what extent we need to change existing applications and databases. Lawyers and solicitors will sort some of this out across the next few years, though I certainly think any data breaches in the next year will be dealt with more harshly by regulators if organizations haven’t made any effort to secure their systems.

There are also some simple things that I think most of us should just do. When I first saw the addition of data classification in SSMS, it seemed fairly trivial. However, the more I’ve thought about it, this simple addition is a way of ensuring that I can easily spend a relatively little amount of time to just think about the information in a database. Just tracking this down can be a pain, and if the information isn’t recorded in an easy to access format, it’s easy to forget what items need our focus. Using Extended Properties is a great idea, as the information is kept with the database, but this means that a better interface than the table properties is needed. There are also a few potential problems doing things this way, but this is a good start to becoming better data stewards.

That’s what we need. A few good starts. We need to see this as an opportunity to clean up practices and move forward in a way that shows us to be professional data professionals that take our responsibility for data security, accuracy, and usage seriously. This is a chance to move forward in a way that reduces our risk of losing data, of becoming the next “headline” corporation or government agency that makes a mistake. Instead, we can embrace this as an opportunity to find new ways of managing our data and extracting information while still complying with data privacy rules.

I read a white paper that talk about the challenges of IoT data in a GDPR world. There are issues to be concerned about, but this also means there will be opportunity for those that devise better data handling methods, that learn to clearly disclose their purpose and practices. I suspect most people realize that there is a certain amount of information that companies collect, and that if there is some value or usefulness that company gives back, we can accept their data storage. Where individuals often become concerned is when organizations move their data to other parties without their consent.

To me, this means it becomes more important for an individual company to understand and process their own data. There will be less movement of data between companies, and perhaps less ability to purchase and import data from others. We’ll need to help our organization extract more value from the data we are allowed to hold, meaning those of us that are data professionals will become more important. At least, that’s what I hope happens.

Steve Jones

March 3, 2018
Republish: Data Decisions or Instinct?

Still in the UK office, so a republish of Data Decisions or Instinct?

March 2, 2018
Data Masker for SQL Server–Syncing Values Across Rows
I’ve been playing with Data Masker for SQL Server v6 and it’s an interesting product. I like the way it works, but I do find it a little challenging sometimes to figure out how to mask values. I’ve written a set of posts for different scenarios.

We had a customer ask about how to mask data across rows. The customer had some data in a table that was a standalone table, and contained data in a column that matched across rows. They wanted this changed, but the matching between rows kept.

In other words, here’s a small mocked set of the original data:
```
myid        Mychar     myint       mytinyint
----------- ---------- ----------- ---------
1           Steve      12345       1
1           Steve      12345       2
2           Andy       12345       3
2           Andy       12345       4
3           Brian      12345       5
3           Brian      12345       6
3           Brian      12345       7
```
Here are the results they want:
```
myid        Mychar     myint       mytinyint
----------- ---------- ----------- ---------
1           aaa      12345       1
1           aaa      12345       2
2           bbb       12345       3
2           bbbb       12345       4
3           ccc      12345       5
3           ccc      12345       6
3           ccc      12345       7
```
I thought this was an interesting scenario, so how do we mask this? It’s not that hard, so let me show you this.

First, let’s create a new masking set. I won’t walk through that here, but once you have a set connected to your database, here’s what we do.

First, we need to substitute data out. In this case, I’ll substitute the name only. In a real world, we’d probably need to substitute the myid and myint columns as well, but I’ll leave those again.

I add a new Substitution rule first.

This is a standard rule. I’ll add my column and pick a dataset. In this case, I’ll just pick make first names (Names, First, Male) and use that. This will result in a random set of names. I’ve chosen unique values. This is important as across a large number of rows, I could end up with random values that match, but with different MyID values. That would be bad.

If I save and run this rule, I’ll see something like this.

Not quite what I need, but it’s a start. The important thing is that the first myid=1 is different from the first myid = 2, which is different from myid=3.

Next we’ll add a Table Internal Sync rule. This is the rule that fixes values across rows inside a table. Here’s the basic config. Note that I choose a table and then I choose the columns that need syncing, in this case just the mychar column.

I need a way to determine which sets of rows should match. In this case, the myid column is used for that. If you examine the initial set, I have the same values for each name. This is what groups things together, so I’ll use this.

One Note: The red “I” to the right means this isn’t an indexed column. If I wanted better performance, I can add an index for this column, either permanently or just for the masking process.

Now I execute this rule, and I see these results:

I have my groups back.

I could expand this to include other columns as well, substituting the myint column in my first rule and including it in the second.

Setup Scripts

Here is the code to set this up:
```
CREATE TABLE MyTestMask
( myid INT
, Mychar VARCHAR(10)
, myint INT
, mytinyint TINYINT PRIMARY KEY
)
GO
INSERT dbo.MyTestMask ( myid,
    Mychar,
    myint,
    mytinyint
)
VALUES
  ( 1, 'Steve', 12345, 1)
, ( 1, 'Steve', 12345, 2)
, ( 2, 'Andy', 12345, 3)
, ( 2, 'Andy', 12345, 4)
, ( 3, 'Brian', 12345, 5)
, ( 3, 'Brian', 12345, 6)
, ( 3, 'Brian', 12345, 7)
GO
```
I’d urge you to give Data Masker a try if you’re looking to ensure compliant, safe data sets for your non-production environments.

I have other articles on Data Masker if you’re interested.
March 2, 2018
Republish: Elevation of Privileges

It’s another day in the office in the UK and I’m republishing Elevation of Privileges while I’m stuck in meetings all day.

March 1, 2018