April 15, 2007 at 8:56 pm
Hey,
Long time reader of sqlservercentral, but first visit to message boards. I was wondering if anyone has heard of this type of request before.
My company just purchased an ERP system where we will be using SS2005 as the database platform. I've been administrating other dbs on SS2000 for quite some time. Since the ERP has sensitive data, my boss doesn't want me to have full rights to all the tables. Therefore, I have been trying to set up a dummy database so that on this new server I can:
1) create, backup, and restore databases
2) Monitor usage /performance
3) create and manage maintenance plans
4) etc...
basically I need access to all DBA tasks except user permissions and certain tables.
Unforunately there is no way my boss will listen to reason about me, or any other internal employee, having full dba responsibilities to this system (and the erp system doesn't have any better ways to help with this type of request)... .so in the end, after everything is setup, he will request that we chagne the sa password (and windows administartor password if needed) so that I can't see certain things in this database.
I've played with SS2000 and SS2005 server roles and db roles, but I still haven't been able to lock it down sufficiently (i.e. is it possible to not allow access to 2005 via windows authentication (there's a windows only, but no ss only)... more importantly is there a way to do this and still be able to access maintenance plans (I've used db roles and server roles, but I still can't create a non-sa user that doesn't have security/user access but can create maintenance plans).
Sorry the ramblings, if you need any clarification let me know.
Paul
April 15, 2007 at 10:29 pm
Tell your boss he's going to have to find someone he trusts.
Perhaps he should consider external hosting.
April 16, 2007 at 10:14 am
Hi Paul,
We always lock away the sa account with a strong password and leave it disabled (even us DBAs don't use it, except for emergencies). We then use Windows accounts so that there is an audit trail. One possibility is to make use of the SQL2005 DDL Audit triggers and set things up so that your boss is emailed if anyone other than the ERP app accesses the sensitive tables. This may make your boss a bit more comfortable.
Of course, you can, as DBA, take copies of the database and access what you like, or even disable the triggers whilst you have a nose, but as Michael says, your boss needs to trust you, as often the DBA has access to potentially the most sensitive of data - HR DBs, DBs with customer credit card numbers, bank account details, etc. If your boss is really that concerned, then s/he should specify to the app developers to have the sensitive data encrypted. Otherwise, Michael's suggestion is about the only other option - and then you get into the realms of other security issues.
HTH
April 16, 2007 at 1:09 pm
Someone has to have the power to manage the system and the databases, so if they don't trust you or other internal people, maybe they could contract with an external consultant to be the DBA.
I think there is a good chance that when your boss realizes that level of security will cost money his concerns about you will disappear.
April 16, 2007 at 8:51 pm
I do not think that it's possible to lock down SQL Server AND the domain hosting it such that no one could ever access the data contained therein--and if it were possible, then if something went wrong--or if the need arose to change something (such as a code update, or maybe a Windows security patch), you might not be able to do so.
Large companies--seriously large companies--mange this by division of responsibilties. You'd have net admins, sys ops, and support DBAs responsible for keeping the Production machines running with sufficient access rights to do so, but without the know-how (or perhaps encryption keys?) to actually use or benefit from the data they're guarding. On the other side, you'd have developers and testers who had extremely restricted access to the Production servers. (I worked at such a place for a while--a lot of effort and money [Sarbanes Oxley] goes into just running the system, without any of it really helping the company's bottom line that I could see. But dang, their code and data was secure.)
Smaller companies don't have that option, becuase they don't have the people--or perhaps can't afford the teams of people with the know-how to actually do it properly. (Don't ever kid yourself--there is always someone out there somewhere on the web who is smarter than you. And no, it isn't me.)
With that said, much of what your boss is looking for may be possible in SQL 2005--but I haven't been using it long enough to know for sure. Some thoughts:
Check out the GRANT sections of BOL. It looks like you can grant or deny permissions on anything you can think of in 2005--and a heck of a lot you've never even heard of.
No clue about maintenance plans. They were kind of an overlooked corner of 2000, I doubt that's changed much with 2005 (especially with the recent sp2/sp2a boondoggle).
[Hmm, looks like I went a bit overboard with the rest of this. Sorry 'bout that.]
To lock down SysAdmin rights via SQL Authenticated logins, all you'd have to do is make sure only the SA account has SysAdmin rights. Then get someone your boss trusts--such as your boss, or maybe his boss--to generate and enter the password (while no one's watching, better sweep the room for mini internet cameras first), write it down on paper, seal it in an envelope, then lock it in the company safe deposit box at the bank where it takes two signatures to access it for if/when they need it. Apparently you can rename SA in 2005. (You could in 2000, but it was a Bad Idea.) This would be a good start.
To lock down SysAdmin via Windows, once again limit the accounts with SysAdmin rights. No mere user or application *needs* SysAdmin rights, so check all those logins--especially the local machine security groups--and modify them accordingly. That will leave the account that's running SQL Server (and SQL Agent, I don't know about Browser, Full Text Search, Intergration Sevices, Analysis Services, Notification Services... don't konw how many of these I've missed), so make sure no one knows any of those logins... and make sure the domain admins can't change the passwords to ones they do know, or they can log in as that account and have SysAdmin rights. Heck, better set the services to just run as "local service" and revoke all NT authenticated SysAdmin rights except for that. [Of cousre, this probably can be emulated/faked somehow, but I don't know how to do that.]
Naturally you'll miss a ton of things and leave loopholes all over the place, so you better get some security auditors in to check your configuration, give you some forms to fill out, some security procedure and protocols to follow, and they'll be back every now and then to check on your compliance, but obviously the cost will be worth it because the company shareholds will have peace of mind (as will the CEOs, who are under the threat of jail time if anything goes bad on their watch).
Of course, once you've got all this in place, you can get on with whatever work you were supposed to be doing before the security blitz came in. Oh, the projects may be late, and it may now take three times the effort to get anything done, but that's life in the big city.
(And you know, when I spell all this out to some folks, they roll their eyes, agree with me heartily, and then ask "but what can I do? That's what the boss/manager/supervisor requires." )
Philip
April 16, 2007 at 9:16 pm
Remember that security has a number of aspects.
One is preventing people who shouldn't be able to do things from doing them.
Another is making sure that you have the data and it is properly secured against loss.
No one will care much about the first when they find out you just lost the entire database. If you have no way of making sure that the system is backed up, not experiencing errors, not corrupted, etc., then that is what you are leaving yourself open to. You can be very sure that your boss will not be taking the bullet for you when this happens, so CYA.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply