SPECIFYING EXECUTION CONTEXT FOR PROCEDURAL CODE pre-SQL

SPECIFYING EXECUTION CONTEXT FOR PROCEDURAL 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 CREATEscript. It saves the DBA from having to do a SETUSERFRED(change the current user to FRED) before executing the CREATEstatement. 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 SELFis 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_mestored 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

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

Bookmark the permalink.

Comments are closed.