December 3, 2009 at 11:43 am
Pardon me if this is a newbie question; I’m an Oracle DBA and relatively new to SQL Server.
In Oracle, I created and tested the following scenario:
• As user U1, I created table U1.TEST and granted INSERT privileges on it to user U2;
• As user U2, I created an identical table U2.TEST and granted INSERT privileges on it to user U3;
• As user U2, I inserted a row into U1.TEST to confirm that he had INSERT privileges;
• As user U2, I created an INSERT trigger on U2.TEST that performs an INSERT into U1.TEST;
• As user U2, I inserted a row into U2.TEST and confirmed that my trigger inserted a row into U1.TEST;
• As user U2, I granted INSERT privileges on U2.TEST to user U3;
• As user U3, I inserted a row in U2.TEST and confirmed that that the row got inserted **and** that a corresponding row got inserted, by the INSERT trigger on U2.TEST, into U1.TEST.
This is, as far as I know, standard DBMS behavior across a number of DBMS’s; if you have an INSERT trigger on U2.TEST, it fires by default with the privileges of the trigger definer (U2, in this case, who has INSERT privileges on U1.TEST), and you don’t have to grant any additional privileges to user U3 beyond INSERT privileges on U2.TEST. I replicated this test in MySQL and in PostgreSQL, among others.
However, I don’t see the same behavior in SQL Server. I carried out the same steps (after the usual stumbling around in a relatively new – to me – DBMS), and I received this error:
Msg 916, Level 14, State 1, Procedure TEST_TRIGGER, Line 9
The server principal "U3" is not able to access the database "U1" under the current security context.
I did some web research and discovered some database-specific settings that seemed to promise success, but they did not work. I should add that it’s important to me, both from a security standpoint and a workflow standpoint, not to have to grant privileges on U1.TEST to user U3, etc. Among other reasons, other users (e.g., user U4, who doesn’t yet exist) that I can’t know about at present may be granted INSERT privileges on U2.TEST in the future. Based on the behavior of other DBMS’s, I think I should be able to find a way to grant U4 et al INSERT privileges on U2.TEST with no other grants.
Thanks in advance for any advice/assistance anyone can offer...
December 3, 2009 at 12:05 pm
Take a look at "Execute As", specifically "Execute As Owner". That should handle the permissions issue you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 4, 2009 at 7:20 am
SQL Server uses ownership chaining to handle these types of issues or as Gus has mentioned you can use EXECUTE AS. I have written a series of blog posts about these types of things here are the links:
Maintaining Security and Performance Using Stored Procedures Part 1 - Execute As
Maintaining Security and Performance Using Stored Procedures Part 2 - Signing
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply