locked
Grant access to db using AD groups through program but restrict access to individual users RRS feed

  • Question

  • Hi all, my first post here.

    I'm corrdinating a project at my work and I have no programming or database management experience however I do understand basic programming and database principles. So please bear wth me.

    We have this security requirement for our custom software and database.  The users log into the software with their Active Directory login which the software catches from the corp network.  The level of access for the software features are controlled by the software which looks at 3 groups which these users are organized into: Engineer, Supervisor, Production.

    We added these user groups under the Security>Users section of the database.

    This plan allows us to easily manage users to add/revoke access to the software/database while using their unique ID and password.

    So we want these users/groups to be able to access the database through the software but we don't want them to be able to install SQL Management Studio and browse the database/table contents.  How can we accomplish this?

    Right now in order for our users to be able to connect the software to the database, we have to grant all groups db_datareader.

    Wednesday, August 1, 2012 1:35 PM

Answers

  • To achieve this level of security, you need a three-tier architecture of some sort. If the users have an application running on their desktop, you have few means of preventing users from logging into SQL Server from Excel, SSMS or whatnots.

    You can use an application role as suggested in one post, but the password for the application is in the client, which a clever user can find. (Although it will keep out the users who are benevolent but only too smart for themselves.)

    With a middle tier, there are several options to deal with the situation. For instance, you can configure the network so that users cannot access the SQL Server machine from their workstations.

    If you have a two-tier application, there is still a way to get a middle tier: put the application on a Terminal Server, and when users log in, they get directly to the application and have no way to get out.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by amber zhang Wednesday, August 8, 2012 6:22 AM
    Wednesday, August 1, 2012 10:02 PM

All replies

  • Very nice requriment from security point of view.

    You can try to use application role for this.

    Refer Link : - http://msdn.microsoft.com/en-us/library/ms190998.aspx


    Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

    Wednesday, August 1, 2012 2:38 PM
  • You should also consider making all access through stored procedures. That is, the users don't have access to SELECT from any tables. They only have access to EXECUTE a store procedure that SELECT's from the table. This can hide the existence of the tables. Now they only see procedures and must pass valid parameter information to the procedure in order EXECUTE it. This doesn't replace the purpose of the application role. The user could still connect with SSMS and execute the proc if they know the valid parameters. But this can be part of your strategy.

    Using a USER WITHOUT LOGIN is an additional option, described in SQL Server 2005 Security Best Practices - Operational and Administrative Tasks http://www.microsoft.com/technet/prodtechnol/sql/2005/sql2005secbestpract.mspx Permissions are only granted to the USER WITHOUT LOGIN and never the connecting user.

    Also, you mentioned connecting with SSMS. Don't forget that Microsoft Excel and Microsoft Access and practically every other application can connect with a valid login.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Wednesday, August 1, 2012 5:53 PM
  • To achieve this level of security, you need a three-tier architecture of some sort. If the users have an application running on their desktop, you have few means of preventing users from logging into SQL Server from Excel, SSMS or whatnots.

    You can use an application role as suggested in one post, but the password for the application is in the client, which a clever user can find. (Although it will keep out the users who are benevolent but only too smart for themselves.)

    With a middle tier, there are several options to deal with the situation. For instance, you can configure the network so that users cannot access the SQL Server machine from their workstations.

    If you have a two-tier application, there is still a way to get a middle tier: put the application on a Terminal Server, and when users log in, they get directly to the application and have no way to get out.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by amber zhang Wednesday, August 8, 2012 6:22 AM
    Wednesday, August 1, 2012 10:02 PM