Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jun 2011
    Posts
    5

    Unanswered: Restricting DBA from accessing SQL DB objects

    Hi,

    I have a requirement to restrict everyone (including DBA) from accessing user defined sql server database objects (like tables/function/views/stored procedures triggers etc). No one should be able to select/modify/delete/drop/disable these database objects from any sql client. These should only be accessible from application. I searched on net and found following:
    1. logon triggers : this is a server level trigger which blocks the user (you can modify to block dba well) to logon on the database server. But the same server may have other application database as well and using this user wont be able to login to other db as well. so this is out of scope.
    2. DENY permission: as per my R&D results, we cant apply deny permission on sa / dba. let me know if this is possible and how.
    3. DB level trigger: we can create database level triggers and roll back the action if any of above mentioned event occur. but DBA can disable the trigger can can access db objects after that.
    4. sp_addextendedproperty: using this stored procedure, we can hide user defined tables in SSMSE. but no control over command line. The DBA can login through sql prompt and can execute commands to view table structure and other objects.

    Does anyone have any idea on how to restrict any user (especially DBA) from accessing sql server database objects.


    PS: I am using SQL Server 2005/2008.

    Thanks,
    Bhawna

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Does anyone have any idea on how to restrict any user (especially DBA) from accessing sql server database objects.
    It seems to me you are trying to solve an organizational problem - untrustworthy / incapable / playful / ... DBA(s) - with technology. Has your organisation ever considered to tackle the root of the problem?

    Here we consider the DBA as the person everyone can go to to solve the problems caused by software / hardware / people and to prevent them from happening.
    How is (s)he regarded in your organisation? As the single source of all database problems? And is that only a perception or is it real?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jun 2011
    Posts
    5
    the application has to be deployed at client end and we don't want client to know our database structure or application logic. We are a product based company and so its for the security of the product.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I have seen this sort of question before. If you want total control of the database objects, you need to offer the database as a service, rather than something installed at the client. Look up Software As A Service (SAAS).

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Once you turn over your code to run on hardware that you don't own, you can't protect it this way. You can make the application sufficiently difficult to "crack" that no aministrator will make the effort to do so, but odds are good that in that process you'll also make the application so difficult/noxious to use that no user will use it.

    SAAS is the best way to make your application available to your customers and also keep your algorithm, schema, etc safe. There might be another answer, but I have not seen even a hint of how that might be done.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I don't think there is anything like that on any DB platform. DB2 just recently introduced a SECADM, this prevents a DBA from accessing the data within a table. Though that still isn't what you are looking for.
    Dave Nance

  7. #7
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    My humble opinion: If you don't trust the administrators of a system, your data (or code) should not be placed on it. After all, those are the ones responsible for keeping your data available and code running.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  8. #8
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    You mean there are people who would just carelessly DROP, ALTER, ... tables, triggers, SP's, UDF's, ... in database that comes with an external application?

    Isn't that about the same as me buying a car, opening the motor cover loosening some screws, pulling some wires, pouring gasoline in a vessel that states "BRAKE FLUID" and then have to call and pay the repair man?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  9. #9
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    As DBA , I can sincerely tell that I don't carelessly drop any objects, and I have not come across any DBA which has done it. It's as bad for an DBA to carelessly drop objects as it is for a sysadmin to carelessly delete folders. If I had experienced a DBA just dropping objects, I would most likely have tried to got him removed from his position.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  10. #10
    Join Date
    Jun 2011
    Posts
    5
    I agree that a DBA just cannot alter/drop any object carelessly and i didn't even meant that. Our requirement is to hide the database structure from client/DBA. We don't want the client to see our code or our database. It will be very helpful if anyone can tell me if this is possible or not. Till now i have browsed 100s of sites but wasn't able to find any solution yet. Kindly help.

  11. #11
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4

    Thumbs up

    I do know that in some companies the program and database code is renamed before shipping:

    CREATE TABLE Purchase(
    PersonId Int NOT NULL,
    ProductId Int NOT NULL,
    UnitPrice DEC(10,2) NOT NULL,
    ...


    Gets translated into

    CREATE TABLE Table001(
    Column001 Int NOT NULL,
    Column002 Int NOT NULL,
    Column003 DEC(10,2) NOT NULL,
    ...


    So it becomes a real challenge to understand the structure of the data model.

    I don't think that is the way to go. SAAS seems to be a good solution, though I have never used it myself.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  12. #12
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    As Wim says, renaming objects (aka security by obscurity) is the option that you've got, combined with encryption of stored procs/views/functions/triggers. Keep in mind though, that even if you can encrypt the procs, the execution plan can still be retrieved, and it will most likely give a pretty good view of what the code is doing.

    That said, if you find that your data model should be kept this secret, you should really consider hosting the database (or even the entire application) for your customers instead, but this brings up another interesting question: Is your data model more secret than your customers data?
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  13. #13
    Join Date
    Jun 2011
    Posts
    5
    Can we encrypt table structure in Sql server. Encryption should be password based and admin should not be able to decrypt it without that password. if yes then how?

  14. #14
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Quote Originally Posted by bhawnajain View Post
    Can we encrypt table structure in Sql server. Encryption should be password based and admin should not be able to decrypt it without that password. if yes then how?
    No .

  15. #15
    Join Date
    Jul 2011
    Posts
    1

    Same Question

    Hi Bhavana,
    Even I am looking an answer for the same question. Can we not use sp_helprotect to do the same? i.e. not giving any permission to DBA for the respective tables/objects? I have seen such security in Unix where we can restrict even the root/owner of a file to have read/write/execute access. I am not sure if that can be done in MS SQL. Have you received any solution for this?
    Thanks,
    Nupur

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •