Users must run the activating application to use an application role's permissions
Microsoft introduced role-based security with SQL Server 7.0. Server and database roles simplify security administration by letting individual users inherit permissions granted, revoked, or denied to those roles. Moving users in and out of roles is much easier than assigning individual permissions to users. At the database level, you can add custom roles. In addition to the standard database roles to which you can assign users, a second type of SQL Server database role exists—an application role. You don't assign users to an application role, as you would to other roles; instead, all users of the client application that activates an application role automatically share that role. The permissions granted to the application role are in force for the duration of the connection that activated the application role.
Application roles are session-based (connection-based) rather than user-based. To activate an application role, you run the sp_setapprole system stored procedure, which requires two parameters: role name and password. After an application activates an application role, SQL Server applies the permissions granted to the application role to the session that the application is using.
Application roles work with both Windows NT and SQL Server authentication, even though application roles don't apply directly to users. After executing the sp_setapprole stored procedure to invoke an application role, you can't disable or deactivate that role for the duration of that connection. SQL Server ignores permissions that an application user might have that are independent of the application role*SQL Server considers only the application role's permissions. To return a database user's original individual security context, the user must disconnect the application from SQL Server and reconnect without activating the application role.
Uses for Application Roles
If you want your users to work with SQL Server data only when they run a specific application, application roles can be useful tools. For example, suppose you have an inventory-control database and you've built a Visual Basic (VB) application that lets users work with inventory. You might not want your users to connect directly to the inventory-control database or to modify sales data. By granting permissions to an application role, rather than to users or database roles, you ensure that users can access inventory data only when running the application.
Another use for an application role might be to prevent an application from executing ad hoc queries. For example, you might use Microsoft Excel as a front end for a decision-support application that queries SQL Server, creating pivot tables and charts for further analysis. However, you don't want Excel users to use Excel's querying tools to perform unauthorized ad hoc queries. You can force users to run the Excel Visual Basic for Applications (VBA) macros that you've programmed to activate the appropriate application role, rather than granting users permissions that they might use in unapproved ways.
The steps you take to use an application role with VBA macros are essentially the same as the steps you take to use ADO code in VB or any VBA host. The GetData() function opens an ADO Connection object, executes sp_setapprole, creates an ADO Recordset, and copies the ADO Recordset's values into a worksheet. Executing the Close method on the Connection object deactivates the application role.
Application roles can also simplify security administration. Suppose you have many users who access SQL Server through only one application and you don't want to have to manage individual logins and enable database access for each user. An application role requires only one login, which many users can share.
Like a standard database role, an application role exists only in relation to the particular database for which you created the role. After you activate the application role, you can directly access another database only if you've created a guest account in the second database. The application role will have only the permissions granted to the guest account in the second database.
Creating an Application Role
To add an application role to a database, you must, by definition, be a member of the sysadmin fixed server role or of either the db_owner or the db_securityadmin fixed database role. To use Enterprise Manager to create an application role in a database, right-click the Roles node under the database you want and choose New Role from the menu. Select the Application role option, and type in a role name and password for the application role. The sp_setapprole stored procedure requires the role name and password as arguments when the stored procedure activates the application role. You can also create an application role by using the sp_addapprole system stored procedure, as the following script—which creates an application role named ProductApprole with a password of password on the Products database—shows:
USE Products
EXEC sp_addapprole
@rolename = 'ProductApprole',
@password = 'password'
Next, you assign permissions to the application role. Note that the application role inherits permissions from the public role, so if you want to close security loopholes, revoke all permissions for the public role on objects that you want to secure. When you deny all public permissions on the database tables, as Figure 1 shows, no role, not even the application role, can directly access the database tables. Denying all permissions on the database tables effectively prevents all ad hoc table querying. When you grant permissions to execute selected stored procedures to the application role after you revoke the public role's permissions, as Figure 2 shows, the application role can access the underlying table's data through the stored procedures if the tables and related stored procedures have a common owner.
If you deny all permissions to the database's tables, you must create views, stored procedures, or—for SQL Server 2000—user-defined functions (UDFs) to allow access to your data. Permissions that you grant to views, stored procedures, and UDFs take precedence over permissions that you deny to the underlying tables, as long as the views, stored procedures, and UDFs have the same owner.
Prev. page
[1]
2
3
4
5
next page 
