I have two databases, Database1 & Database2 and two applications that access both databases using an account called AppUser. There are hundreds of views in Database1 that access tables in Database2.
So far in the current model, AppUser has been granted access to these tables in Database2.
I want to use DB_CHAINING ON in Database1 & Database2, and allow access to Database2 tables only through sql objects in Database1. After all AppUser doesn't need direct access to tables in Database2.
Currently the sql objects in the two databases can only be created by DBAs & developers. There are no other human business users that can create schemas/sql objects in Database1 & Database 2.
I keep reading that enabling DB_CHAINING is a security risk.
Here for instance they make the claim:
Let it be said directly: this method opens a security hole that permits a local power user to elevate his permission to sysadmin, although the way to do it is a little more complicated and roundabout than when the database is set to TRUSTWORTHY.
Can you please give an example of security risks I am exposing myself to in case I use DB_CHAINING ON for the two databases? As far as I am concerned, it is safer since AppUser won't have access directly to tables in Database2.
One security risk that I identified is accidentally allowing other users to create views that can access the dbo owned tables. In my case it's not an issue because no other human business users are allowed to create sql objects in the two databases.
There is another post here where it has been suggested the use of option (4) where one signs the sql objects (note that views cannot be signed only stored procedures & functions). While it is the most secure option, it's also one of the most convoluted solutions that the geniuses from Microsoft came up with. I used it already in a different context but honestly I think it is overkill.
One serious risk that I identified is having a third database, Database3 with DB_CHAINING ON and with the same owner as Database1 & Database2. A db_owner user in Database3 would be able to create views/stored procedures that return data from any tables in Database1 & Database2 even though the user might not be able to access those tables directly, and as long as his login is mapped to a user in Database1 & Database2. This issue is avoidable by making sure the Database3 db_owner doesn't have access at all on Database1 & Database2.
I decided not use the DB_CHAINING ON. But the more I think about it, the sql server model forces one to allow wider permissions than necessary. It is not uncommon to have multiple databases mapped to business areas/contexts and often it is necessary to have views that access data in multiple databases. Views cannot be signed. If a view in database1 selects data from a table in database2 I have to grant the user select on that table in database2 even when database1 & database2 have the same owner.