Database Security in SQL Server 6.5 - Stored Procedures
(Page 8 of 8 )
In this discussion, I have only covered ownership chains with views, but the same rules apply to stored procedures. The one exception is that SQL Server will check permissions on base tables for stored procedures. For example, the following simple stored procedure queries Joe_Smith.Orders based on the @CustID parameter:
CREATE PROCEDURE JaneD.Customer_Total(@CustID int)
as
SELECT Sum(Total) as Total_Sales
FROM Joe_Smith.Orders
WHERE CustID = @CustID
go
If you execute the procedure as JaneD, you will get an error saying she does not have SELECT permission on the Joe_Smith.Orders table. If you grant EXECUTE permission to FredJ, he will get the same error. This is the proper response.
Suppose you change the stored procedure to use JaneD’s view instead:
CREATE PROCEDURE JaneD.Customer_Total2(@CustID int)
as
SELECT * From JaneD.Order_Totals
WHERE CustID = @CustID
go
In this case, SQL Server allows JaneD to execute the procedure. The flaw in the permission checking for views affects stored procedures that use those views as well. It is not possible to protect the underlying tables by wrapping them in a stored procedure.
Triggers
Triggers are special kinds of stored procedures, so they follow the same rules. If a table’s trigger references an object owned by another user, the person executing the statement that caused the trigger to fire will need to have appropriate permissions on the referenced object. A permission violation will cause the trigger to fail and the entire transaction to roll back. An exception to the rule is that because the Inserted and Deleted temporary transaction tables do not really have owners, they are considered to be owned by the table owner. Triggers can only be created by the table owner anyway, so SQL Server never does permission checking on the Inserted and Deleted tables.
Recommendations on Ownership
Because of the issues with permission checking that I have discussed, the standard practice is to have dbo own all objects in a database. Having the same owner on all objects means that SQL Server will check the permissions just on the top-level object and will not have to check permissions throughout the entire chain. There are two main benefits of this approach:
- The management of permissions is far easier and more straightforward.
- Overall performance is significantly better for databases with large numbers of objects and/or large numbers of dependencies between those objects.
Once you have chains with more than two levels, you will find that having dbo own everything in the database is the best way to go. To help you reset object ownership to dbo throughout the database, you can use the following stored procedure:
DROP PROCEDURE TakeOwnership
go
exec sp_configure 'allow updates', 1
Reconfigure with Override
go
Create Procedure TakeOwnership
as
UPDATE sysobjects
SET uid = 1
WHERE type = 'U' or type = 'V' or type = 'P'
go
exec sp_configure 'allow updates', 0
Reconfigure with Override
exec sp_tables
go
exec TakeOwnership
exec sp_tables
go
Although you should always approach direct updates to the system tables with extreme caution, this is one of the stored procedures Microsoft should have put in the standard installation. You can modify this procedure to change the ownership of a single object or to change ownership of one set of objects.
Another way to make sure that dbo owns all database objects is to set up aliases for the dbo user account for everyone who has permission to execute one of the object creation statements. This technique will eliminate the chance that someone could create a stored procedure or view that might work in development but not in production. Aliases were created to solve just this kind of problem—it makes sense to use them if you can.
Summary This chapter has covered all the ways to authorize users and secure data in a database, but it has not really discussed much in the area of best practices. The sheer number of different environments in which SQL Server is used precludes an exhaustive list of best practices. Remember to give careful thought to who can access the database and to the permissions assigned to the public group. In almost all cases I recommend that you not enable the guest database user so that all users must be explicitly granted access. Finally, grant the most general permissions to groups, and then assign more specific permissions on a user-by-user basis. What you will find is that SQL Server 7.0 and 2000 offer a richer set of options for securing databases than SQL Server 6.5, but with a little thought you should be able to meet your needs.
 This article is excerpted from SQL Server Security Distilled by Morris Lewis (Apress, 2004; ISBN 1590592190). Check it out at your favorite bookstore today. Buy this book now. |
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
