When to use an application role in a database?

Question

What things should I consider when choosing between database roles and application roles in SQL Server?

I understand how each type of role works, but I'm struggling to understand when an application role would be appropriate to use over a database role.

Up until now, I've just used database roles in my application. I've basically created an account for each user and then assigned them to whatever roles they need access to using GRANT statements. The application in question is just a simple data-entry/reporting application.

Would there be any benefit on using application roles and if so, how do I go about using them? Specifically how do I ensure that a given user cannot access objects they shouldn't be able to. Do I need multiple application roles or is there some other way?

Answer 1

The stated purpose of application roles is to attach a set of security to a 'application'. The idea being that the approle password is only known to the application, and is actually defined within it (in a suitably obfuscated way)

If you have a user with a fixed database security levels that needs different security levels when using a particular application then you might want to use an application role. Otherwise don't bother

For example Joe logs into the database using his database credentials but should only read (not change) data in a table

Joe uses the same database through the financial application. The application happens to connect using the same credentials (Joe)

However through the application he also needs to be also allowed to insert/update a table.

In this case you would use an application role to override Joe's security by calling sp_setapprole in the application

(Joe could could actually call this sp himself if he knew what the password was... but he shouldn't know it)

The application would have some validation in the front end stopping him doing silly things to the data.

It's a very specific case.

Answer 2

Application roles are useful when:

• a database is shared between >1 application, and
• the same user might use more than 1 application, and
• either application has data that is 'particular to that application', and
• it doesn't make sense to grant the user access to that data (because they might mess around with it from a different application), and
• it doesn't make sense to give the application its own login to maintain that data

Noting that:

• The above situation is only going to arise in an enterprise, where applications come and go, but data is forever, and access-control decisions are 'pushed down to the database' as much as possible, using database roles. In that situation, the strategy of 'summarizing' a person's access to data in terms of what applications they can use isn't going to be enough.
• Data that is intended to be accessed by different applications in terms of the user-identity would NOT be protected behind an 'application role'.
• It almost always would be fine to give the application its own login for the purposes of maintaining its own data. Two exceptions might be: ** where auditing or access-control is performed by database-level triggers, and these triggers needed to distinguish between the currently-executing application role and the user which invoked the application role; or ** where a very severe security policy didn't allow the application to have a login of its own, but could only ever act in the database in the broader context of a particular user

In what unearthly realm would application roles ever be needed? One modern (2020-ish) use-case is storing OAuth tokens and refresh-tokens. These need to be stored in the database, but the 'grant' has been given specifically to one application ... not to all the applications which access the database. To allow the app to manage its own app1.oauth_tokens table and NOT give permission on the app1.oauth_tokens table to the user when they're accessing the database through another application, an application role is a possible strategy.

It's a shame the application role feature wasn't better thought through: it would have been nice to express an access-control like: "users in group A can view that data; users in group A who are accessing it from app X can additionally modify it". But that's not what we get :-(

Answer 3

Usually when we create application role, they are for those applications who liked to manage their own security and wanted to be independent of SQL Server security and we would like them to access certain tables in the database may be not all the objects. These are for static applications where User comes and do some stuff and that user will use these application role to make changes in database