asked
Our Group Security dictates that personal information is classified as highly confidential, and as such, the regular DBA performing normal database admin tasks should be prevented from reading data in the database containing that data??
How we can make it possible ?? DBA should be able to manage the SQL server without having access to particular database.
Just add the DBA in question to the db\_denydatareader database role and also to the db\_denydatawriter role (just in case). This way, the DBA will still be able to perform normal admin tasks but will not be able to read the sensitive data. Once the roles are granted, you will still need to add the audit to prevent the DBA in question from temporary removing this membership (and adding it back in after the data is read) and/or from accessing the data in the database tables as sa. The script to grant the membership: alter role db_denydatareader add member [dba_user_name]; go alter role db_denydatawriter add member [dba_user_name]; go Oleg
If I had to work in the environment where I had to maintain the databases while unable to read the data, I would certainly start looking where to apply next. If the data is either encrypted or DBA is a member of the role preventing them from reading it then how are they supposed to help to figure out the problems of the "data does not look right" kind?
Agreed with the exceptions of securityadmin & dbcreator. With securityadmin, they can elevate themselves to sysadmin so I would be cautious with that one. I'd also be cautious with dbcreator as that would place them (by default) into the 'dbo' role upon database creation. This in turn would by-pass the explicit deny of the db_denydatareader/writer roles.They could also just remove themselves from that role as dbo. As long as the database owner (and they aren't placed in the fixed DB role) is updated to some other login then the DBA's after initial creation, then the explicit deny would function as expected. Of course, we don't know what role (if any) the DBA is in so this might be all moot at this point! ;-) Personally, I think that if you can't trust the DBA, then you need to find a different DBA. ;-)
@archana13.s Please read the comments above, they include the role list (in my comment) and the concern raised by @JohnM regarding the securityadmin role. This role membership is needed so that DBA can add/remove permissions for other database users. The concern is that the DBA who has securityadmin role membership technically can add him/herself back to the sysadmin role which will then bypass the originally set deny reader/writer restrictions. The issue can be easily mitigated by the audit and/or simple agreement. DBA is your friend, not your enemy. If the DBA is told not to add own account back to sysadmin role then he or she will not do it, that is all. For sanity/formality check the role memberships can be easily audited if need to be.
@JohnM True, thank you for pointing it out, but if the user in question is granted any other server role or all of them except the sysadmin, which would be more than plenty to administer any database, then it will work just fine. I believe that serveradmin + securityadmin + setupadmin + dbcreator + bulkadmin (for openrowsets and bulk inserts) gives more than enough for admin tasks while still denying the select permissions to any tables.
Requirement is to disable sa and prevent the DBA to read the data from database having sysadmin permission. The application is highly critical and they don't want to use encryption because of performance issues.
If the DBA isn't going to be removed from the sysadmin server role, then I think your only option is to encrypt the data from the application layer. The DBA, I'm assuming, wouldn't have the ability to decrypt the data except through the application itself and you can control permissions to the application.
The database role db\_denydatareader is not a joke, it was designed and exists specifically for this reason, to deprive someone from the permission to read the data without affecting the user's other permissions. If encrypting the data is not an option then the solution suggested in the answer above, coupled with @JohnM recommendations and some audit if needed, will work, provided that the DBA is not a member of sysadmin server role but is a member of some other combinations of the server roles which allow full access to any administrative tasks.
we are ok to remove DBA's from sysadmin role but need to know the other combination of server roles which will allow them to perform the all administrative tasks as well as manage the server with reading the data from one user database on the sql instance.
What kind of application is connecting to the DB? There is a way, if it is C# at least, to encrypt the data through the app and nobody can read it if they don't have access to the app.
It looks like OP is looking for a possibility to prevent the DBA from reading the data using SSMS while still allowing whatever admin tasks to take place. Outside of explicitly issuing DENY to SELECT for every table, the db\_denydatareader role membership might do the trick.
22 People are following this question.
