Programmable objects, such as stored procedures and user-defined functions, have their own security context. Database users need permissions in order to execute stored procedures, functions, and assemblies. Once the database engine has checked for permission to execute a programmable object, it then checks permissions for operations performed inside programmable objects. When database objects access each other sequentially, the sequence forms an ownership chain. You will learn about ownership chains later in this chapter.
Managing Security for Stored Procedures
Stored procedures are probably the most frequently used database objects for a database developer. As with other database objects, stored procedures are subject to security. You need permissions to perform operations, such as creating a stored procedure, and users must have appropriate permissions in order to execute a stored procedure. Table 2-6 lists the permissions that you can grant on stored procedures.
Executing a Stored Procedure
When an application makes a call to execute a stored procedure, SQL Server checks if the current database user has the EXECUTE permission on the stored procedure. The following sample grants EXECUTE permission to Sara on the dbo.uspGetBillOfMaterials stored procedure. (The code in this and subsequent sections is included in the sample files as ManagingAccessToProgrammableObjects.sql.)
-- Change the connection context to the database AdventureWorks. USE AdventureWorks; GO -- Grant EXECUTE permission to Sara on a stored procedure. GRANT EXECUTE On dbo.uspGetBillOfMaterials TO Sara;
In the same way, if you want to prevent a user from executing a stored procedure, you can revoke or deny the EXECUTE permission for the user.
Managing Security for User-Defined Functions
User-defined functions are programmable objects, such as stored procedures. Basically, there are two types of user-defined functions: scalar functions, which return a single value, and table-valued functions, which return a table. Depending on the type of user-defined function, you need to grant either EXECUTE or SELECT permission (see Table 2-7).
Executing Table-Valued Functions
When a user executes a table-valued function, SQL Server checks whether the user has SELECT permission on the table. You can grant this permission on a function in the same way you grant the SELECT permission on tables. The following sample grants SELECT permission to Sara on the dbo.ufnGetContactInformation user-defined function.
-- Change the connection context to the database AdventureWorks. USE AdventureWorks; GO -- Grant permission to Sara to execute a user defined function. GRANT SELECT ON dbo.ufnGetContactInformation TO Sara;
Executing Scalar Functions
In order to execute a scalar function, a user needs to have EXECUTE permission on the function. You can grant EXECUTE permission on a scalar function in the same way that you grant EXECUTE permission on a stored procedure. The following sample grants Sara EXECUTE permission on the dbo.ufnGetStock function.
-- Change the connection context to the database AdventureWorks. USE AdventureWorks; GO -- Grant Sara permission to execute a user defined function. GRANT EXECUTE ON dbo.ufnGetStock TO Sara;
Managing Security for Assemblies
SQL Server 2005 provides you the ability to include .NET assemblies (objects which reference .dll files) inside the database engine and invoke these assemblies in stored procedures and functions. You can assign the same permissions to assemblies as for stored procedures. Refer to Table 2-6 for those permissions.
Defining a Permission Set
When you create an assembly, you need to specify a permission set. The permission set specifies a set of code access permissions that are granted to the assembly in SQL Server. There are three different permission sets:
SAFE is the recommended permission set for assemblies that do not need to access external resources.
You can find more information about Code Access Security on the following URL: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/secmod/html/secmod116.asp.
Executing an Assembly
When an application tries to access an object inside an assembly, the database engine checks whether the current user has EXECUTE permission. The following code is used to grant EXECUTE permission on an assembly to the user Sara:
-- Change the connection context to the database AdventureWorks. USE AdventureWorks; GO -- Grant Sara permission to execute an assembly. GRANT EXECUTE ON <AssemblyName> TO Sara;
By providing EXECUTE permission for one assembly, you are granting EXECUTE permission on all assembly objects to the user.
Managing Ownership Chains
An ownership chain is the sequence of database objects accessing each other. For example, when you insert rows in a table from a stored procedure, the stored procedure is the calling object and the table is the called object. When SQL Server traverses a chain, the database engine evaluates permissions in a different way than it would if you were accessing objects individually.
When you access an object within a chain, SQL Server first compares the owner of the object with the owner of the calling object. If it is the same owner, permissions on the called object are not evaluated. This feature is very useful for managing object permissions. For example, suppose Sara creates a table called Person.SupplierContacts and a stored procedure called Person.InsertSupplierContacts through which Sara inserts rows into the PersonSupplierContacts table. Since these two objects have the same owner, Sara, you only need to grant EXECUTE permission to other users on the Person.InsertSupplierContacts stored procedure in order to allow them EXECUTE permission on the PersonSupplierContacts table. You do not need to grant permissions on the table to other users.
Managing the Execution Context
The login and user connected to a session or executing a procedure determines the execution context. Login and user tokens provide SQL Server 2005 the information for evaluating object permissions. SQL Server 2005 provides you the ability to change the execution context using the EXECUTE AS statement. This operation is called Context Switching.
Running EXECUTE AS
The EXECUTE AS statement allows you to explicitly define the execution context for the current connection. You can use EXECUTE AS to change the login or the user for the current connection. The execution context change is valid until another context change is made, until the connection is closed, or until a REVERT statement is executed. The following sample uses EXECUTE AS to change the execution context to user Sara.
-- Change the connection context to the database AdventureWorks. USE AdventureWorks; GO -- Change the execution context to the user Sara. EXECUTE AS USER='Sara'; -- The following statement will be executed under Sara's credentials. TRUNCATE TABLE dbo.ErrorLog;
The above code will finish with an error because Sara does not have permission to truncate a table. The following code to truncate the table will succeed.
-- Change the execution context back to the original state REVERT; -- Now the following statement will be executed under -- the original execution context. TRUNCATE TABLE dbo.ErrorLog;
Managing Context Switching
Apart from controlling execution context for batches (groups of T-SQL statements sent to the server for execution, as in the TRUNCATE TABLE example above), you can control execution context for stored procedures and user-defined functions. When you switch the context in these modules, you can control which user account will be used to access objects referenced in the stored procedure or function. To accomplish this task, you can use the EXECUTE AS statement with the following modifications:
The following sample creates a stored procedure that switches the context to the dbo user. Then it grants EXECUTE permission to Sara on the stored procedure and changes the context to test the execution of the stored procedure.
-- Create a stored procedure to execute statements --' as dbo. CREATE PROCEDURE dbo.usp_TruncateErrorLog WITH EXECUTE AS 'dbo' AS TRUNCATE TABLE dbo.ErrorLog; GO -- Grant permissions to execute this procedure to Sara. GRANT EXECUTE ON dbo.usp_TruncateErrorLog TO Sara -- Change the execution context of this batch to Sara. EXECUTE AS [USER=]'Sara' -- Execute the stored procedure. EXECUTE dbo.usp_TruncateErrorLog
As you can see in the sample above, context switching can be a good approach to allow some operations to users that you cannot grant through permissions.