Using SQL Server AppRole 1

[dellyjm]

Hi,

can anyone give me a good reason why I should use SQL Server app role in my applications. I can see some benefits but it's miniscule.

Thanks.

Delton

http://www.jnbs.com

 

[hmscott]

I have one possible use for it that I am currently exploring. I have super users that add new users to the database by way of an admin module.

Add new users is not a problem, but when I want to add a new super user, I runn into problems trying to add the db roles "db_accessadmin" and "db_securityadmin". In order to add other users to these two roles, the user must be a db_owner or a sysadmin. I'm not willing to give these privileges to my super users. I am currently exploring the possibility of using an app role that has these privileges to temporarily "elevate" my super users while adding new super users.

It's not finalized, but conceptually it could work. Otherwise, I have not used the AppRole. Our security policies around here require a 1:1 relationship between users and logins (ie, no shared logins).

Regards,

hmscott

 

[gavinhuet]

Security is the short answer.

You can set up users who have no rights to any objects in your dayabase. They log in using the application. The application sets the application role within SQL Server. The application can then let the users see the data and use the objects.

You might think it is like creating a single user log-on, but that is incorrect, with this approach users can log on to the server using for example windows authentication - so you have nothing to maintain. They cannot do anything with the data unless they run the 'set application role' stored procedure and have the password. The only access rights are those for the application role.

Big drawback is when your application uses data controls. The standard MS data control will create its own connection to the database (you just give it the connection string), this connection string will be the users and not the application role, therefore the data control will not be able to return any data. What has to be done is the connection that has the application role set needs to get the data into a recordset which the data control will be set to reference.

I hope this explanation helps.
-- Gavin

 

[dellyjm]

Thanks guys I appreciate the responses and now have a MUCH better understanding of the use of the application role.

Thank you.

Delton

http://www.jnbs.com