Lesson 2: Using Application Roles | MCSE Microsoft SQL Server 2000 System Administration(c) Training Kit (c) Exam 70-228

3 4

In addition to granting access to users and groups and then assigning them permissions, you can create an application role within a database with specific permissions through which users access data in SQL Server 2000. In this lesson, you will learn to create application roles using SQL Server Enterprise Manager and Transact-SQL. You also learn the security implications of using application roles.

After this lesson, you will be able to

Create application roles
Activate and use application roles

Estimated lesson time: 30 minutes

Understanding Application Roles

You use an application role to restrict user access to data in SQL Server 2000 through a specific application (such as Microsoft Excel or a custom accounting application). Restricting user access to an application prevents users from executing poorly written queries or attempting to access any sensitive information.

An application role is a database role to which permissions are assigned. An application role contains no members and is inactive by default. You cannot add Windows 2000 or Windows NT 4.0 groups, users, or roles to application roles. An application must submit a password to activate the application role. A password can be hard-coded into an application, or it can be an encrypted key in the registry or in a SQL Server 2000 database.

The permissions of a user accessing SQL Server 2000 through an application role are limited to the permissions granted to the application role. The existing permissions, or the lack of permissions, by the user accessing SQL Server 2000 through an application role are ignored. In addition, an application role is database-specific, meaning that any access beyond the database in which the application role exists is limited to guest user access.

Creating Application Roles

Creating an application role using SQL Server Enterprise Manager is substantially the same as creating a user-defined database role. Within the database in which you are creating the application role, right-click the Roles container and then click New Database Role. In the Database Role Properties – New Role dialog box, enter a name for the new role in the Name text box, click the Application Role option button, and then enter a password into the Password text box. See Figure 11.7.

figure 11.7 - creating an application role.

Figure 11.7

Creating an application role.

After creating the application role, configure permissions for the role the same way you configure permissions for a user-defined database role, by clicking the Permissions button. See Figure 11.8.

You can also create an application role using the Transact-SQL sp_addapprole system stored procedure.

 sp_addapprole 'AccountingAppRole' , 'AppPassword'

The preceding example creates the AccountingAppRole with a password of AppPassword in the current database. You grant permissions to an application role with Transact-SQL using the same GRANT, REVOKE, and DENY statements used to manage user, group, and user-defined database role permissions.

figure 11.8 - configuring permissions for the new role.

Figure 11.8

Configuring permissions for the new role.

Activating and Using Application Roles

To activate an application role, use the sp_setapprole system stored procedure. Once the application role has been activated, the user (using the application that activated the application role) can perform whatever activities are permitted to the application role.

 EXEC sp_setapprole 'AccountingAppRole' , {Encrypt N 'AppPassword'} , 'odbc'

The preceding example activates the AccountingAppRole using the AppPassword. It uses the ODBC encryption method before sending the password to SQL Server 2000.

Practice: Creating and Testing Application Roles

In this practice you create and test application roles.

To create and test application roles

Switch to SQL Server Enterprise Manager.
In the console tree, expand the Databases container for the default instance, and then expand the SSEMDB database container.
Right-click Roles and then click New Database Role.
The Database Role Properties – New Role dialog box appears.
In the Name text box, type NewAppRole.
Click the Application Role option button.
In the Password text box, type pass and then click OK.
In the details pane, double-click NewAppRole.
The Database Role Properties – NewAppRole dialog box appears.
Click the Permissions button.
The Database Role Properties – SSEMDB dialog box appears.
For the database object Customer_Procedure, select the Exec check box to grant EXECUTE permissions on this stored procedure.
For the database object Customer_View1, select the Select check box to grant SELECT permissions on this view and then click OK.
Click OK.
Switch to the Ana connection in SQL Query Analyzer.
Execute the two queries in the query pane to verify that Ana can neither execute Customer_Procedure nor select from Customer_View1.
Do not close this original connection for Ana.
On the File menu, click Connect.
The Connect To SQL Server dialog box appears.
Click the SQL Server Authentication option button.
In the Login Name text box, type Ana.
In the Password text box, type password and then click OK.
Notice that you are connected as Ana to the master database.
On the toolbar, change the current database to SSEMDB in the database drop-down list.
In the query pane, type EXEC sp_setapprole 'NewAppRole' , 'pass' and then click the Execute Query button on the toolbar.
Notice that the NewAppRole has been activated.
On the toolbar, click the Clear Window button.
In the query pane, type EXEC customer_procedure and then click the Execute Query button on the toolbar.
Notice that Ana can now execute this stored procedure through the application role.
On the toolbar, click the Clear Window button.
In the query pane, type SELECT * FROM Customer_View1 and then click the Execute Query button on the toolbar.
Notice that Ana can now select from this view through the application role.
Switch to the original Ana connection in SQL Query Analyzer.
Execute the queries.
Notice that Ana can neither execute the stored procedure nor select from the view. She was able to use the permissions of the application role, but these permissions are valid only for that connection.
Close SQL Query Analyzer. This will also deactivate the application role. Do not save any changes.

Lesson Summary

Application roles are useful for limiting user access to specific applications only. You can create an application role and grant it permissions using either SQL Server Enterprise Manager or Transact-SQL. An application then activates the role by using the sp_setapprole system stored procedure and passing a password. All rights of users through the application role are limited to the rights of the application role, regardless of the rights of the actual user.