locked
Deny dbo access to data RRS feed

  • Question

  • Hi!

    I am wondering: what is the point of application roles?

    The thing is like this: I am writing a client application in .NET. I want data access limited to that application only. But I don't want a server administrator be able to access the database data.

    I have seen the concept of rules and users, schemas and logins. But why are they even there, if any simple Windows admin has acces to my data? Or, is there a way to limit data acces to system administrators?

    Hope my question is clear enough.

    Tuesday, November 30, 2010 4:54 PM

Answers

  • The idea, is that the database can be divided into areas that naturally occur in the work unit. For example, the Human Resources staff needs to access everything related to employees. The Engineering team needs tables, views, stored procedures related to product design, and the production staff needs to access tables, views, procs related to creating the product. Then you can just grant access to a schema and they will get access to all the tables, views, procs necessary to complete their work. At least all the production staff might have SELECT and EXECUTE permission to the schema (and all it's contained objects) so production staff can read all data and execute all stored procedures. CONTROL permission would probably be more restricted.

    Similary, you should group the users into roles. These could be Windows groups, or could be a user-defined database role. Then you don't have to grant a new person any permissions. Just make them a member of the Windows group or database role. And since you grant the group or role the appropriate permission to the schema, the new employee gets all the necessary permission. And there is nothing wrong with being a member of multiple roles. The production manager would be a member of the Production role, and also a member of the Managers role. They would get the permissions of both. 

    There is nothing wrong with applying more granular permission to indivuals and specific objects, but in work unit with many employees, and with a database with many 100's of tables, views, and procs, that isn't very practical.

    And if you have a small database devoted to one purpose, one schema is fine. It could be the default dbo schema, but even a small database might benefit by separating tables, views, and procs, into the data exposed to customers (a CustomerData schema) and data you use for managing the application (an AppData schema). Or perhaps frequently updated tables (such as the orders table), and rarely updated tables (such as the products table).

    Schemas are more important in large organizations where you create the system, and 2 years later someone you never met has to maintain the system.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Tuesday, November 30, 2010 9:35 PM

All replies

  • Application roles are not intended to limit access by members of the sysadmin server role. Application roles were a way to require (non-admin) users to access the data through a particular application. The whitepaper SQL Server 2005 Security Best Practices - Operational and Administrative Tasks recommends using a USER WITHOUT LOGIN for that purpose instead.

    As for sysadmins and members of the local administrators group, the general principle is that whoever owns the computer gets to decide how it is used. If you are trying to protect your intellectual property, use a license agreement. If you are in a high security enviroment, you can protect your data by using Extensible Key Management, and SQL Server Auditing. But as you must physically control the compuer, that's probably not relevant in your case.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Tuesday, November 30, 2010 6:33 PM
  • Ok, I understand this.

    But still, I find it strange that whoever is a system administrator has access to my database data and is able to manually change the content of the database. This can, after all, make the application unstable.

    Moreover, consider the case when an app stores some data in a database, an app that is used by normal users. The database is stored on their local computer. This means that whoever uses the application, has access to the database architecture and design (considering that in most cases a user has administrative rights in his computer).

    In this case, I don't understand the point of complex security systems since there is no way of preventing access to the database...

    Tuesday, November 30, 2010 7:24 PM
  • The alternative (to administrator control) is that the owner of a computer wouldn't be able control their own computer. In a corporate situation, if the developer get's hit by a bus, the administrator of the computer must be able to recover the system. In a private situation, just because I buy an app, doesn't mean some developer who I don't know can store what ever they want on my computer. Of course the developer has legal rights to their intellectual property, but that's a legal enforcement mechanism (the license). You are free to disagree with this principle, but that's the current norm.

    And as a practical matter, if you control the computer, you could install all kinds of hacking tools, such as directly reading the memory, so you can't prevent someone with legitimate physical control of the computer from grabbing the data anyway. (Disclaimer; I'm not an expert on that.) If a computer is stolen, then encryption mechanisms like BitLocker, NTFS permissions, and Transparent Data Encryption can block access. (Except many of my relatives don't use a password for their computer logins anyway. <sigh>)


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Tuesday, November 30, 2010 7:43 PM
  • Ok, thanks for your replies.

    Currently, I feel I miss understanding the point of all of these security systems.

    You suggested that what I ask for is taking the control of a system from the system administrator. This is not the case. What I need is having my application's database safe from anyone, even the administrator, who shouldn't be sniffing around the databases's data or architecture. What's so wrong in asking this, anyway?

    But let's take it the other way around.

    Could you please explain me the use of schemas, first? For example, AdventureWorks has some custom schemas: Person, Production, Purchasing and Sales. Why are they useful? Why not have all the tables in the same schema (dbo)?

    Thanks!

    Tuesday, November 30, 2010 8:30 PM
  • The idea, is that the database can be divided into areas that naturally occur in the work unit. For example, the Human Resources staff needs to access everything related to employees. The Engineering team needs tables, views, stored procedures related to product design, and the production staff needs to access tables, views, procs related to creating the product. Then you can just grant access to a schema and they will get access to all the tables, views, procs necessary to complete their work. At least all the production staff might have SELECT and EXECUTE permission to the schema (and all it's contained objects) so production staff can read all data and execute all stored procedures. CONTROL permission would probably be more restricted.

    Similary, you should group the users into roles. These could be Windows groups, or could be a user-defined database role. Then you don't have to grant a new person any permissions. Just make them a member of the Windows group or database role. And since you grant the group or role the appropriate permission to the schema, the new employee gets all the necessary permission. And there is nothing wrong with being a member of multiple roles. The production manager would be a member of the Production role, and also a member of the Managers role. They would get the permissions of both. 

    There is nothing wrong with applying more granular permission to indivuals and specific objects, but in work unit with many employees, and with a database with many 100's of tables, views, and procs, that isn't very practical.

    And if you have a small database devoted to one purpose, one schema is fine. It could be the default dbo schema, but even a small database might benefit by separating tables, views, and procs, into the data exposed to customers (a CustomerData schema) and data you use for managing the application (an AppData schema). Or perhaps frequently updated tables (such as the orders table), and rarely updated tables (such as the products table).

    Schemas are more important in large organizations where you create the system, and 2 years later someone you never met has to maintain the system.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Tuesday, November 30, 2010 9:35 PM