October 27, 2009 at 4:18 pm
Hi,
I’m considering a permissions problem from a slightly different perspective (I suspect I kind of know the answer already, but here goes ...).
Previously, I’ve not been worried about what the person administering a database I’ve developed might get up to (i.e. their organisation paid for it, so if they break it well .. :/ ).
I’m looking at a situation where we are wanting to limit what hosting organisations can actually do with the database (specifically querying the contents of tables which we only want them to access through a web-app).
The SQL version is 2005.
What I am implementing (so far) is ....
i) - Encrypting View/Procedure definitions
ii) – Signing procedures so they cannot be altered/EXECUTE AS, with USER WITHOUT LOGIN having SELECT etc. Permissions on views / tables
iii) - Having a schema (other than dbo), with a USER WITHOUT LOGIN as the owner (who has CONTROL/ALTER etc. but not SELECT)
iii) – Having a user role with execute permission on relevant procedures etc. (i.e. a database role with appropriate permission and a database user dropped into the role)
iv) – Data encryption on (very) select(ed) columns (i.e. certainly nothing involved in SARG/Ordering)
However, I’m wondering how far its is practicable to attempt to further limit what the curious DBA can actually do with the database (given that encrypting everything is out of the question).
In an ideal world we’d only want the DBA / sysadmin with as limited capacity to view/query data as an ‘ordinary’ application user.
Is there anything further that can be done (e.g. deny dbo/db_owner SELECT etc. Permissions on schema objects ???, something more fancy with certifications and USERs WITHOUT LOGINs).
A sysadmin / db_owner can surely just add users/roles/permissions/objects to circumvent all the above, or I am as a sqldeveloper-and-non-dba missing something here? (this is a good learning exercise for me).
Suggestions & (polite) comments greatly appreciated 🙂 (Particularly if there is anything further that can be done
Thanks
October 27, 2009 at 4:32 pm
Ultimately you can't stop anyone. Unless the app has the keys to encrypted data, if someone has control of the machine and domain, they can get in. The service account needs rights to get to everything, and an admin can change that.
In terms of what you've done:
1. basically obfuscation. Someone can decrypt these, but why are you worried about view/procedure changes? If they change something, the app might stop working, and they'd have to deal with that. Is that a real concern?
2. Interesting. I'm not sure what will happen here. I assume they could re-sign things, but maybe not. That's a good idea and might help.
3. I'm not sure what you're doing here. Is this user for you (the vendor) to use when you need to change things? This is good for the casual person, but I'm not sure what it means for the advanced user. I suspect they could just add another user as the schema owner.
4. (iii 2 ) This is standard security. I'd expect this
5. What's the point here? I don't see why a vendor needs to protect the data. Typically it's a company that wants to protect the data from release, or from prying eyes. If that's the case, this works as long as the keys are managed well. If you use SQL Server encryption, I think a DBA can find a way to get access to the keys. If you use certificates outside of SQL Server, and submit them with the app, then you are much more secure.
Ultimately I'm not sure what you mean by hosting organizations. Is this something you sell to other companies or is it something that you own and are hosting somewhere on shared servers or in a colocation facility?
October 27, 2009 at 7:42 pm
If you can't trust your hosting organization, you need to get a different one. Those guys should be lawyered up to the max and under 17 layers of NDA. If they violate any of that you could end up owning the company.
But it's like Steve says, if they can administer the box, they can administer SQL Server. There's just no way to split it apart from the OS.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 28, 2009 at 5:13 am
Hi,
Thanks for the replies. They are most helpful.
Basically its an ISV installing software, and we want to ensure (as far as possible) that they (end users) only get to see what they are licensed to see.
Without going into the exact details the database application could (probably will) end up gathering more information than users are strictly licensed to report on.
Indeed this is a selling point, as summary information will be returned to users based on all the data recorded, but we'll restrict details to that subset they are licensed to see.
This is fine, until a DBA etc. managing the estate the software is installed onto tinkers with the database directly. So anything that makes this a little more difficult is good.
As the volume of data will be reasonably large, we want the filtering process distinguishing between licensed/unlicensed info. to occur in the database.
The database will need a sense of what coverage the user is entitled to see, this will be encrypted and used to help control what volumes objects return, and this value can perhaps be returned to the application to double check what is returned against what is licensed to be returned.
Any further data encryption will be considered provided it does not impact (significantly) upon performance (i.e. in 2005 indexing encrypted columns wont help much for WHERE clauses!).
As far as certificates are concerned, I'm still investigating this fully, but from suggestions I've seen one can sign objects and then prevent re-use of the certificate.
http://www.thelastpickle.com/2008/01/15/how-i-think-sql-encryption-is-meant-to-be-used/
Thanks again for replies 🙂
October 28, 2009 at 7:22 am
That's interesting, and I think you are in for some heartache.
My guess is that signing things, or maybe not including the functionality would be better. But ultimately, if you are trying to license on a volume of data, I think you're out of luck. At least at some customers.
And I'd ignore those. Likely most customers will just pay for the access. Not worth beating up a few that get around it, or can get around it. They won't see the value in it, and I think they'd be right.
My opinion is this is a poor business practice, but that's from your brief description. maybe there's validity here.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply