2

I have been researching the use of Application Roles within Microsoft SQL Server and was wondering if there is a way to limit a user to only be able to set specific application roles. As I understand, any user of the database with sys.sp_setapprole execute permissions can set any application role they choose, as long as they provide the application role name and password. To me this seems like a trade-off in security.

For example, let's say John works in Accounting and uses an application named Accounting.exe, which is hard-coded to set the Accounting_appRole once connected to the AdventureWorks Database. Bill works in Finance and uses an application named Finance.exe, which is hard-coded to set the Finance_appRole once connected to the AdventureWorks Database. Both John and Bill must, at a minimum, have execute permissions for the sys.sp_setapprole stored procedure of the AdventureWorks Database. Therefore, if John from accounting e-mails the Accounting.exe application to Bill in finance, Bill can execute the Accounting.exe application without issue, which is undesired.

I think there should be a way to limit who can set a specific application role, does anyone know of a way? All I can come up with is modifying the sp_setapprole stored procedure to use SUSER_SNAME() to limit the value(s) of @rolename that can be used.

2
  • 1
    This is off topic and belongs on dba.se
    – Kermit
    Apr 9 '13 at 15:35
  • Sorry, @FreshPrinceOfSO I requested that it be moved.
    – CanMan
    Apr 9 '13 at 18:56
0

I wouldn't recommend messing with the system stored procs. You could wrap the sp_setapprole call in a stored proc and have your app call that proc instead. The wrapper proc would have the password hard-coded and use EXECUTE AS to impersonate a user who can use sp_setapprole. Then you could limit access to the wrapper proc. Another advantage to this approach is that the password doesn't go out across the network, nor is it included in your .exe file.

EDIT:

My mistake, you can't wrap the call directly. But you can wrap a modified version of the T-SQL that it runs. Looking at the version in my 2008r2 server, I see that the proc calls SETUSER intead of EXECUTE AS. Sad to see deprecated code in system stored procs. For yours, you may want to use EXECUTE AS instead.

You could also grant rights on whatever your app needs to function to a SQL user who can't login, give certain users rights to impersonate that user, and have your app EXECUTE AS the login-less user. This route still requires cookie handling to set the context back. In fact, it's very similar to a customized version of sp_setapprole, since both require some sort of impersonation.

Or, you could also create a database role and grant the role to authorized users. This seems like it might be more like what you want. Application roles are designed for app-defined rights. It seems like you really want user-defined rights.

3
  • I agree, modifying the sys sproc is not a good idea. Not sure about the wrapper yet, seems like yet another layer of complexity. Especially trying to handle the cookie for unsetapprole in code.
    – CanMan
    Apr 9 '13 at 19:17
  • Just tried calling the sp_setapprole from another stored procedure, received the Error: Application Roles Can Only Be Activated at the Ad Hoc Level. This is because the sp_setapprole does a if (@@nestlevel > 1) check. See: support.microsoft.com/kb/310824
    – CanMan
    Apr 9 '13 at 20:14
  • The EXECUTE AS with a login-less user seems to be a sufficient solution since you can grant impersonate of that user to specific users. I am trying to limit/hinder a users privileges in a way that requires them to access the database through my app and only my app, or makes it very hard to do it any other way, while still using Windows Authentication.
    – CanMan
    Apr 10 '13 at 4:00
0

The idea of an application role is that the privileges and the secret are given to the application that the user is using. The user only potentially gains those privileges because they're using that application. Any access-control decisions among users are now the responsibility of the application, not the database. Which is the problem as you have described it.

So you need to think more deeply about what privileges you want the application to have, (contingent upon the ability of the CONNECT and EXECUTE permissions granted to the user who owns the connection).

There are probably not very many privileges in the database which you'll deny to a user, but wish to allow to the application which the user is using, despite the user themselves not having such access.

A good example is access to OAuth tokens. In the OAuth scheme, the tokens are issued to a 'piece of software' (called the client in OAuth terminology). These data are truly the property of the 'piece of software' itself, and not really the property of 'the users of the software'.

Consider application-roles a way for the application to keep application-private-state in the database, which is only accessible to users when they're using that application. Don't consider application-roles as a technique to apply access-control policies which need to distinguish among users.

Then you won't encounter the scenario you described, at all.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.