December 8, 2011 at 1:52 pm
I am trying to solve a security issue if a hacker breaks into the database. In the current setup the hacker as userA can only execute SP in db1 but has select access to all data in db3. How do I get rid of the select access in db3. Are there other security risks?
Current setup
db1 and db3 owner is sa
db1
userA has connect and execute permissions and executes a SP that requires select permissions in db3 (cross-database access)
userB has db_owner
userC has select and update permissions.
db3
userA has connect and select permissions
the above all works
as the DBA I would like to get rid of the userA select permissions in db3 so I tested the following and it works with TRUSTWORTHY, AUTHENTICATE and with execute as owner in the SP.
Tested setup
db1
db1 is set to TRUSTWORTHY
userA has connect and execute permissions and executes a SP (with execute as owner) that requires select permissions in db3 (cross-database access)
userB has db_owner
userC has select and update permissions.
db3
userA is set to authenticate
userA has only connect permission
the above all works
To make it more secure I should change the owner to non sysadmin of db1 to safeuserdb1 and db3 to safeuserdb3.
Thus when a hacker breaks in he can only damage db1. How can I prevent this last sentance from happening.
December 9, 2011 at 10:22 am
It might be simpler to use cross-database ownership chaining. I'm assuming all objects are in the dbo schema for both DBs.
- db1 and db3 have the same owner (doesn't matter what).
- cross-database ownership chaining is on in both databases.
- In db1, userA has connect and execute permissions
- In db3, userA has connect permissions and nothing more
In this scenario, if userA goes through a stored procedure in db1 and it accesses objects in db3, so long as the owners of the objects map up to the same login, ownership chaining will occur across DBs.
K. Brian Kelley
@kbriankelley
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply