SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

You Must Trust Someone

After some recent talks with security folks and auditors, one of the things I have had a hard time getting across is that you must trust those folks responsible for account and server management when it comes to securing your data. Yes, you can put in a lot of deterrents, but at the end of the day most of them can be beaten by a wily admin. So if you're not hiring trustworthy people, that's the first and most important weak link in your chain. Now this isn't to say that you won't have to deal with the case of someone who has gotten disgruntled or who has gotten in a situation where they can be manipulated. Though rare, those things happen. But at the end of the day, you must trust your server and account administrators.

Let's look at an attack vector to explain why. Best practices with Windows security says to do permissions management using security groups. So typically you'll have a Windows security group for your DBAs' elevated accounts (if you aren't using elevated accounts, here's why you should). What's to stop said account administrator from creating an account and dropping that account in said group? The account only needs to stay there long enough to get the data and get out. So the account admin does just that. Creates a new account (to try and avoid non-repudiation), adds it to the security group, and then logs on to the SQL Server with sysadmin rights. Now everything on that server is accessible. Yes, even the database you encrypted using Transparent Data Encryption (see previous post about how this doesn't stop an admin). So you've got to trust the folks working account administration.

What about server administrators? You've got running SQL Server in single-user mode, as I described last post, but there's another trick possible, too. It used to be the recommended advice for handling the permissions was to do the following:

Windows User Account -> member of Domain Global Group -> member of Server Local Group -> Access to Resource (file share, SQL Server, etc.)

If you're still following that model, the server admin can simply make a new account a member of that local group on the server. And the account could even be a newly created account on said server, for the same reason the account administrator might do so. So the admin now has access to SQL Server and the data. If you're not following that model, and you're only using domain security groups, then there are the attacks I gave before still work. In addition, for SQL Server 2005 installs which aren't on a cluster, there are the MSSQLUser and SQLAgentUser local groups which have sysadmin rights within SQL Server as well.

So it sounds like you can't do anything about it. And to a certain point that's true. If someone is bound and determined to get the data and has those types of rights, they're going to be able to create a way. By following best practices you will cause those who aren't hell bent on getting in to stop, especially after they see any sort of resistance. But what about that 0.1 or 0.01% that will do whatever it takes? The catch then is to go from prevention to detection. Solid auditing is required. There are a few event IDs you'll need to watch for in the Security event logs:

These event IDs need to be monitored on the domain controllers as well as any servers where SQL Server is running. The events themselves will need to be investigated to ensure that the the appropriate groups aren't being changed. And if you're talking about the local to the server groups, you'll also need to be getting the events off of the server and into a secure repository in as timely a manner as possible, because the admin could always purge the security event log. And if you've been thinking along the attack vectors I've given already, you've thought of the idea of creating a local account, making it a member of the local Administrators group, and flushing the Security event log under that user account, thus erasing who created the account in the first place.

Now back to my original premise, you can automate all of this and have a great auditing system, but at the end of the day you must be able to trust the people you hire into these positions. Even if the person gets caught, they've still gotten the data. You may be able to prosecute them and recover your losses from a financial perspective (to a certain extent), but you've still taken a reputation hit and if it's privacy information, you've got a bunch of folks who are now at risk. If it was intellectual property, then it could be quickly sold or just given away to another entity. Trust them, but check up on them with sound auditing.


K. Brian Kelley - Databases, Infrastructure, and Security

IT Security, MySQL, Perl, SQL Server, and Windows technologies.


Posted by Anonymous on 20 February 2009

In Brian Kelly's recent blog post , he makes an excellent case outlining why there are little options

Posted by Anonymous on 20 February 2009

This is a follow-on post to You Must Trust Someone . My point in that post was to establish that being

Posted by Mark Horninger on 26 February 2009

"Trust but verify"... a good approach!

Posted by TimothyAWiseman on 27 February 2009

Trust but verify.  With admins, its the only approach.  

Posted by reggiesnelly on 27 February 2009

Hire trustworthy people and establish a process of examining events is excellent advice.

A few other thoughts.

Ask for explainations about events, let people know you're monitoring and always looking to improve.

Create business or corporate security policies associated with the administration of the SQL Servers.  These can be made extremely clear to the OS system admins.

You don't need to provide information to the OS domain level admins as to how user logins are audited within SQL or the notification process of changes.  If you're in a large enough setting there are probably already SOX audity procedures in place for accounts.

The data contained within tables can be encrypted, of course this is resource expensive, but can provide another layer of protection.  Then only allow the end user\service accout access to the cert.

Make it difficult for oneself as a DBA to have access to the unencrypted info such as credit card numbers.

Posted by Amit Lohia on 27 February 2009

Security is making the hacker believe it is not worth the effort. It is not worth spending millions of dollars to secure things which are  worth thousand of dollars.

As Brian mention you have to trust someone and hopefully the person do not betray your trust. People are ethical in nature (DBAs top the list :)).

Posted by Simon on 3 March 2009

The concept argued in this article might hold true for SQL Server's security model but not for other databases. At its simplest role based security is good enough to protect any data from any user or group, regardless of whether they are network or database admins.

Essentially you just don't allow access to anyone but the owner of the data (the creator). The hole in SQL Server's model is that it allows Windows Administrators special privileges that allow them to manage membership to database roles that don't belong to them. The reason for this is convenience. If the owner of a table suddenly got hit by a bus and no one else new his credentials to access his data, that data would be forever lost.

We have built a proprietary relational database that can optionally operate in this security model where there are no backdoors or special privileged users. Combined with encryption this makes it as close to impossible for anyone but the onwers of confidential data to access it.

Posted by Simon on 3 March 2009

Also, detection by putting in place audits isn't necessarily that effective. If someone has admin rights then they have the ability to take a copy of the database (or even the entire environment) without tripping any audits. On the copy of the database they can proceed to alter their user account permissions to see payroll info until the cows come home.

Posted by K. Brian Kelley on 4 March 2009

The point that was made, though, Simon, is that you still have to trust your admins. Your database has to run on an OS and it has to be accessed by users. By the nature of being an admin, I have the ability to hook a debugger up to your application. And therefore, your other mechanisms are breached. Or failing that, I capture the logon credentials of one of the owners of the data.

And with respect to auditing, copying a database isn't such an easy endeavor if the database is on a moderately heavy environment. You'd have to bring the database off-line twice. The first time to copy it and the second time to copy it back. And in the meantime between when you grabbed it and put it back, you've got a complicating factor that other data changes will end up "missing" and that could be enough to lead to detection. Also, in cases where SQL Server is being used with built-in encryption, you've also got to get the encryption keys. So it's not as clear cut as copy and copy back. Not to say it's not a do-able attack vector. It's just a bit more complicated than you let on.

Posted by Simon on 5 March 2009

In fact, I would prefer to operate in an environment with superuser admins with my trust. It's far more convenient to get them to fix something when the security goes belly up. I was simply saying that it's not a requirement that admins have god powers in your RDBMS. Role based security is sufficient to lock data down to the data owner.

Regarding running a debugger or password capture (using a keylogger I assume), or packet sniffing, trojaned clients, camera over the keyboard or whatever other method you suggest, these are not DBMS concerns. They address different parts of the surface area. I can suggest ways to prevent each and every one of them but that is beyond the realm of this particular discussion.

I think you may have misunderstood my point about the database copy problem. The goal is in exposing some confidential information without being audited. For example, viewing celebrity medical records, salaries, passwords, financial records, etc. Why would you need to copy it back? Also, if you don't want to take the database offline it's pretty simple to use a copy on a tape backup isn't it?

On the whole I agree with you. In the real world you do need to place trust in your admins as it's too inconvenient and expensive to lock down your database with fine grained role based security, biometric identification, cryptography in transit and at rest, etc. I just wanted to point out that its not impossible to prevent admins seeing what they shouldn't see if comprehensive security policies are applied correctly (except the SQL Server DBMS which essentially has a backdoor for Windows Admins). As Amit said, if the data isn't that valuable the there's no point going to the trouble. But if you're talking about launch codes for nuclear weapons then I'd imagine it's probably worth the cost and effort.

Posted by Anonymous on 20 November 2009

This was actually spurred by a post from Ted Krueger ( @onpnt ), which led to a short, but hearty, discussion

Leave a Comment

Please register or log in to leave a comment.