9: Stored Procedures in .NET

Make QR Code ISO/IEC18004 in .NET 9: Stored Procedures

EXECUTE AS LOGIN = 'Dumbledore'; SELECT * FROM Gryffindor; REVERT;

b Now try to read the data using the stored procedure Since Dumbledore has the Execute permission on the stored procedure, he is able to read the data in the underlying table Thus, the following statement succeeds:

EXECUTE AS LOGIN = 'Dumbledore'; EXEC uspStudentList; REVERT;

NOTE The same concept applies to stored procedures that modify the data In the following steps, we ll create stored procedures that INSERT, UPDATE, and DELETE data By granting the Execute permission on the stored procedure, the underlying table can be modified

5 Enter and execute the following script to create and test stored procedures that use the INSERT, UPDATE, and DELETE commands Since you are logged on with the account that created the database and stored procedure, additional permissions on the stored procedure aren t needed a Use this script for a stored procedure using the INSERT command:

CREATE PROC uspInsertStudent (@LastName varchar(35),@FirstName varchar(35)) AS INSERT INTO Gryffindor VALUES(@LastName,@FirstName, NULL); GO Exec uspInsertStudent 'Finnegan', 'Seamus' EXEC uspStudentList;

b Enter and execute the following script to create and test a stored procedure that uses the UPDATE command:

CREATE PROC uspAssignPoints (@LastName varchar(35),@points int) AS UPDATE Gryffindor SET Points = @Points WHERE LastName = @LastName GO Exec uspAssignPoints 'Longbottom', 5; Exec uspStudentList;

c Enter and execute the following script to create and test a stored procedure that uses the DELETE command:

CREATE PROC uspDeleteStudent (@LastName varchar(35)) AS DELETE FROM Gryffindor WHERE @LastName = LastName; GO EXEC uspDeleteStudent ' Finnegan ' EXEC uspStudentList

6 Verify that Dumbledore can t execute INSERT, UPDATE, or DELETE statements against the Gryffindor table directly Therefore, each of the following statements should fail a Try to INSERT data with the following script This will fail:

EXECUTE AS LOGIN = 'Dumbledore'; INSERT INTO Gryffindor VALUES('Snape', 'Severus', NULL); REVERT;

EXECUTE AS LOGIN = 'Dumbledore'; UPDATE Gryffindor SET Points = 10 WHERE LastName = 'Longbottom'; REVERT;

EXECUTE AS LOGIN = 'Dumbledore'; DELETE FROM Gryffindor WHERE LastName = 'LongBottom'; REVERT;

7 Grant the Dumpledore user the Execute permission on the uspInsertStudent, uspAssignPoints, and uspDeleteStudent stored procedures

GRANT EXECUTE ON uspInsertStudent to Dumbledore; GRANT EXECUTE ON uspAssignPoints to Dumbledore; GRANT EXECUTE ON uspDeleteStudent to Dumbledore;

8 Try to execute each of these stored procedures as Dumbledore a Execute the uspInsertStudent stored procedure as Dumbledore using the following script This will succeed

EXECUTE AS LOGIN = 'Dumbledore'; EXEC uspInsertStudent 'Brown', 'Lavender'; EXEC uspStudentList; REVERT;

b Execute the uspAssignPoints stored procedure as Dumbledore with the following script This will succeed, also

EXECUTE AS LOGIN = 'Dumbledore'; EXEC uspAssignPoints 'Longbottom', 5; EXEC uspStudentList; REVERT;

c Execute the uspDeleteStudent stored procedure as Dumbledore with the following script This will succeed, too

EXECUTE AS LOGIN = 'Dumbledore'; EXEC uspDeleteStudent 'Longbottom'; EXEC uspStudentList; REVERT;

9 Taking this one step further, we can actually explicitly deny access to the underlying table (which is effectively the same as not granting any permissions to the table), but with Execute permissions on the stored procedure, the user can still read and modify the data a Use the following script to read or modify access to the table

DENY DENY DENY DENY DELETE INSERT UPDATE SELECT ON ON ON ON Gryffindor Gryffindor Gryffindor Gryffindor to to to to Dumbledore Dumbledore Dumbledore Dumbledore

EXECUTE AS LOGIN = 'Dumbledore'; EXEC uspInsertStudent 'Brown', 'Lavender';

EXEC uspDeleteStudent 'Thomas'; EXEC uspAssignPoints 'Finnegan', 15 EXEC uspStudentList; REVERT;

This topic came up in 6, but deserves some clarification here In SQL Server 2005, we have the ability to specifically identify the execution context of stored procedures, triggers, and many functions In other words, we can specify which user s account will be used when the module is run While execution context concepts apply to all these modules, let s focus our discussion on a stored procedure It will work the same way for other modules Consider this scenario On a regular basis, we import data into a staging table from an external database Immediately before importing the data, we truncate the staging table We use a stored procedure to do this, which is executed by a DBA In this case, either the DBA needs elevated permissions to truncate the staging table and import the data, or the stored procedure needs elevated permissions However, since there isn t a Truncate permission available, we d have to grant the DBA ALTER permissions on the database to allow him to truncate the table Instead of granting ALTER permissions, we use the EXECUTE AS clause to allow the user to execute the stored procedure with elevated permissions without actually granting the elevated permission to the user for any purpose other than this stored procedure The EXECUTE AS clause has several contexts that the stored procedure can be configured to run under The choices are: