Voice of the DBA

Tag: security

Paying for Data

Would you recommend your organization pay a ransom to get the key to decrypt data that’s become encrypted on your database systems? What if the data were encrypted in backups going back two weeks?

It’s an interesting question, and one I might expect we will have to deal with at some point. This week I saw an announcement that another organization, this time a university, paid a ransom to recover data. Before you react with “of course, not,” think about this quote from the University Vice-President:

“We are a research institution,” she was quoted as saying. “We are conducting world class research daily and we don’t know what we don’t know in terms of who’s been impacted and the last thing we want to do is lose someone’s life’s work.”

That’s a powerful statement, and certainly one that I could see salespeople, accountants, and more making as an appeal to upper management. What’s interesting in the piece is that the university acknowledges that using the decryption keys is a complex endeavor, requiring IT to get systems operational again. I hope that at the least they also made new backups of the encrypted data before using the keys. Who knows what sort of results come about from criminals that have encrypted, and the offered to provide reversal instructions and keys.

I expect the ransomware efforts to become more prevalent across the next few years, especially as people have success. Even if fewer people pay ransoms, I bet plenty of people will look to disrupt organizations with these actions. Perhaps even a competitor will look to introduce ransomware into your company? Maybe by offering a job to an individual that leaves such a surprise behind as they get ready to move to a new employer.

There are all sorts of issues here, and potential attack vectors. The fact that ransomware (or other malicious software) could be introduced to remain idle, or even transparently encrypt and decrypt systems until a date passes and the keys delete themselves, is horrifying. At the very least, I think this brings to mind the idea that we need multiple versions of backups, so mirroring a file system to another location, or some cloud service like Azure, Dropbox, etc., isn’t enough. Perhaps we should also have checks in place that look for changes. After all, can you imagine a rouge process implementing TDE and letting it run for a month before deleting the certificates from your system and truncating all the tables a week later?

Steve Jones

June 11, 2016
Changing Your PASS Credentials

I got an email today from PASS, noting that credentials were changing from username to email. That’s fine. I don’t really care, but I know I got multiple emails to different accounts, so which account is associated with which email?

I clicked the “login details” link in the email and got this:

Not terribly helpful, but I was at least logged in. If I click my name, I see this:

Some info, including the email, which I’m not sure is linked to the email I clicked on, or is based on browser cookies. However, there’s no username here.

If I click the edit profile link, I get more info, but again, no username. No way to tie back anything I’ve done in the past to this account.

I have always used a username to log into the SQLSaturday site, so I went there. On this PASS property, I’ve got my username.

If I click the username, I go back to the PASS site, to the MySQLSaturday section, but again, no link to this username. However I realize now which email is related to which username.

Hopefully the others will go dormant soon and I won’t get multiple announcements, connectors, ballots, etc.

The point here isn’t to pick on PASS as much as it is to point out some poor software and communication preferences. Changing to email from username (or vice versa) can be a disruptive change. I’d expect the email would include some information on username and email relation, or at least username since it was sent to a specific email. That would allow me to determine where I might need to contact PASS to update things, or which username was affected for me.

I’d also expect that the username to be stored somewhere and visible on the site. Even if this isn’t valid login information, why not just show it? When we migrated SQLServerCentral from one platform to another, we kept some columns in the database that showed legacy information. This information wasn’t really used, but it did help track down a few problems we had with the migration. Having a bit of data is nice, and it doesn’t cost much (at least in most cases).

This wasn’t a smooth process, though not too broken for me. I like that PASS sent the communication, and I’m glad the old method still works. I logged in with username today. I wish there was a bit more consistency between PASS applications, and that they included a date when username will no longer work. I also hope they update their testing (or test plan) with any issues they discover this week, so the problems aren’t repeated.

May 24, 2016
Half Baked Features

I gave a talk recently on some of the data protection features being added in SQL Server 2016, along with a few that have been available for some time. I think the talk went well, but I point out lots of potential issues with the features themselves. I had a question from someone that noted Always Encrypted, Dynamic Data Masking and more aren’t really full developed, are they?

The question threw me, not because I agree, but because I think that there is a complex answer. Encryption and data security features are easy to use. The features really are easy to implement, especially encryption. Most encryption is just another function call for the developer, which is something almost all of us can write. The mechanics of using these features are fairly easy.

However the architecture, the decisions on how to manage keys and where to deploy features, those are hard. We deal with those relatively infrequently, but when we must make those decisions, we should carefully consider the ways in which our systems might be attacked or mis-used.

Some of the restrictions that I see in various features are unavoidable. At least, I can’t see a way to avoid them. In some sense, things like a binary collation are almost required because of the nature of how encryption must operate on data. Perhaps there are ways to mitigate issues, but I’m not sure. No security mechanism is perfect and all encryption can be broken. However I think the way these features work is good enough in many situations.

There are some things, such as allowing Dynamic Data Masking on Always Encrypted columns, which can be implemented. However, the changes are more complex, and involve not just SQL Server changes, but probably also ADO.NET changes. Making the decision on how to actually implement these changes shouldn’t be taken lightly, and I’m happy to have a working feature that might be enhanced in later versions over not getting any options at all in this version.

Steve Jones

The Voice of the DBA Podcast

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

May 17, 2016
The Proliferation of Roles

The best practice guidance for SQL Server security is to use roles for permissions, instead of granting rights to users. I’ve always followed this guidance in my career. I’ve learned that if one person needs access, sooner or later someone else will. Even if it’s an automated process, I’ll use a role so that I can build a test login to ensure I’ve configured things correctly.

In small companies, or in relatively static applications, this makes perfect sense and few people argue with the rule. That is, until they get some requirement that only one account will ever be used. Then they want to just grant rights to a user. After all, why add the role for one person. See my thought above. Sooner or later, someone else will want access.

However I also have had people complain that if each new required position or process needs their own role, sooner or later we’ll have this proliferation of dozens of roles.

True.

I worked in a large Fortune 100 company and we had thousands of groups in our AD forest, and easily dozens of roles in many databases. While that might seem complex and confusing, it wasn’t bad. We named roles to match AD groups or job functions, and adding in new users was simple as we usually mapped them to the same roles as a previous user. If a new system or person needs access, usually their access is the same as some other account.

However, I know they can be complex, so I’m wondering if you have any tips, tricks, hints, or even gotchas for using roles. I’d also be curious if you think there are cases where roles don’t make sense.

Steve Jones

The Voice of the DBA Podcast

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

May 13, 2016