Next, create in database1 a dbo-owned view that selects the rows from the sensitive_data table in database2. Try to select from that view:
CREATE VIEW dbo.sensitive AS
SELECT * FROM database2.dbo.sensitive_data
GO
SELECT * FROM dbo.sensitive
GO
If you're running SQL Server 2000 SP3 and you haven't enabled cross-database ownership chaining, you should get an error message that permission on the sensitive_data table is denied.
Go back to the connection where you're logged in as an administrator. To enable cross-database ownership chaining, you can either set the global configuration option or set a database option in both databases involved in the chain, as the code in Listing 3 shows.
Finally, return to user1's connection, and try again to select from the view:
— You're logged in as user1.
SELECT * FROM dbo.sensitive
GO
This time, you should be able to see the sensitive data in database2 even though user1 has no special rights in that database. You can't select the data directly, but you can build a view that lets you perform an action that you shouldn't be able to do as user1. In addition, when cross-database ownership chaining is in effect, you as user1 could create a dbo-owned stored procedure that lets you not only retrieve the data in sensitive_data but modify it as well.
One important fact isn't mentioned in the second Microsoft article or the Guzman article. Even if all the conditions are met—that is, db chaining is enabled in both databases, and the view and table owners map to the same login—if user1 has no access to database2, an attempt to select from the dbo.sensitive view will fail. You can test this behavior by removing user1's access from database2:
— You're logged in as sa.
USE database2
EXEC sp_revokedbaccess user1
Finally, return to user1's connection and try to select from the view:
— You're logged in as user1.
SELECT * FROM dbo.sensitive
GO
This time, you get a different error message:
Server: Msg 916, Level 14, State 1,
View Sensitive, Line 2
Server user 'user1' is not a valid
user in database 'database2'.
Although the SP3 behavior is more secure than the previous behavior of allowing cross-database access to act just like ownership chains within a database, it's still a little too broad. It would be nice to be able to choose who has what access across databases, but we'll have to wait for that capability. I hope we won't have to wait too long.
End of Article
Prev. page
1
2
[3]
next page -->
