Database Security in SQL Server 6.5 - Ownership Chains
(Page 7 of 8 )
At first, having multiple object owners may seem like a good way to segment one database into multiple sections or to allow users to manage access to their own tables. As it turns out, having more than one object owner in a database causes problems when SQL Server checks permissions.
Views
The problem arises when one object (for example, a view) refers to one or more objects that are owned by two or more database users. A simple example could be a view created by JaneD that uses two tables owned by Joe_Smith:
SETUSER 'JaneD'
CREATE VIEW JaneD.Order_Totals (CustID, Order_Total)
AS
SELECT CustID, SUM(Total)
FROM Joe_Smith.Orders
GROUP BY CustID
SETUSER
This is known as an ownership chain and is shown in Figure 3-1.
Figure 3-1. A simple ownership chain
NOTE Run the script ownershipChains_setup.sql now. You”ll find it in the code download for this book at http://www.WinNetSecurity. com, from which you can create all the tables and the views in this section.
Both JaneD and Joe_Smith have the right to assign permissions on their own objects because they are the owners. To be able to reference his tables, Joe_Smith would need to also grant SELECT permission on them to JaneD. However, JaneD does not have this permission. JaneD can grant SELECT, INSERT, UPDATE, and DELETE permissions to any other user for her view, but she cannot grant any permissions to Joe’s underlying tables at all.
Let’s stop for a moment and think about the ramifications of this situation. JaneD has created a view on Joe_Smith’s tables, and because she is the owner of that view, she can assign permissions however she wants. Assume Joe_Smith granted SELECT permission on his tables to JaneD but not to anyone else. Should JaneD essentially be able to transfer her permissions to another user by creating a view on Joe_Smith’s table? In other words, should FredJ be trusted with Joe_Smith’s tables because Joe_Smith trusts JaneD and JaneD trusts FredJ? This kind of transitive permission assignment violates the concept that the owner of an object should have complete control over who has permission to use it, so SQL Server does not allow it.
At least that is what SQL Server Books Online (BOL) says should happen. In one of the instances in which this book contradicts the documentation, let me demonstrate what actually does happen.
After running the script to build the objects and set the permissions shown in Figure 3-1, you have a situation in which JaneD has no permissions whatsoever to use Joe_Smith’s Customers and Orders tables. You can prove that fact by running a query like this:
SETUSER JaneD
SELECT * FROM Joe_Smith.Customers
SETUSER
This will return an error. Because SQL Server checks permissions when a view is used, not when it is created, it will allow JaneD to create the view on Joe_Smith’s table without generating an error. It is when someone tries to use the view that you should get an error. In fact, BOL states the following in the article titled “Ownership Chains”:
“. . . if Joe has permission on the CREATE VIEW statement, he can define a view based on the [pubs..]authors table even if he does not have SELECT permission on authors. However, the view would be useless to everyone, including Joe.”
Two simple tests in following the script can prove this statement wrong. First, JaneD can select from her own view, even though she has no permissions on the underlying table. Second, when she grants FredJ the SELECT permission on her view, as shown in the following code, he can also query Joe_Smith’s tables through it. FredJ cannot query Joe_Smith’s tables directly, but he can query JaneD’s view:
SETUSER 'JaneD'
PRINT 'Testing JaneD''s permissions on Joe_Smith.Orders
and JaneD.Order_Totals'
SELECT * FROM Joe_Smith.Orders
SELECT * FROM JaneD.Order_Totals
GRANT SELECT ON JaneD.Order_Totals TO FredJ
GO
SETUSER
SETUSER 'FredJ'
PRINT ' '
PRINT 'Testing FredJ''s permissions on Joe_Smith.Orders
and JaneD.Order_Totals'
SELECT * FROM Joe_Smith.Orders
SELECT * FROM JaneD.Order_Totals
SETUSER
go
What is supposed to happen is that when FredJ uses the view in a SELECT statement, SQL Server first checks the permissions on the view and then checks his permissions for each of the tables, because the view and the tables have different owners. If FredJ does not have SELECT permission on all of them, his SELECT statement should fail. Testing shows that SQL Server 6.5 has a flaw in the way it handles permission checking for a view that refers just to base tables.
Remember, the standard version of SQL Server 6.5 for this book is Service Pack 5a. I ran these tests on Service Pack 5a and the post–Service Pack 5a update. I also spent about 6 hours combing through Microsoft’s site and several sites dedicated to SQL Server looking for recognition of this problem. Finally, I even tested the scripts by logging into new sessions with the JaneD, Joe_Smith, and FredJ logins from another computer using a regular Windows NT user account just in case I was somehow getting administrator privileges without knowing it. Unfortunately, this is one of those times when a few simple tests prove that you cannot always trust the documentation.
The next example involves a more complicated broken ownership chain (see also Figure 3-2), in which the expected behavior does occur:
CREATE VIEW Joe_Smith.Totals_by_Name(Cust.name,Order.total) AS
SELECT a.name, b.Order_Total
FROM Joe_Smith.Customers a, JaneD.Order_Totals b
WHERE a.CustID = b.CustID
Figure 3-2. A complex ownership chain
In this example (also in ownershipChains_setup.sql), Joe_Smith creates a view that joins the output of JaneD’s view with Joe_Smith.Customers, to provide the customer’s name instead of the customer number. The following script tests the permissions on the new view:
SETUSER 'Joe_Smith'
SELECT * FROM Joe_Smith.Totals_by_Name
GRANT SELECT ON Joe_Smith.Totals_by_Name TO FredJ
SETUSER
GO
SETUSER 'FredJ'
SELECT * FROM Joe_Smith.Totals_by_Name
SETUSER
GO
Here is the result:
=====================================================
SELECT permission denied on object Order_Totals, database Test, owner JaneD
============================================================
You will see that Joe_Smith cannot use his own view because JaneD has not granted him SELECT permission on her view. FredJ, however, can query both JaneD.Order_Totals and Joe_Smith.Totals_by_Name because he has SELECT permission on both views (note that neither JaneD nor FredJ can query Joe_Smith’s tables directly). The results of FredJ’s query are shown in the following table.
Cust_Name Order_Total
John's Game Emporium 60.00
Bonnie's Dog House 60.00
Erin's House of Giggles 60.00
Rebekah's Kitty Kennel 60.00
Just to test to make sure that moving another level up the chain does not give Joe_Smith permissions he should not have, the next example adds one more view named Joe_Smith.OrderTotals:
CREATE VIEW Joe_Smith.TotalOrders (Total.Amount)
AS
SELECT SUM(Order_Total)
FROM Joe_Smith.Totals_by_Name
Figure 3-3 shows the resulting ownership chain.
Figure 3-3. A more complex ownership chain
This is a view based on the Joe_Smith.Totals_by_Name view; therefore, Joe_Smith owns all the objects referenced. Nevertheless, SQL Server recognizes that Joe_Smith does not own all the objects in the chain and thus checks permissions on each referenced object. In this case, when you run the following script, Joe_Smith still receives an error when he tries to use the OrderTotals view, and FredJ receives an error for OrderTotals because he has not been granted SELECT permission on it, even though he can access the view on which OrderTotals is based:
SETUSER 'Joe_Smith'
SELECT * FROM Joe_Smith.TotalOrders
SETUSER
GO
SETUSER 'FredJ'
SELECT * FROM Joe_Smith.TotalOrders
SETUSER
GO
Here are the results:
=================================================
SELECT permission denied on object Order_Totals, database
Test, owner JaneD
SELECT permission denied on object TotalOrders, database
Test, owner Joe_Smith
=======================================================
Consider an alternative scenario in which Joe_Smith owns all the objects referenced in the chain. In this case, SQL Server’s designers assume that Joe_Smith will assign permissions on an object to FredJ only if he wants FredJ to see the contents of the underlying tables; therefore, SQL Server checks FredJ’s permissions only on the top-level object, not on the tables. In fact, FredJ may have no assigned permissions on the tables and views below Joe_Smith.OrderTotals and still SELECT data through the view. You can test this scenario by changing the script to make Joe_Smith the owner of all the views, removing FredJ’s SELECT permission from Order_Totals, and then executing the following SELECT statement:
SETUSER 'FredJ'
SELECT * FROM Joe_Smith.Customers
SELECT * FROM Joe_Smith.Orders
SELECT * FROM Joe_Smith.Order_Totals
SELECT * FROM Joe_Smith.Totals_by_Name
SELECT * FROM Joe_Smith.TotalOrders
SETUSER
Only the last SELECT statement will work.
 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. |
Next: Stored Procedures >>
More MS SQL Server Articles
More By Apress Publishing
