August 17, 2009 at 2:48 am
I want to have a database (db1) containing all the SPs that our website would use which would keep it seperate from the data database as we have many so to have all the 'logic' in 1 DB is better for me.
These SPs would access data in our actual data database (db2) however I do not want the user from web (who will have exec permission in db1) having any possible connection to our data base (db2).
I have read some sections in BOL but cannot get the with execute as and 'set trustworth on' and Authenticate permission to work.
For example
DB1 - log in as User1, exec procedure with execute as User2, and User2 has permissions to query the data in DB2.
thanks
August 17, 2009 at 4:33 am
Hi,
Ref this Topic related to the SETUSER (user can process with the other user name)
http://www.sqlservercentral.com/Forums/Topic767655-146-1.aspx
August 21, 2009 at 6:13 pm
Cross database ownership chaining is actually a good solution here. Here's how to implement:
1) Create the guest user on the database containing the data. Make sure it and the public role have no additional permissions other than CONNECT to the database. This will ensure that if they compromise the server in some way, they can't get into the data using the account from the web.
2) Configure both databases for cross database ownership chaining. Make sure this is at the database level, not the server level.
3) Ensure that the owner of the schema in the database with the stored procedures matches the owner of the schema where the tables are in the second database. If both are in the dbo schema, then make sure the owner is the same over both databases. You can change who the owner is by ALTER AUTHORIZATION ON DATABASE::db_name TO login;
This will allow an ownership chain to form from the stored procedure in the first database to the tables in the second database. That means you only have to grant permissions to execute the stored procedures. You don't have to grant any permissions against the tables themselves.
K. Brian Kelley
@kbriankelley
August 24, 2009 at 3:52 am
This solution appears to work.
In my DB that just contains stored procedures the 'web' login has execute permissions, and this database has its trustworthy property set to on.
The 'web' login has no access directly to the data database.
The owner of this stored procedure DB also exists as a user in the data database with Authenticate permission and then with the necessary read/write permissions for the data tables as required.
Also, all the stored procedures need to have 'with execute as OWNER'
This does seem to work fine, but are there any obvious security issues with this approach?
Thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply