Secure Cross database access via Stored procedures

  • 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.

  • 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