none
SQL Server 2012 User Permission

    Question

  • Hi

         Currently we are trying SQL Server as future development if possible. I have question about what is the best approach to provide permissions to user. We can use window authentication or database login. for e.g. If we have created database login with user id: "appuser" and password is "appuser123". This user has read and write permission to all the tables in database called "studentdb". Now when user uses application and log-in using "appuser" and "appuser123" , we want to allow to change data in tables of "studentdb" through the application, but if user try to use sqlcmd with this credential we don't want the "appuser" to have WRITE (insert/update/delete) access to all the tables.

         So basically we want "appuser" user id permission to behave differently when it is logged through application and "sqlcmd" utitlity.

    One  approach we are trying "sp_setapprole", but do you thing same functionality can be achieved through different scenarios ?

    thanks in advance


    Drew

    Tuesday, July 02, 2013 1:17 PM

Answers

  • The standard advice for all versions of SQL is to use Windows authentication where possible, and only use SQL authentication if it's the only option (for instance websites in a non-ad environment).

    In terms of your example I don't think that's possible. Either the user has read/write permissions to a database / table or it doesn't, the method used to connect using it doesn't make a difference.

    I'd suggest what you need to look for is to ensure that which ever method used by the user to access the application, they can't determine what the password details are (how that's done would be determined on how your app works), so only the application itself has modify permissions to the database. If you wanted the users to be able to access the database via sqlcmd then grant them read permissions separately, preferably with all users in a user group and that group granted permission via Windows authentication. Then you get separation of permissions and can also see which specific users are connecting and accessing the database.

    Tuesday, July 02, 2013 6:43 PM

All replies

  • The standard advice for all versions of SQL is to use Windows authentication where possible, and only use SQL authentication if it's the only option (for instance websites in a non-ad environment).

    In terms of your example I don't think that's possible. Either the user has read/write permissions to a database / table or it doesn't, the method used to connect using it doesn't make a difference.

    I'd suggest what you need to look for is to ensure that which ever method used by the user to access the application, they can't determine what the password details are (how that's done would be determined on how your app works), so only the application itself has modify permissions to the database. If you wanted the users to be able to access the database via sqlcmd then grant them read permissions separately, preferably with all users in a user group and that group granted permission via Windows authentication. Then you get separation of permissions and can also see which specific users are connecting and accessing the database.

    Tuesday, July 02, 2013 6:43 PM
  • thanks for your reply. we want to make sure if user log-in in the application (using read/write permission) and in our case every user will know their password (doesn't matter it is window authentication or database log in ) because every application prompt user to enter server , database and log-in information. Only thing we want to make sure user can't log-in the database using utility like sqlcmd and delete/update/insert data intentionally.

    But I can see what you are proposing and I will mark your reply as Answered. may be we will end up using "application role" functionality of SQL Server 2012.


    Drew

    Wednesday, July 03, 2013 12:46 PM
  • It depends on the application you're using really and how it handles authentication.

    Most of the setups I've dealt with over the years have been web applications so this may not be typical for all, but all of them have been setup such that the user authenticates with the application, and then the application authenticates with SQL. There's no direct connection from the user to SQL so they don't need to know the database login details, only those details required to login to the website (either using their windows login or some form of asp login). The application has the SQL login credentials stored in the connection string for the application in the relevant file, eg global.asa, web.config etc.

    While certainly new functionality in 2012 might give more options, it's been possible to keep users and databases separated using this method since at least SQL 7 when I first encountered it. If it's an in-house web application you're using then it might be worth reviewing how it handles authentication, and it's it's a 3rd party application they might be able to advice on whether there are other options available that would resolve the issue for you.

    Wednesday, July 03, 2013 2:21 PM
  • thanks again. We are using Desktop applications and all application log-in by default is their log-in id to database. By keeping different log-in to database for each user gives us ability to perform audit trail on database by their log-in id. if we start using generic database log-in id (from config files) then we will loose ability to identify which actual user is logged-in currently.


    Drew

    Wednesday, July 03, 2013 2:28 PM