Creating Application Roles in SQL Server

  • Artikel

Application roles provide a way to assign permissions to an application instead of a database role or user. Users can connect to the database, activate the application role, and assume the permissions granted to the application. The permissions granted to the application role are in force for the duration of the connection.

Important

Application roles are activated when a client application supplies an application role name and a password in the connection string. They present a security vulnerability in a two-tier application because the password must be stored on the client computer. In a three-tier application, you can store the password so that it cannot be accessed by users of the application.

Application Role Features

Application roles have the following features:

  • Unlike database roles, application roles contain no members.

  • Application roles are activated when an application supplies the application role name and a password to the sp_setapprole system stored procedure.

  • The password must be stored on the client computer and supplied at run time; an application role cannot be activated from inside of SQL Server.

  • The password is not encrypted. The parameter password is stored as a one-way hash.

  • Once activated, permissions acquired through the application role remain in effect for the duration of the connection.

  • The application role inherits permissions granted to the public role.

  • If a member of the sysadmin fixed server role activates an application role, the security context switches to that of the application role for the duration of the connection.

  • If you create a guest account in a database that has an application role, you do not need to create a database user account for the application role or for any of the logins that invoke it. Application roles can directly access another database only if a guest account exists in the second database

  • Built-in functions that return login names, such as SYSTEM_USER, return the name of the login that invoked the application role. Built-in functions that return database user names return the name of the application role.

The Principle of Least Privilege

Application roles should be granted only required permissions in case the password is compromised. Permissions to the public role should be revoked in any database using an application role. Disable the guest account in any database you do not want callers of the application role to have access to.

Application Role Enhancements

The execution context can be switched back to the original caller after activating an application role, removing the need to disable connection pooling. The sp_setapprole procedure has a new option that creates a cookie, which contains context information about the caller. You can revert the session by calling the sp_unsetapprole procedure, passing it the cookie.

Application Role Alternatives

Application roles depend on the security of a password, which presents a potential security vulnerability. Passwords may be exposed by being embedded in application code or saved on disk.

You may want to consider the following alternatives.

  • Use context switching with the EXECUTE AS statement with its NO REVERT and WITH COOKIE clauses. You can create a user account in a database that is not mapped to a login. You then assign permissions to this account. Using EXECUTE AS with a login-less user is more secure because it is permission-based, not password-based. For more information, see Customizing Permissions with Impersonation in SQL Server.

  • Sign stored procedures with certificates, granting only permission to execute the procedures. For more information, see Signing Stored Procedures in SQL Server.

External Resources

For more information, see the following resources.

Resource Description
Application Roles Describes how to create and use application roles in SQL Server 2008.

See also