Specifying execution context for procedural code |
 |
By Bob Beauchemin, Niels Berglund and Dan Sullivan
28 Feb 2005 | Addison-Wesley |
|
|
 The following excerpt, courtesy of Addison-Wesley, is from Chapter 6 of the book "A First Look at SQL Server 2005 for Developers" written by Bob Beauchemin, Niels Berglund and Dan Sullivan. Click for the complete book excerpt series or purchase the book.
Specifying execution context for procedural code
In previous versions of SQL Server, cataloged procedural code always ran
within the security context of the caller, as explained earlier in the section on ownership chaining. This is a good strategy for the most common case -- for example, when you want to allow users access to tables through stored procedures without giving them access to the base tables. However, it is not always what you want. Take, for example, a stored procedure that executes dynamic SQL composed by concatenating strings. This does a type of indirect parameterization of the table name. This is necessary because you may want to build a query with a table name as a parameter.
— this won't work
CREATE PROCEDURE count_rows(@name NVARCHAR(50))
AS
SELECT COUNT(*) FROM @name
GO
— this will
— the dynamic SQL executes in the caller's context
CREATE PROCEDURE count_rows(@name NVARCHAR(50))
AS
EXECUTE('SELECT COUNT(*) FROM ' + @name)
GO
SQL Server 2005 now allows you to specify that procedural code execute in a different execution context. There are three reasons you might want to do this.
- You want dynamic SQL to execute in the context of the creator of the stored procedure, as static T-SQL would.
- Since data access code in CLR procedures (through the SqlServer data provider discussed in Chapter 4) is effectively dynamic SQL, you might want this code to execute in the context of the creator of the stored procedure as well.
- You want to evaluate ownership chains in the context of the creator of the stored procedure rather than the caller of the procedure.
You choose the execution context on a per-procedure basis when you create the procedure, using the EXECUTE AS parameter. Execution context can also be set on user-defined functions, except for inline table-valued user-defined functions. Examples are shown in the following code.
— pre-SQL Server 2005 execution context
— this will execute as the direct caller
CREATE PROCEDURE count_rows(@name NVARCHAR(50)
WITH EXECUTE AS CALLER
AS
EXECUTE('SELECT COUNT(*) FROM ' + @name)
GO
— this will execute as the stored procedure creator
CREATE PROCEDURE count_rows_as_me(@name NVARCHAR(50))
WITH EXECUTE AS SELF
AS
EXECUTE('SELECT COUNT(*) FROM ' + @name)
GO
— this will execute as a specific user
CREATE PROCEDURE count_rows_as_fred(@name NVARCHAR(50))
WITH EXECUTE AS 'FRED'
AS
EXECUTE('SELECT COUNT(*) FROM ' + @name)
GO
Note that the third option is just a convenience for a DBA running a CREATE script. It saves the DBA from having to do a SETUSER FRED (change the current user to FRED) before executing the CREATE statement.
The second option shows how ownership chaining affects stored procedures that make use of dynamic SQL. Prior to SQL Server 2005, permission was always checked against the identity of the caller of a stored procedure when it referenced a database object using dynamic SQL. That is still the default behavior in SQL Server 2005. EXECUTE AS SELF can be used in the definition of the stored procedure so that even though permission will be checked when dynamic SQL is used, the behavior will be the same as static SQL. Figure 6-3 shows using EXECUTE AS SELF to make dynamic SQL behave the same as static SQL.
Special care must be taken to guard against SQL injection (that is, piggybacking of dangerous code after "normal" parameters) when EXECUTE AS SELF is used. Although counting the rows in a table is pretty mundane code, the fact is that any dynamically constructed code in a stored procedure can be potentially dangerous. Given the count_rows_as_me stored procedure in the previous example, if the procedure was cataloged by the DBO role, the following code will execute as DBO, regardless of the user who calls it.
DECLARE @s VARCHAR(50)
SET @s = 'authors;drop table customers'
— count the rows and drop the table!
count_rows_as_me @s
This probably wasn't the desired result.
Although EXECUTE AS SELF looks interesting, it should be used with care because it can make ownership chains more complex. When the stored procedure count_rows_as_me accesses any table that the current owner does not own, an ownership chain will be broken and permissions on the underlying object will be checked. In addition, when a different stored procedure uses this stored procedure, it is possible that ownership chains could be broken at two levels, as shown in the script that follows.
— table FOO_TABLE is owned by DBO.
— using the count_rows_as_me procedure from the previous example
SETUSER JAY
GO
— this checks permissions if JAY is not DBO
count_rows_as_me 'foo_table'
—
—
SETUSER FRED
GO
CREATE PROCEDURE select_and_count
AS
SELECT * FROM customers
count_rows_as_me 'foo_table'
GO
— this does two ownership checks
— even if FRED is DBO
select_and_count
GO
By default, procedural code that uses a nondefault execution context can only access resources in the current database -- that is, you may not use three-part names at all. This is to guard against a user with DBO privilege in one database gaining access to data in another database. If you need to access resources in another database or system-level resources, you must grant appropriate permissions to the executor of the code. Another option is to sign the code with a certificate, map the certificate to a login, and grant permissions to the certificate. This option is in development at the time of
this writing.
Click for the next excerpt in this series: SQL Server permissions and new objects
Click for the book excerpt series or visit here to obtain the complete book.
|
|
|
|
