Voice of the DBA

Tag: security

The Digital Woes of Public Records

Researcher Chris Vickery, discovered that 191 million voter records are available to the public. The information was in a database on the Internet, which seemed to be a collection of voter records and information compiled from public sources in various US states. While no Social Security numbers were present, names and dates of birth, as well as address and voting scores were in the database.

That’s scary, though potentially not a problem. A number of states publish voter data as public records. A few might have restrictions on the use of that data, but the fact that the data is available means it could be used maliciously, with overburdened authorities unlikely to prosecute anyone even if they’re caught.

This is one of those areas where our understanding and control of data hasn’t caught up to the digital age. It is one thing when public data is available to those that must physically search for it, or even query for singleton records. However data can reveal much more information, or even be used in new ways when large volumes of it is available. Now the ability to access every voter’s name, address, and date of birth could potentially be a problem.

I see so much data that we might have taken for granted in the past, thinking nothing of it’s visibility, being a problem in the future. When someone can gather large amounts of data, and store is cheaply, even accessible in something like a data lake, we may find that public data is problematic. When anyone can start to gather and combine lots of data from different sources, we might find that capability quite scary as potentially lots of information about individuals can be determined. We’ve seen anonymous data sets de-anonymized with the application merge of data from different sources.

I truly hope that we find ways to better protect and ensure privacy in the future, as all the capabilities and power that computing brings to data analysis truly has a dark side.

Steve Jones

The Voice of the DBA Podcast

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

February 9, 2016
Creating a User Without a Login

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

This is one of those simple things that many people should be able to do in order to build in better security for their database code. However I’m sure many people haven’t ever built one. I ran into this recently, and thought it would be a great SQLNewBlogger post.

Creating the User

It’s a bit anticlimactic, but creating a user in a database without a login is simple:

CREATE USER UpdateStatsUser
WITHOUT LOGIN WITH DEFAULT_SCHEMA = [dbo];
GO

This creates a new user in my database,

that is not associated wtih a login.

I can assign this user permissions, like any other user.

GRANT CONTROL ON dbo.Authors TO UpdateStatsUser;

I could then use these permissions any other way.

Why would you do this?

A short summary from Pinal Dave, which is pretty good. Essentially you want to give different permissions to a user, without using something like an Application Role, which has a name and password that must be managed. Here, you can allow a user to execute a routine as another, more privileged user, without giving the original user additional permissions.

SQLNewBlogger

This is a really simple post that took my longer to write than create the user. About 10 minutes. I wouldn’t expect most of you to stop here. I’d want a post that shows you understand something about how this user can be used, show me an example of reading or writing a table as a user with this impersonation in action.

References

CREATE USER – https://msdn.microsoft.com/en-us/library/ms173463.aspx

February 8, 2016
Maybe Security is Harder than Rocket Science

I was giving a talk last year about software development and I made an off-hand remark that most of developers out there shouldn’t be writing authentication schemes for applications. My reasoning was that most people aren’t good at writing these systems and there are plenty of good authentication schemes already written that you can incorporate into a system.

However there are some bad ones as well. While I hope you don’t pick any of them, I also know that many of you might just build a poorly architected system because your focus isn’t on authentication. Your focus is on some other aspect of your application. I’m sure you know some of the good advice for building systems, as well as the best ways to handle passwords, but do you follow it? Under deadlines? When you have pressure to focus on more important aspects of your system? Or do you implement anti-patterns because it’s easy?

The European Space Agency (ESA) is full of rocket scientists. Literally, as they send rockets and astronauts into orbit around the earth. However they were hacked recently and the disclosures aren’t pretty. They not only had personal information released, but passwords were stored in plain text. What’s worse, 39% of the passwords were three letters.

Three.

I’m sure many of the people working on ESA systems were smart individuals, and they may be great web developers that build beautiful, useful sites. However their security programming is extremely poor, and really, there’s no excuse. Not even the pressure of scientists that want simple, easy logins.

It’s 2016. No short passwords, no limitations on complexity such as preventing special characters (one site recently didn’t allow a “,” for me), and no storage in a reversible format. There are lots of best practices, but they require some effort to learn, understand, and implement, as well as modification over time to keep up with changing guidelines.

Or, as I suggested, just stop implementing this yourself. Use some authentication scheme that’s been shown to work well with few vulnerabilities.

Steve Jones

The Voice of the DBA Podcast

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

February 2, 2016
Training at the Top

Many of us see flaws and problems in the way that we code securely as we build software, as well as the way in which our infrastructure security is configured. There have been no shortage of times in my career when I, or a coworker, wondered why our company didn’t work to implement better security in its systems.

Perhaps it wasn’t us. Perhaps it’s not a lack of desire, but maybe it was due to a lack of knowledge. I ran across a piece in Enterprise Security that notes we should have security training starting at the top, with our C-level executives. Far too many of them don’t necessarily understand the threats or nature of the threats because many of these threats didn’t exist 20, or even 10, years ago. Often we have management that has never faced these kinds of vulnerabilities.

I think there’s certainly room for most of us to learn more about security, especially database security and SQL Injection as these are fundamental issues around some of our most important assets: our data. However when we want to implement stronger security, or limit access, we need the support of management, who themselves need to understand the issues, not just respond to whoever makes the best case, or complaints the loudest.

The world has changed, in that our valuable assets can be transferred to our competitors, or common criminals, and we aren’t away of the disclosure. Or perhaps worse, our enemies could change some data and we might never know without the ability to perform comprehensive audits of our systems, something many of us might not be able to do. We certainly need technical capabilities, but also the time and support from management.

I think there is a good case to ask our management make an effort to understand cybersecurity, and I’d urge you to pass this link along to your management.

Steve Jones

The Voice of the DBA Podcast

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

January 28, 2016