How to Restrict DBA from Read/Write But Still can perform Maintenance tasks
Hi All, I recently got an requirement from Client for Migration/Regular DBA Support of SQL Server Production Servers. BUT they asked to HIDE data from OFFSHORE DBA. Onsite DBA will have access for Emergency Work.I thought of below option: -> NO SYSADMIN but db_owner with db_denyread and db_denywrite. Explicit Access on msdb, master to perform DBA tasks like Profiler, Job Execution and Security tasks.. Can you advise how to implement this so that Offshore should be able to handle regular Maintenance but CANNOT see Data inside DBs. Any Internal Tool from Microsoft OR any 3rd Party Tool..??
This is the "who watches the watchmen" problem, and the quick answer is that it would be difficult, if not impossible, to make it so that a DBA who is actually supposed to perform administrative tasks (particularly security-related) on a SQL Server instance is unable to read from a table in a database to which he has access. Even if you give `db_denyread` and `db_denywrite`, the user is a `db_owner` and thus can change his own rights. Also, `db_owners` are allowed to execute as other users, so he could switch to a user with the necessary rights and perform select/insert statements. One viable solution could be to create a user-defined database role and grant the explicit permissions necessary (e.g., create index) per-database, denying reading and writing rights on tables. Put that into the model database as well so new databases get that role. Then, add the offshore DBA to that role. It's more work and will definitely be an iterative approach, determining exactly which rights are necessary. Unfortunately, even that solution could fail: if the DBA is allowed to alter users and grant rights, he would also be able to alter his own account and grant himself rights. To get around that problem, you'd probably have to create some process (perhaps an application) which the offshore DBA could use to create/modify users and logins. That application would connect using an account with the securityadmin role (or `db_accessadmin` on each database), and the application would know to filter out certain users like the offshore DBA. But a determined insider could get around that, too. Presumably, the organziation will have SQL Agent jobs or some other scheduled tasks to perform actions. If there's a proxy account that can read from or write to tables, the offshore DBA could run a job using that proxy account and dump data into tempdb--you are giving him rights to msdb, after all. I was also thinking about using a trace or extended event to log table access by the DBA's account. You might be able to combine that with explicit permissions to build in enough controls at least to mitigate the problem. The trace would log any access and then internal security could deal with the issue, rather than trying to lock down the administrator's access perfectly.
**Permissions** This is extremely difficult. As Kevin said, you can explicitly assign just the permissions the "DBA" needs for the maintenance tasks, but you have to be very careful not to assign them permissions they can use to escalate their own permissions. And if you keep the permissions very narrow, then you can probably replace this "DBA" with a few shelll scripts. Also remember that if they can take a backup then (unless you have carefully structured things on the OS side as well) they can take that backup, copy it to a server that they own, restore it and read to their heart's content. **TDE** One way you can come close is through properly set up transparent data encryption. This requires enterprise edition and is difficult to set up properly (and remember to have a backup of the key...) But if you do set it up properly, then you can give someone full ownership, but not the key, and then they will be unable to even read the data. That way they could do all administrative tasks (since they have full permissions) but not read or alter the data meaningfully (because they can't encrypt or decrypt it without the key). Of course, that does not mean that the DBA cannot cause mischief if they were malicious (or goof things up accidentally for that matter). They can still see the structure, still change the structure, delete data, change users, etc. They can also still set up a trace and get all that this reveals... **Some Client Encryption** Another approach, especially if you are only trying to protect a couple of columns, is to have the client application do the encrypting and decrypting. This way you could, for instance, keep the DBA from being able to read or change the salary column in a moment of idle curiosity, but he would still have the permissions needed to do real work. **A Reminder** The more you limit your DBA, the less they can do for you. Just for obvious examples: If they can't read the structure, they can't help you change it or index it. If they can't read the data, they can't help troubleshoot a lot of application problems. I have seen instances of restricting DBAs from one or two sensitive columns and/or tables, but even there most of those were done mostly to make a merely curious DBA think twice. They knew quite well that a determined DBA could get around those restrictions. The technical restrictions were more a reminder that he had been told not to peek than a real barrier. And if you are restricting access to much more than a couple of sensitive columns then you are hamstringing your DBA and he won't be much good to you. The bottom line is that if you can't trust your DBA, find a different DBA. [[Edit: If you are trying to protect passwords in particular, then I would recommend never storing passwords in a way anyone could ever get the plaintext back. Rather salt them, hash them, and throw away the plaintext once you've done that. Its easy enough to verify if the password matches later without ever holding the plaintext longer than it takes to hash it. SQL provides some limited ability to do that built in, but generally you should do it client side or in middle-ware.]]
In our case the background is EXPORT CONTROL regulations in one one country, now they are also asking for an auditing (what all are done by offshore DBA) / tracing solution also . Do we have standard windows batch programs avaiable to connect to database and execure commands , Like Unix wrapper script which can connect to oracle and exceute SQL commands or PL/SQL code . If there are standard programs , we could edit it to make additional entry for one such execution ?.