Cross-database view permissions

Question

I'm working with a database (let's call it DB_data) that contains all of the tables for a series of applications. In an attempt to minimize downtime during upgrades, a facade database (let's call it DB_facade) has been created which has a view for each of the tables in DB_data. It also contains all of the functions and stored procedures, which work against these views.

In trying to lock down security in DB_data we've done a DENY on all of the tables for all of the users in DB_data. All of these users have also been created in DB_facade with permissions to the views.

The problem here, is that because of cross-database ownership chaining the DENYs in DB_data are overriding the GRANTs in DB_facade.

I'd like to avoid turning on ownership chaining for both of these databases because of the potential security issues (although in my original tests, that did seem to correct the access problem). Also, we're trying to minimize impact to the applications, so requiring all access to be through stored procedures and using certificates (for example) wouldn't work.

Does anyone have any other suggestions on how to handle this?

Thanks!

Answer 1

Do you have this problem if you exclude the DENY on the tables in DB_data? If you don't explicitly GRANT permissions on these tables, you may be able to get the security you need and get the access rights through the views.

Answer 2

from what i've seen and done, sql server doesn't let you have any permissions unless explicitly told so. You should be able to grant select (or use the role datareader) in DB_Data to the users, and as long as it's the same account and it's mapped to both databases (you'll have to grant select and exec on db_facade) that should work just fine.

Answer 3

You can create a view in the DB_data database for each view in the DB_facade database. The new views would have rights to select from the tables. GRANT SELECT on the views in DB_data. Change the views on DB_facade to SELECT from the views on DB_data. And, the tables would have DENY set.

I recognize one disadvantage to this; the users can still interact with the DB_data database. They wouldn't be able to access the tables, but they could access the new views.