Lesson 4: Managing Application Security

[Previous] [Next]

You have learned how to control access to a database using login authentication and permissions. You may also wish to secure access to databases at the application level. SQL Server provides views, stored procedures, and application roles to support application-level security.

After this lesson, you will be able to

• Describe the use of views and stored procedures to provide application-level security
• Describe the use of application roles to provide application-level security

Estimated lesson time: 60 minutes

Managing Security with Views and Stored Procedures

Views and stored procedures provide a secondary method of giving users access to data and the ability to perform activities in a database. They allow you to set up security with SQL Server objects that are created for an application.

Views and stored procedures enable you to manage the permissions for the view or stored procedure only, rather than the permissions for the objects to which they refer. They also shield users from changes to the underlying tables.

Using Views to Simplify Security

You can use views to prevent users from knowing that there are certain columns to which they do not have access in a table.

TIP

---

You could also restrict access to certain columns using column permissions. However, views are easier to manage and give better performance.

Grant permissions on a view to users without granting permissions on the underlying tables. The users can then use the view despite having no access to the underlying tables, thus protecting the underlying tables.

For example, the Salary column in a table contains confidential employee information, but the rest of the columns contain information to which users must have access. You can define a view that includes all of the columns in the table with the exception of the Salary column. As long as the table and the view have the same owner, granting SELECT permissions on the view allows users to view nonconfidential columns without permissions to the table itself.

Example 1

This example creates a view that retrieves employee information from the Employees table but excludes a column with confidential data. Figure 12.11 shows how the view is used to permit an end user to see only some of the information in the underlying table. The end user has no permissions to use the underlying table.

 CREATE VIEW Employee_View AS SELECT EmpID, FirstName, LastName, DOB FROM Employees 

Figure 12.11 Using a view to prevent users from seeing all of the columns in a table

Using Stored Procedures to Simplify Security

Grant users permissions to execute a stored procedure without granting them access to the tables that are read or modified. Then only applications that are written to execute the stored procedure will be able to gain access to the data.

In an archiving scenario, data that is older than a specified interval is copied into an archive table and then deleted from the primary table. Permissions can be used to prevent users from deleting rows from the primary table directly or from inserting rows into the archive table. You can create a stored procedure to ensure that both activities are performed together, and then you can grant permissions to users to execute the stored procedure.

Example 2

This example creates a stored procedure that can be used to insert a row into the Employees table. Figure 12.12 shows a user who has permission to execute a stored procedure to insert data into a table. The user does not have permission to insert data directly into the table.

 CREATE PROCEDURE Insert_Emp    @EmpID int,    @FirstName varchar(30),    @LastName varchar(30),    @DOB datetime,      @Salary money AS INSERT Employees  VALUES (@EmpID, @FirstName, @LastName, @DOB, @Salary) 

The following query executes the stored procedure.

 EXEC Insert_Emp 676, 'Carl', 'Petersen', '6/16/1965', 40000 

Figure 12.12 Using a stored procedure to insert a row into a table

Exercise: Using a View or Stored Procedure to Implement Permissions

In this exercise, you will create a view called Employee_View and a stored procedure called Employee_Proc. You then will grant the public role permissions on Employee_View and Employee_Proc. You will find the script for this exercise in C:\Sqladmin\Exercise\Ch12\CrVwSP.sql installed on your hard disk drive from the Supplemental Course Materials CD-ROM.

• To use a view or stored procedure to implement permissions

1. Switch to SQL Server Query Analyzer, close all query windows, and log on with Microsoft Windows NT authentication.

You should be connected as a member of the sysadmin role.

2. Execute the following statement to create a view on the Employees table that includes only the FirstName, LastName, and Title columns:

 USE StudyNwind GO CREATE VIEW Employee_View AS SELECT FirstName, LastName, Title FROM Employees 

3. Execute the following statement to create a stored procedure that queries the FirstName, LastName, and Title columns of the Employees table:

 USE StudyNwind GO CREATE PROCEDURE Employee_Proc AS SELECT FirstName, LastName, Title FROM Employees 

4. Execute the following statements to allow the public role to select from Employee_View and to execute Employee_Proc:

 USE StudyNwind GRANT SELECT ON Employee_View TO public GRANT EXEC ON Employee_Proc TO public 

Exercise: Testing Permissions on the View and Stored Procedure

In this exercise, you will query data from the Employee_View view and execute the Employee_Proc stored procedure. You will then attempt to query the Employees table directly. You will find the script for this exercise in C:\Sqladmin\Exercise \Ch12\TstVwSP.sql installed on your hard disk drive from the Supplemental Course Materials CD-ROM.

• To test the permissions on the view and stored procedure

1. Switch to SQL Server Query Analyzer, and close all query windows.
2. Open a new query window and, using SQL Server authentication, connect as Umberto with the password password.

Umberto does not belong to any database or server roles and has no specific permissions other than those that are associated with the public role.

3. Execute the following statement to query the Employee_View view:

 SELECT * FROM Employee_View 

Were you able to query the view? Why or why not?

Answer

4. Execute the Employee_Proc stored procedure:

 EXEC Employee_Proc 

Were you able to execute the stored procedure? Why or why not?

Answer

5. Execute the following statement to query the Employees table:

 SELECT * FROM Employees 

Were you able to query the table? Why or why not?

Answer

Managing Security with Application Roles

Application roles allow you to enforce security for a particular application. By using application roles, you can ensure that users gain access to data through specific applications only.

Figure 12.13 shows how you might want order entry clerks to be able to update the Orders table when they use the order entry application. You do not want the clerks to be able to gain access to the tables from another product, such as Microsoft Excel. In this situation, you could create an application role for the order entry application.

Figure 12.13 Using application roles to control application access

Application roles differ from other roles. The following lists the fundamental differences between application roles and other roles:

• Application roles have no members—a user uses an application that activates an application role. The role then controls all database access for the user's connection. The user thus gains permissions when using the application. This avoids the need to grant permissions to users directly.
• Application roles require a password to be activated.
• To protect the security of the application role password, you may want to use a simple encrypted key. You could also use an extended stored procedure to store the password at the server so that it is not transmitted across the network.
• An activated application role overrides the user's other permissions in the database. SQL Server temporarily ignores all permissions that are applied to the user account or to other roles to which the user belongs.

Creating Application Roles

Use SQL Server Enterprise Manager or the sp_addapprole system stored procedure to create a new application role. Only members of the db_owner, db_securityadmin, and sysadmin roles can execute the sp_addapprole system stored procedure. The syntax for the sp_addapprole system stored procedure in this case is as follows:

 sp_addapprole [@rolename =] 'role', [@password =] 'password' 

Consider the following facts when you create new application roles:

• The sp_addapprole system stored procedure adds a security account for the new role by adding a record to the sysusers table in the current database.
• The password value is the password that is required to activate the role, and it is stored in encrypted form.

Example 3

This example adds the new application role SalesApp to the current database with the password hg_7532LR.

 EXEC sp_addapprole SalesApp, hg_7532LR 

Exercise: Defining an Application Role

In this exercise, you will define an application role called Order_Entry in the StudyNwind database, using SQL Server Enterprise Manager. This process is very similar to creating a database role, with the exception that you do not define any members.

• To define an application role

1. Log on to your computer as Administrator or another account that is a member of the local Administrators group.
2. Start SQL Server Enterprise Manager.
3. Expand your server group, and then expand your server.
4. Expand Databases, and then expand the StudyNwind database.
5. Right-click Roles, and then click New Database Role.
6. Enter Order_Entry for the name of the new role.
7. Click Application Role, and enter password for the password.
8. Click OK to close the dialog box and create the new role.

Managing Application Role Permissions

Use SQL Server Enterprise Manager or the GRANT, DENY, and REVOKE statements in Transact-SQL to manage application role permissions.

Example 4

This example grants SELECT permissions for the Products table to the SalesApp application role.

 GRANT SELECT ON Products TO SalesApp 

Exercise: Assigning Permissions to an Application Role

In this exercise, you will assign permissions to the Order_Entry application role. The following table lists the permissions that must be assigned to Order_Entry.

Table	Permissions
Categories	SELECT
Customers	SELECT, INSERT, UPDATE
Order Details	SELECT, INSERT, UPDATE
Orders	SELECT, INSERT, UPDATE
Products	SELECT

• To assign permissions to an application role

1. Using SQL Server Enterprise Manager, select the Roles folder in the StudyNwind database.
2. Right-click the Order_Entry application role, and then click Properties.
3. Click Permissions.
4. In the list of objects, check each of the permissions shown in the table at the beginning of the exercise.
5. Click OK twice to close the dialog boxes and accept the permission assignments you have made.

Activating Application Roles

After a client connects to SQL Server with any login account, the client application must execute the sp_setapprole system stored procedure to activate the permissions that are associated with an application role. The sp_setapprole stored procedure can be executed by direct Transact-SQL statements only; it cannot be executed within another stored procedure or from within a user-defined transaction.

The syntax for the sp_setapprole procedure is as follows:

 sp_setapprole [@rolename =] 'name' , [@password =] {Encrypt N 'password'} | 'password' [,[@encrypt =] 'encrypt_style'] 

Consider the following facts when you activate application roles:

• The current application must provide the password.
• The scope of an application role is the current database only; if users change to another database, they are allowed to perform activities based on the permissions in that database.
• After an application role is activated with the sp_setapprole system stored procedure, the role cannot be deactivated in the current database until the user disconnects from SQL Server.

Example 5

This example activates the SalesApp application role with a password of hg_7532LR.

 EXEC sp_setapprole 'SalesApp',' hg_7532LR' 

Example 6

The following example shows how to set an application role from a Microsoft Visual Basic application that uses ActiveX Data Objects (ADO).

 ' Set up a connection Dim cnADO As ADODB.Connection Dim strConnect As String Set cnADO = New ADODB.Connection strConnect = "driver={SQL Server};" _     & "uid=Carl;pwd=password;server=sqlserver;database=StudyNwind" cnADO.Provider = "MSDASQL" cnADO.ConnectionString = strConnect cnADO.CursorLocation = adUseNonecnADO.Open ' Carl is connected using permissions assigned to his account ' or any roles or Windows NT groups to which he belongs ' Initialize variables for the application role Dim cmADO As ADODB.Command Dim pmADO As ADODB.Parameter Dim strRoleName AS StringDim strRolePass AS String Set cmADO = New ADODB.Command ' Set values for the role name and password strRoleName = "SalesApp" strRolePass = "hg_7532LR" ' Define the command and type (stored procedure) With cmADO     .CommandText = "sp_setapprole"   .CommandType = adCmdStoredProc End With ' Set up parameters for role name and password Set pmADO = cmADO.CreateParameter("rolename", _   adVarChar, adParamInput, Len(strRoleName), strRoleName) cmADO.Parameters.Append pmADO Set pmADO = cmADO.CreateParameter("password", _   adVarChar, adParamInput, Len(strRolePass), strRolePass) cmADO.Parameters.Append pmADO ' Execute the command to activate the application role cmADO.ActiveConnection = cnADO cmADO.Execute ' Now the application role is active for this connection ' and Carl has only the permissions assigned to the ' application role SalesApp 

Exercise: Activating an Application Role

In this exercise, you will use SQL Server Query Analyzer to log on as a user and activate the Order_Entry application role. You will find the script for this exercise in C:\Sqladmin\Exercise\Ch12\ActApp.sql installed on your hard disk drive from the Supplemental Course Materials CD-ROM.

• To activate an application role

1. Open SQL Server Query Analyzer and, by using SQL Server authentication, connect as Carl with the password password.
2. Execute the sp_setapprole system stored procedure to activate the role:

 EXEC sp_setapprole 'Order_Entry', 'password' 

3. Execute SELECT statements to query the Employees and Customers tables.

 SELECT * FROM Employees SELECT * FROM Customers 

What permissions does Carl have after the Order_Entry application role is activated?

Answer

How long will the Order_Entry role be activated for Carl?

4. Close the query window to end the session. This will deactivate the Order_Entry role for Carl.

The following table lists additional system stored procedures that you can use for managing application roles.

System stored procedure	Description
sp_dropapprole	Drops an application role from the current database
sp_approlepassword	Changes the password for an application role

Lesson Summary

SQL Server provides views, stored procedures, and application roles to support application-level security. You can use views to limit columns or rows accessed by users. Stored procedures can be used to access data. Users needing access to the data can be given access only to the stored procedure An application role is a role that has no members—a user uses an application that activates an application role. The role then controls all database access for the user's connection. The user thus gains permissions when using the application. This avoids the need to grant permissions to users directly.