Data Security and SQL Server

[zachattack05]

So I've been thinking about SQL security lately and wanted to see what others are doing.

I have been using integrated security (window's authentication), stored procecedures when necessary and have done my best to limit access to tables and those stored procedures with SQL server in conjunction with my application. But I'm starting to wonder if that is enough.

Imagine for a moment that you are developing an application that requires strict security, say for a bank or something and SQL was going to be used as the backend to keep track of transactions and account balances etc... Now, you can use stored procedures and all that I have above, but what is there to stop some rogue employee from installing SQL Server Management Studio or literally any sql tool to talk directly to the sql server. Even if you limit access with your application or even on the server level, a delete permission is a delete permission and it doesn't matter if your application issues it or if another one does.

Is there a way to prevent this other than on the workstation level (preventing install permissions for users etc...) Can you actually setup SQL to only talk to your application and that's it to prevent others from accessing the data?

I'm just curious how larger corporations would do something like this. I mean, imagine Epic, the health information software, or the software that banks use, or the software that any large corporation would use to track accounts and payments and services like AT&T, Sprint, MCI (do they exist still?) or any point of sale store where all the stores are wired together like Walmart (buy something at one store and you can return it at any of them with the same receipt). Even though some of these may not be all that "important" (forging receipts compared to stealing money from the banks) they all need security and I know not all of them would use SQL, but some might use something similar or may actually use SQL! How do they keep prying eyes and fingers out of the data except through the appropriate channels?

Any ideas? Suggestions? Articles/links?

[WDrago]

I'll take a shot at this, and this may be a very naive idea since I've only been using SQL Server for a short time, but I think I'd put the database behind a master application and have the clients communicate with the master app instead of directly with the database. The clients have no db access permission, only the master app does.

I think this is what banks do anyway to deal with the distributed nature of their databases--Bank A takes over bank B and now bank A needs some sort of interface to bank B's database. The master app takes care of this and to the bank tellers it looks like every account holder is in the same database regardless of where that customer's data really is.

-Bill

[Momerath]

First, you don't use integrated security. Users have no permissions on the database, just the server does. Everyone talks to the server, server talks to the database (n-tier). Users can install whatever SQL tool they want, but they can't get through the databases firewall, since it only accepts connections from the server. Even if they could get to the server, they have no user id/password for the database.

Everything is duplicated across tables, everything is backed up frequently.

Yes, there is someone out there who has access to the db, someone has to have system admin privilage.

We (where I currently work) deal with this by having the SA account owned by someone who isn't a DBA and has no physical access to the machine. When the DBA needs to do something, they need this person to come log them in. Everything done on that machine is recorded with key and screen loggers. Anything 'missing' from the logs triggers a full audit of the system.

[Ketsuekiame]

Just because you use Integrated Security doesn't mean every man, woman and child on your domain can still access the database. They still need to be given permission.

Even with SQL Studio, if you don't have permission, then you can't access it :)

Our servers are configured with "default deny" permissions. So, everyone is denied until the server is told otherwise.

MySQL operates differently and uses the equivalent of SQL Authentication Mode, Oracle I think has its own implementation.

The weakest point of your SQL Server is generally the user facing side. This would be a website, or win forms interface.

When you program these points incorrectly, for example: String query = "select * from MyTable where " + tbxFilter.Text end users can perform what's called SQL Injection attacks.

[zachattack05]

Ketsuekiame,

I know that it doesn't open it up to everyone, but the problem is that if Joe Employee has delete permissions because it is part of his job, and he becomes angry or whatever, and decides to delete or change data to cause problems that's what I am trying to prevent.

Momerath,

Do you know of a good resource where I can read about that concept or if you have a moment, could you elaborate on how something like that would work?

WDrago,

I think you are talking about the same thing Momerath is? Do you have any resources where I can read about that concept as well?

Thanks so much for your help!

[Ketsuekiame]

Ketsuekiame,
I know that it doesn't open it up to everyone, but the problem is that if Joe Employee has delete permissions because it is part of his job, and he becomes angry or whatever, and decides to delete or change data to cause problems that's what I am trying to prevent.

So you have four options.
1. Make them log in with their account details (your problem exists)
2. Make them log in with some specific details (your problem exists)
3. Release different versions of the software with the account information hard coded into it specific to each employee
4. Central server access only

Even in #3 and #4 your problem still exists, because using your software (which I assume they will do their work in) is still modifying the database. They can simply use your tool, rather than SQL Studio (arguably SQL Studio would be harder to use because they'd need to know SQL) to mess with the database and cause havoc.

The point is: if someone needs write access to the database, no matter how you control it, they can still mess with the data they have access to and you can't stop them.

So now the issue becomes IT Policy.

First, back up regularly. However, if your disgruntled employee has been making small adjustments for a long time, you will probably never find them without a very intensive regressive audit. Undoing that damage may not even be feasible.
Second, ensure that messing with the data is a punishable offence by contract.
Third, audit everything and regularly review the audit data. (everything we do is audited at some level, either at a procedure level or by utilising triggers. You can't touch a single thing in our live databases without it getting audited somewhere).

Finally, I completely disagree with Momerath's way of working. I know ofa DBA team who (arguably again) work with more sensitive data than bank details.
They do not have a central "Server Admin" who has complete control of everything. There's actually no point to it.

Once the SA has logged the DBA into the system, do they then sit over their shoulder watching everything they do? Once you've logged them in you have no control over what they're going to do once they're in. Momerath mentioned screen capping and key logging.

If you screen cap and key log everything, then what you're doing is actually making a context sensitive copy of extremely sensitive details (which is probably worse!). There are a lot of rules, especially in the UK about how sensitive data is held. So now you have a second problem to deal with...

You need to have some trust in your software team, that kind of paranoia will only hamper productivity especially in a large development team (we have several hundred devs for example). So long as everything is audited properly, procedures are well documented and not following procedure is punishable, then there's not much else to fear other than external attack.

I should note, that in our case, not following procedure correctly could lead to jail time due to the nature of what we work with. However, in the normal case, the punishment would usually be monetary based (example; loss of job or fines)

[zachattack05]

Ketsuekiame,

I kind of agree and I understand that some trust has to be given to employees. That's really not what I am worried about.

The sabatoge that Joe Employee does by using the application and messing things up that way is the least of my worries.

I'm worried about an employee with some knowledge of SQL that wants to "tinker" or sabatoge the system that way, rather than through the application interface.

I was hoping there was a way for SQL to identify the application connecting to it and either allow or disallow that application.

For example, since the application that I am writing deals with medical information, I want to make sure that no one can just "dump" entire tables into a file and walk out the door with SSNs and names and addresses etc... Obviously I can't stop someone from sitting in front of my application and writing them down, or printing records one at a time. Data theft is like software piracy, the only way to completely stop it is to not let anyone see it or use it and you just can't do that, but you can make it difficult for them to do it.

I doubt most of the people using the application will have rogue employees, and I doubt of the ones they do have even less would know SQL or have the knowledge to execute it, but you never know.

I was hoping for a way to allow access to a database only through specific channels is all. It's not perfect, and some of the stuff that you and others are suggesting is not something I can program into my application. Company policy, and key logging for example are good ideas, but I can't implement that, the end user would have to.

The reason this is a big deal to me is it is a huge selling point for the software. The application I am creating has little competition, and the competition that I have found uses flat files that must be on a file share to be accessed my network computers. The user names and passwords are stored in plain text and anyone with a thumb drive and access to the network can copy the entire system to it and walk out the door.

I want to blow the competition out of the water with this and boast about how great security is, right out of the box. I think using SQL alone is a huge improvement, but I'd like to do everything I can to make it easy for the user and secure without the users having to know SQL or set SQL permissions, my application should do all of that through a simple interface.

[Ketsuekiame]

I'm worried about an employee with some knowledge of SQL that wants to "tinker" or sabatoge the system that way, rather than through the application interface.

Access control doesn't give them global access. You can limit their access down to specific tables if you so wish. They should only be able to do in SQL Server what they can do in your app. Being able to delete rows is a different permission to being able to drop tables for example.

Generally speaking in large distributed apps, you shouldn't ever connect to the database directly.

You should note that data theft is a criminal offense, not a civil one (even in America). It may be a PR nightmare for the company, but they will be dealt with by the law.

A word of warning (or a few); you're entering into dangerous territory. Do not get into this without having a lawyer (who specialises in IT) creating your contract of sale. Seriously. When (not if) things go wrong, you need a strong contract and a strong legal team on your side. If only to prove you're not responsible, because they will cover their own backs and you will be a (if not the first) target.

[zachattack05]

I plan on running everything through an attorney. I already have one lined up to help with that when I get to that point.

I think I'm going to have to research this a bit more on keeping people out of the database except through the application's interface. I know there has to be a way to do this.