Using Cross-Database ownership chaining
Posted on 2006-06-01
Okay most sites think cross-database ownership chaining is bad, but so far in testing those claims don't hold up.
So here is the problem I have. I have multiple databases on a one server (that's the way it's going to be and it's not changing). Basically one database houses common information and the others are like projects that utilitized by Web Applications. What I need to do is be able to create an sql user account that calls views and stored procedures in the one database, but those views and stored procedures could be accessing the other databases.
Currently the way this is done is to have an account that has select and execute access in all of the needed databases and then use that account for each app. It would also have update, delete, insert in the databases that had applications that require that. The database that houses the common information is used for reporting, so no updating by applications is done to it.
What I have found as a way to get what I want is to turn on the cross-database ownership chaining for the databases that need it, then make sure I have a SQL user account that has the necessary select and execute access in the different databases.
masterDB - contains the reporting crap
- has a user account (MISTER_READ) that has select access on the tables, nobody logs in with this account, doesn't need to.
app1DB - custom application database that might lookup something in the masterDB
- has it's own SQL user account (app1_user)
- contains views and/or stored procs. that lookup masterDB stuff
- make MISTER_READ the owner of those views and stored procs that access the masterDB and grant the app user account (app1_user) select/execute on those items
Then you turn on the cross-database ownership chaining on and it works! so where is the security flaw? I might be missing something here....
The articles I have looked at indicate that this would been that if MISTER_READ account was a database owner then that user account could create views in database's that it should have acces to. Prove it! You would have to be the owner of those objects in order to do that and when the cross-database ownership chaining is on it doesn't work for objects makes with dbo as the owner.
So if you decided to read though this lengthy blah, blah, blah will it work or is there something better?