locked
application roles RRS feed

  • Question

  • I have a web application to access MS SQL Server for back end database server.

    I would like to know it is right track to have application role create and using this application role to  connect MS SQL Server.

    If I am correct, should I assign members to this role and only this role's member can access the database?

    If yes, I use Studio Management to configure the application role, on the general tab, it says owned schema which are all users ID, is it the way to assign members to this application role?

    Your information and help is great appreciated,

    Regards,

    Sourises,

    Tuesday, May 31, 2016 5:56 PM

Answers

  • Application roles do not have members.

    An application is something you activate with sp_setapprole together with a password. The idea is that users on their own do not have permissions; these have only been granted to the application role. The call to sp_setapprole is typically compiled into the application together with the password which is not unveiled for the users. ...however, if the application runs from their own machine, they can analyse the executable to find the password, so this is only security by obscurity.

    On the other hand, it seems that you are asking for a plain database role. You create such a role with CREATE ROLE, and you add members to it with ALTER ROLE ADD MEMBER. (Introduced in SQL 2012, on earlier versions you use sp_addrolemember). Using roles and assigning permissions to the roles rather than directly to users is good practice as it makes it simpler to manage permissions.

    Tuesday, May 31, 2016 9:07 PM
  • If you have a web application, and application role works better than with a Windows app, since the password for the application role can be stored on the web server, out of reach for the users.

    So with an application role, users can login with the IDs and have very limited permissions, but through the application they can get all powers they need. You can still tell in the database which user you have.

    But if you are to replace user logins with a single login ID that is hard-coded into the web app, you lose the above(*). There is also no longer any need for the application role.

    (*) But it can be solved by other means, for instance SET CONTEXT_INFO.

    • Marked as answer by sourises Friday, June 3, 2016 1:31 PM
    Thursday, June 2, 2016 9:17 PM

All replies

  • Application roles do not have members.

    An application is something you activate with sp_setapprole together with a password. The idea is that users on their own do not have permissions; these have only been granted to the application role. The call to sp_setapprole is typically compiled into the application together with the password which is not unveiled for the users. ...however, if the application runs from their own machine, they can analyse the executable to find the password, so this is only security by obscurity.

    On the other hand, it seems that you are asking for a plain database role. You create such a role with CREATE ROLE, and you add members to it with ALTER ROLE ADD MEMBER. (Introduced in SQL 2012, on earlier versions you use sp_addrolemember). Using roles and assigning permissions to the roles rather than directly to users is good practice as it makes it simpler to manage permissions.

    Tuesday, May 31, 2016 9:07 PM
  • Thanks for the information and help, yes, I understand.

    For some reason, I am asked to convert one database using database roles to one login ID for the whole group.

    My question is if this is the case, is application role a good candidate for one ID for the application?

    Does this method can apply to web application, because I need a connection string between web server and database server and handle user interface from web application.

    Thanks again for your help and information,

    Regards,

    Sourises,


    • Edited by sourises Wednesday, June 1, 2016 10:11 PM
    Wednesday, June 1, 2016 10:02 PM
  • If you have a web application, and application role works better than with a Windows app, since the password for the application role can be stored on the web server, out of reach for the users.

    So with an application role, users can login with the IDs and have very limited permissions, but through the application they can get all powers they need. You can still tell in the database which user you have.

    But if you are to replace user logins with a single login ID that is hard-coded into the web app, you lose the above(*). There is also no longer any need for the application role.

    (*) But it can be solved by other means, for instance SET CONTEXT_INFO.

    • Marked as answer by sourises Friday, June 3, 2016 1:31 PM
    Thursday, June 2, 2016 9:17 PM