Managing Application Security Using Stored Procedures, User-defined Functions, and Views

When permissions are granted on complex objects such as stored procedures, user-defined functions, or views, the user does not need to have permissions on the underlying objects within or referenced by it. This characteristic is illustrated in the following example:

 Create Database TestCOC Go create login AnnS WITH PASSWORD = 'My,password', DEFAULT_DATABASE = TestCOC GO Use TestCOC GO CREATE USER AnnS Go Create Table dbo.aTablet      Id int identity(1,1),      Description Varchar(20)      ) Go Create Procedure dbo.ap_aTable_List as      Select * from dbo.aTable go Create Procedure dbo.ap_aTable_Insert     (SDesc varchar(20) as      Insert Into dbo.aTable (Description)      Values ((SDesc) Go Deny Select, Insert, Update, Delete On dbo.aTable To Public Grant Execute On dbo.ap_aTable_Insert To Public Grant Execute On dbo.ap_aTable_List To Public Go 

A table is created along with two stored procedures for viewing and inserting records into it. All database users are prevented from using the table directly but are granted permission to use the stored procedures.

Note 

All database users are automatically members of tbe Public role. Whatever permissions are granted or denied to tbe Public role are automatically granted or denied to all database users.

After this script is executed, you can log in as AnnS in Query Analyzer and try to access the table both directly and through stored procedures. Figure 19-3 illustrates such attempts.

Figure 19-3: Stored procedures are accessible even when underlying objects are not.

There are some exceptions to the rule I have just described:

• If the schema of the stored procedure is not the schema of all the database objects in the stored procedure, SQL Server will check the object's permissions on each underlying database object. This is not an issue when all objects belong to the default dbo schema. However, you will have to do some careful planning when you decide to use database schemas.

• _ If you are executing a character string batch in a stored procedure, you still need to set permissions on all underlying objects. SQL Server will consider this string to be a separate batch and therefore it will check the permissions of underlying objects. For more details, refer to the "Ownership Chains" section, earlier in this chapter.

• If a stored procedure references objects in some other database, behavior will depend on whether cross-database ownership chains are enabled or not. For more details, refer to the "Cross-database Ownership Chains" section, earlier in this chapter.

Stored procedures, user-defined functions, and views are important tools for implementing sophisticated security solutions in a database. Each user should have permissions to perform activities tied to the business functions for which he or she is responsible and to view only related information. It is also easier to manage security in a database on a functional level than on the data level. Therefore, client applications should not be able to issue ad hoc queries against tables in a database. Instead, they should execute stored procedures.

Users should be grouped in roles by the functionality they require, and roles should be granted with execute permissions on related stored procedures. Since roles are stored only in the current database, using them helps you avoid problems that occur during the transfer of the database from the development to the production environment.