locked
block dba access to user data RRS feed

  • Question

  • Is there a way in sql 2008 to block a dba's access to user data? I think the capability exists. however I cant seem to find any documentation on it.

     

    thanks


    Shahab
    Thursday, July 22, 2010 3:50 PM

Answers

  • Hi,

    The DBA has in most cases been assigned sa (system administrator - highest permission) , so there is nothing you can restrict from a permissions point of view.

    you could however encrypt the data and therefore block access that way.


    Jon
    • Proposed as answer by Tom Li - MSFT Tuesday, July 27, 2010 4:32 AM
    • Unproposed as answer by Tom Li - MSFT Sunday, August 1, 2010 8:36 AM
    • Marked as answer by Tom Li - MSFT Sunday, August 1, 2010 8:36 AM
    Thursday, July 22, 2010 5:12 PM
  • It depends what you mean by DBA. SQL Server itself does not use that term. It uses the term DBO and database_owner.

    But usually when people talk about a DBA, they are talking about a SQL Server System Administrator, which is someone who logs in with the name 'sa', or is in the sysadmin server role. Anyone who meets these conditions has full control over everything in the server, and permisssions cannot be restricted.

    You might be able to do something like encrypt the data before it ever gets to SQL Server, so a sysadmin can't see interpret it, but that's not the same as blocking access to the data.


    HTH, Kalen Delaney www.SQLServerInternals.com
    • Proposed as answer by Tom Li - MSFT Tuesday, July 27, 2010 4:33 AM
    • Marked as answer by Tom Li - MSFT Sunday, August 1, 2010 8:37 AM
    Friday, July 23, 2010 12:45 AM
  • The only way to block access to the data is to as Kalen said, encrypt it before it ever gets to SQL Server, but then all your queries would have to work against encrypted data and there would be a performance impact, or you keep them out of the sysadmin fixed server role as well as the local administrators group on the server, and the Domain Administrators group in Active Directory, and then enabling Transparent Data Encryption for the database.  Why would you have to go to such extremes?  A sysadmin can backup the database and restore it elsewhere to access the data, if they aren't in the sysadmin role, but are a local admin or domain admin, they can restart SQL Server in single user mode and gain access as a sysadmin in SQL and get to the data, and TDE encrypts the data at rest and makes it so someone can't just take a backup file or the database files to a different server to gain access to the data.

    Most places that have the kind of restrictions that prevent a DBA from accessing user data have Auditing happening on the systems, for example a server side SQL Trace that starts when the SQL Server starts, on in SQL Server 2008 the Server Audit Feature.  Auditing monitors for access to the data, and can be configured such that if the audit stops so does SQL Server.  I have audits on multiple servers that monitor for data changes by accounts that aren't the application service account. 

    If you don't trust the DBA, they shouldn't be your DBA.  If you are a software vendor and want to keep your schema, and whatever else in your database a secret, don't distribute it, offer your product as a Software As a Service only, or host the database in SQL Azure or another cloud database platform.


    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    • Proposed as answer by Tom Li - MSFT Tuesday, July 27, 2010 4:33 AM
    • Marked as answer by Tom Li - MSFT Sunday, August 1, 2010 8:37 AM
    Friday, July 23, 2010 2:01 AM

All replies

  • Hi,

    The DBA has in most cases been assigned sa (system administrator - highest permission) , so there is nothing you can restrict from a permissions point of view.

    you could however encrypt the data and therefore block access that way.


    Jon
    • Proposed as answer by Tom Li - MSFT Tuesday, July 27, 2010 4:32 AM
    • Unproposed as answer by Tom Li - MSFT Sunday, August 1, 2010 8:36 AM
    • Marked as answer by Tom Li - MSFT Sunday, August 1, 2010 8:36 AM
    Thursday, July 22, 2010 5:12 PM
  • And to add to Jons suggestion you could activate auditing for that database / databases as well and then you know if data has been tampered with.

     

    - Raoul

    Thursday, July 22, 2010 8:28 PM
  • It depends what you mean by DBA. SQL Server itself does not use that term. It uses the term DBO and database_owner.

    But usually when people talk about a DBA, they are talking about a SQL Server System Administrator, which is someone who logs in with the name 'sa', or is in the sysadmin server role. Anyone who meets these conditions has full control over everything in the server, and permisssions cannot be restricted.

    You might be able to do something like encrypt the data before it ever gets to SQL Server, so a sysadmin can't see interpret it, but that's not the same as blocking access to the data.


    HTH, Kalen Delaney www.SQLServerInternals.com
    • Proposed as answer by Tom Li - MSFT Tuesday, July 27, 2010 4:33 AM
    • Marked as answer by Tom Li - MSFT Sunday, August 1, 2010 8:37 AM
    Friday, July 23, 2010 12:45 AM
  • The only way to block access to the data is to as Kalen said, encrypt it before it ever gets to SQL Server, but then all your queries would have to work against encrypted data and there would be a performance impact, or you keep them out of the sysadmin fixed server role as well as the local administrators group on the server, and the Domain Administrators group in Active Directory, and then enabling Transparent Data Encryption for the database.  Why would you have to go to such extremes?  A sysadmin can backup the database and restore it elsewhere to access the data, if they aren't in the sysadmin role, but are a local admin or domain admin, they can restart SQL Server in single user mode and gain access as a sysadmin in SQL and get to the data, and TDE encrypts the data at rest and makes it so someone can't just take a backup file or the database files to a different server to gain access to the data.

    Most places that have the kind of restrictions that prevent a DBA from accessing user data have Auditing happening on the systems, for example a server side SQL Trace that starts when the SQL Server starts, on in SQL Server 2008 the Server Audit Feature.  Auditing monitors for access to the data, and can be configured such that if the audit stops so does SQL Server.  I have audits on multiple servers that monitor for data changes by accounts that aren't the application service account. 

    If you don't trust the DBA, they shouldn't be your DBA.  If you are a software vendor and want to keep your schema, and whatever else in your database a secret, don't distribute it, offer your product as a Software As a Service only, or host the database in SQL Azure or another cloud database platform.


    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    • Proposed as answer by Tom Li - MSFT Tuesday, July 27, 2010 4:33 AM
    • Marked as answer by Tom Li - MSFT Sunday, August 1, 2010 8:37 AM
    Friday, July 23, 2010 2:01 AM
  • There are work around like advised earlier in the responses like "Auditing". However even if you enable "Encryption" using TDE members of the "sysadmin" can access the data.

    Moreover why is this requirement of blocking "DBA" raise? Please can you elaborate.

     As Jonathan pointed out, Are you not trusting your DBA?


    Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!
    • Proposed as answer by Tom Li - MSFT Tuesday, July 27, 2010 4:34 AM
    Friday, July 23, 2010 9:53 AM
  • I am my own development and deploy dba... I am testing a filestream feature and I need for a few minutes, block my own user from accessing to the database just for testing the impersonation access (it's a web application that impersonates into a local account to get access for the filestream purposes).

    Of course, I don't trust myself, that's the only way to get an installation to go perfect (or almost perfect) when it's sent to production servers... Otherwise everything goes OK with my account but when it's installed on production servers it's a configuration nightmare...

     

    What do you think? May I block myself from the database server? (and, of course, getting me in again without any third-party intervention).


    Ing Rosell
    Monday, October 4, 2010 2:35 PM