Archive for the ‘Database Security’ Category

I was recently having a discussion with a client and they were looking for recommendations on how to handle a common security issue with SQL Server. Here is there situation, they have a number of applications that connect to the database, and security was implemented by using Windows Authentication. The application required read and write access directly to the tables so this required that each of the users had direct read/write access to each of the tables in the database (starting to feel uneasy yet). It didn’t take long before people were connecting directly to the database to start making changes outside the application, or pull reports of their own. Even if the data was not sensitive, and it was… making changes outside the application created all sorts of problems.

Then answer was pretty simple and came in the form of application roles.

Access can be granted to the individual users either by each account or as a group. The permissions can be limited to allow for log on only. Once that has been completed the application role can be created using the Create Application Role syntax. Permissions can be granted accordingly to the application role that allow for the direct table access.

When this is done and someone logs on via the application the application can issue the sp_setapprole and that connection can now use the elevated permissions. If the user were to try to connect to the database without the application role the elevated permissions are not there unless they have the password for the app role and set that role for that connection.

So in other words the application becomes only access method to the data as long as permissions are not granted elsewhere to the individuals user account. This is a great way to take advantage of everything that AS has to offer in the way of account and permissions management without having to worry about opening the database up.



Posted: February 3, 2014 in Database Security

I was surprised again last night by another news report that yet another company has joined the long list of companies whom have had data compromised. The short list of companies includes names that many of us know. I even know people personally who have gone out and canceled credit cards just to decrease the odds they will be impacted. What surprises me the most is how companies are reacting or not reacting to the recent security breaches. I hope that there are many companies looking at the list and are terrified they are next. Each time I hear of a new security breach I can’t help but to think of a couple key points.

Are we seeing just the smash and grab jobs? Consider this, say you owned a large retail store, let’s say one that sells just about everything under the sun. If you had 300,000 products in your store, would you notice if 4 or 5 went missing? What if of those 300,000 products 3,000 of them were televisions? Would you notice 4 or 5 T.V.’s missing? Chances are you wouldn’t, and if you did, how long would it take you to discover them missing? Now what if those T.V.’s were magic T.V.’s, the kind that you could copy? If someone made a copy of the T.V. and walked out the door would you, the store owner, ever know? I ask these questions because it appears to me that no one notices data is missing until large groups of data are compromised. One could argue that the large numbers come from data that is at risk and that it may not have been lost at all. There was a movie a number of years ago, I think it was Swordfish or something like it. Anyway, the way the thief would get away with the crime was to take just small amounts of money from many people, rather than a large amount from a few. The idea is that an individual may not miss a penny or two, but when removed from millions of transactions the pennies are well worth it. If we are only hearing about the smash and grabs on the news, how much is happening we don’t know about?

Security is a Puzzle. A few years ago I had to pass a security certification for a position I was in with the US government. One of the principle points I learned was that, as a whole, security is a puzzle. To someone who wants the data on the other side, the security we have in place is nothing more than an elaborate puzzle they need to get through. If you were a hacker, what information do you need to know to start? Sure you need the skills that it requires, but there is more. What if I were a cat burglar? If I wanted to bypass an alarm system at the museum, wouldn’t it help if I knew what kind of alarm it was? If I know the maker of the alarm, would the model number help me? Each piece of information that I have as a cat burglar is one more variable I can remove and, as I remove variables, the puzzle solution becomes just that much easier. We have to identify all the places where we are giving the keys to the bad guys. To log on to a system you need at least a couple pieces of information, a user account and a password. Is the user account on the screen, is the password on a sticky note? When you go to a web page and there is an error in the code, does it tell you the company is running mysql, or that the web server is apache?

Take a look around, how can you help remove the pieces of the puzzles your organization is willing to share?

A couple quick notes…

I found some interesting statistics on data loss and did a short post on why these simple numbers enforce the need to complete regular Security Audits on your databases. Based on the numbers I found, completing a security audit and making appropriate changes a company can limit its risk by nearly 50%, just by following best practices internally to your organization. I hope you find it of interest, you can find the full post here on Xtivia’s blog.

My first presentation at the PASS Summit somewhere around 8 years ago was all about how to hire a DBA and how to be hired as a DBA. It was received really well and I was really amazed by the feedback. This past November in Seattle at the PASS Summit I was presented an opportunity to present on some interviewing tips. I am excited to say that on March 13th, I will be presenting this for the PASS Professional Development Virtual Chapter. The great thing about these chapter meetings is that you don’t have to leave the office or your home to attend the event, because it is all on line and it is free to attend. If you would like more information or would like to attend, you can do that here. I hope to see you there.

In the new Pro SQL Server 2012 Practices chapter 9 is all about SQL Server compliance and auditing. Written by a friend of mine Jonathan Gardner (B|T) who is based out of New Orleans, LA. So if you don’t hold that against him I think you can find some great information about working with SQL Server Auditing features. This chapter in particular sits well with me because I don’t know if DBA’s understand how helpful a good Audit can be. When I first heard the feature name, the first thing that came to my mind was a feature that would assist administrators who are working within one of the current audit guidelines. Audits like Sarbanes-Oxley (SOX), HIPAA or PCI DSS. Now, granted it can do that. But it can do so much more as well. I have even tied in an Audit to my dbUtilties database, but that is more for another time.

The second thing that came to mind is the all too often heard question that I get when I am talking about features with SQL Server, and that is “Is this only available in Enterprise Edition?” Jonathan has been careful to include not only how to set up and configure your Audit, but letting us know about the changes with 2012. So if you think that you need a copy of Enterprise Edition to make the relevant to you and the organization you work with, I think you are in for a surprise.

The chapter starts with a review on what some of the common audits are and how they impact you as an administrator, then walks though some of the configurations options that you will want to know about. He then walks you through setting up and audit. The chapter then comes full circle by ending with some tips on auditing I had not considered before. There is even a section that shows audit group areas that it a must have if you are working with the HIPAA audit.

The more I consider the audit features with SQL Server I am not sure I can think of a reason that someone might not want to use it. I see the benefits in many areas including trouble shooting. Jonathan does a great job of presenting the information and the chapter is a great addition to the book.

In the United States of America you are innocent until found guilty. I want to make sure that I am very clear on this fact, because one of the many databases that are involved in protecting this country came under fire recently. I have done a bit of research and there is not a lot of information that has been released about this. But I can tell you that there is an individual who in his own description calls himself a Systems Analyst at the Department of Homeland Security, I will call him “Bob” to protect his rights.

From what I have gathered “Bob” who worked with the TSA database, found out that he was being let go. It appears that “Bob” was not too happy about this and tried to take the database down. According to a few articles that I have found it looks as if the database was the one that is used to protect us as we take flights around this country…

So I have to ask? Are we being serious enough about the permissions? Is our data secure? Is your company data secure? This is not a joke and the data that we keep nowadays is more important than ever. But I am always surprised at how many databases are left open. In this case “Bob” may have been a DBA, and had all the permissions that he needed.

But I ask you to remember:

  • When someone turns in two weeks notice as a DBA, don’t be surprised when they are walked out the door that second.
  • Next time you are audited and the auditors are being a real pain, hope they are the ones that audit the database where your data is stored.
  • If you wonder why developers are not in production and only Admin’s and Change Managers can change production, it is to limit liability, it’s not a matter of trust.

Just in case you were wondering… “Bob” is up on two charges of the Federal kind. These are the kind of charges that can put you in a very bad place located underground. According to the Linked In site that I found “Bob” is working as a consultant for the last 6 months. (I found “Bob’s” LinkedIn Page by Name, City, past Employment)

Have you checked your consultants recently?

Story in EarthTimes

Denver Post

Computer World