What permissions do I need to set up.

Question

What permissions do I need to set up.

Rob Reid-246754

SSCarpal Tunnel

Points: 4183
More actions
January 15, 2008 at 4:01 am

#180657

I have an existing database system that is all working fine. (ASP > SQL 2005)
All CUD actions are done through stored procedures. The logon user that the website uses to connect to the DB has access to the DB (db_datareader,public) and it all works fine.
Now I have had to split the DB up into two separate DBS as there are a group tables that are going to be shared by both the Live and Demo systems. This middle (Settings) DB just contains tables. All the procs/funcs are in the _live or _demo DBs and they reference any tables in the Middle (Settings) DB through a fully qualified name
[settings_db].dbo.someTable.
The web user login has access to both databases. However at the moment any stored proc calls from the website that needs to do Inserts/Updates/Deletes in the DB that contains the stored proc is working but if it tries to do an insert/update/delete on a table thats in the middle (Settings) DB it comes up with this error:
"The INSERT permission was denied on the object 'TEST_2', database 'settings_DB', schema 'dbo'. "
I can rectify this by granting the web login db_datawriter permission on the settings DB but I don't really want to do this if possible as I have just spend ages putting a lot of legacy code into stored procs due to an sql injection attack on the site and I only want the web user to have execute permissions on procs.
What settings / config can I do so that
a) All the procs/funcs remain in DBA (_live OR _demo) and only reference tables in DBB (settings DB)
b) The web user just has read permissions and then execute on any procs it needs to run.
Thanks for any help in advance.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Rob Reid-246754 SSCarpal Tunnel Points: 4183 More actions · Answer 1

Also I've tried setting TRUSTWORTHY on for both DBs that need to talk to each other but it still fails.

I'm not using EXECUTE AS though as there is one login (that the website uses) that has permssions to both DBs. Its this user that has execute permission on the stored proc thats trying to do carry out an update/insert in the other DB. It also has Read access for both DBs

Thanks

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

It's because the ownership chain is broken.

Assume a user that has exec permissions on a stored proc, but not on a table. The proc does a select on that table. If both the proc and the table hve the same owner, the user can run the proc without an error. That's ownership chaining.

If the proc and the base table had different owners, then the user would get an error, because as soon as ownership changes, the ownership chain is broken and permissions must be rechecked.

By default, ownership chaining between dataabses is disabled. You can switch it on (its a server-wide setting) but be aware there are security implication to doing so.

Books online will have more info on that.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Rob Reid-246754 SSCarpal Tunnel Points: 4183 More actions · Answer 3

Hellol. Currently there is one login that has been given the same access to both databases. The owner of the tables is dbo. I suppose that just because the name is the same its seen as totally different in each DB.

At the moment the website can call a stored proc in DB1 that accesses tables in DB2 and there is no problem just returning records eg SELECT * FROM [DB2].dbo.TABLE. It can also run SQL from the website that joins tables from both databases with no problem. Its only when the procedure tries to do an UPDATE/INSERT/DELETE in DB2 from DB1 that an error gets returned back.

I did have db_datawriter enabled for that user which worked fine but I have unticked it as I cannot guarantee that someone won't add code to the website that doesn't check input very well. Only last month one developer added a news category list box to a page to filter the news articles but didnt sanitise the input variables and some hacker appended some clever sql injection to the end of the select that added lots of script tags linking to a virus site everywhere. If the web user logon hadn't had db_datawriter enabled then that hack would have failed.

I don't think I'd be allowed to turn cross DB ownership chaining on. Is there no other way of accomplishing it with some EXECUTE AS method.

Thanks.

Steve Jones - SSC Editor SSC Guru Points: 738642 More actions · Answer 4

It sounds like the user just doesn't have INSERT permissions on the table in the other database. SELECT permissions are different from INSERT permissions.

You don't need datawriter. Create a new role in the database (2nd one) and add the web user to this role. Grant the ROLE the rights it needs (INSERT, UPDATE, DELETE, SELECT if these are correct) and you should be ok.

Rob Reid-246754 SSCarpal Tunnel Points: 4183 More actions · Answer 5

Thanks Steve thats done the trick nicely.

Great site by the way!