It seems that there is a difference between the way t-sql permissions and CLR permissions work. I don't know if this is a fault of SQL2005 or of my coding - perhaps someone can tell me.
E.g. Assume there are two tables called test1 and test2 with no permissions granted to anyone on them. Below is a simple trigger that inserts into test2 when test1 is updated
CREATE TRIGGER tr_test ON test1 FOR UPDATE AS
BEGIN
DECLARE @.v INT
SELECT @.v = MAX(ID) FROM test1
INSERT INTO test2 (audit) VALUES ('The max value is currently: ' + CONVERT(VARCHAR(10),@.v))
END
GO
Attempting to update a row from a non-DBO account will fail (because the user does not have update permissions). This is of course correct.
Now assume we have a stored proc that updates the row in test1. We can grant EXEC permission to the stored proc and, hey presto, everything works.
Now, the problem. If the above trigger were written like this instead:-
[Microsoft.SqlServer.Server.SqlTrigger (Name="tr_test", Target="test1", Event="FOR UPDATE")]
public static void Trigger1()
{
using (SqlConnection conn = new SqlConnection("Context Connection = true"))
{
conn.Open();
SqlCommand cmd = new SqlCommand("select max(id) from test1", conn);
SqlDataReader rdr = cmd.ExecuteReader();
rdr.Read();
int val = rdr.GetInt32(0);
rdr.Close();
string s = string.Format("The max value is currently: {0}", val);
cmd = new SqlCommand("insert into test2 (audit) values (@.p)", conn);
cmd.Parameters.AddWithValue("@.p", s);
cmd.ExecuteNonQuery();
}
}
This is functionally equivalent to our previous trigger. However, running our stored proc again fails this time because we don't have SELECT permissions on test1.
So ultimately my question is, how do I get this to work without having to grant permissions to the tables?
Thanks
swg
You should be using the 'inserted' and 'deleted' tables. What you are doing is requerying the table (therefore you need select rights).
So it should read something like: select max(id) from inserted
Cheers
Paul.
|||The problem is that your CLR trigger breaks ownership chaining because it executes the sql code like dynamic sql. It has the same effect as using sp_executesql to make the query in your t-sql trigger, which would fail for the same reason.
Erland covers this in-depth here http://www.sommarskog.se/grantperm.html#ownershipchaining
There are two ways to work around this:
1. using the EXECUTE AS clause on your CLR trigger so that the trigger has access to the tables. Erland's article above is a good reference on how to do this and the potential pitfalls that should be avoided.
2. having your CLR trigger call a T-SQL stored proc to make the update, which shouldn't break the ownership chain. You can do this with SqlCommand.CommandType = CommandType.StoredProcedure
Steven
|||Thanks for the responses guys.
Paul, yes I agree that would have been a better example. However, the same symptom occurs if you access the inserted/deleted trigger tables.
Steven, thanks very much. I'll give your options some consideration.
This would seem to me to be a deficiency in the CLR/SQL Server integration. Although I can see (now that you have pointed it out to me) why the CLR case is different, I would hope I'm not alone in thinking that when SQL2005 offers us CLR for use with triggers and stored procs, the developer should not expect that the CLR equivalent version to operate differently. Certainly I didn't expect this to be the case and the connection string "Context Connection = true" reinforced this. Obviously I cannot excercise your option 2 in a normal trigger because I will not have access to the inserted/deleted tables from a stored proc. However, the EXECUTE AS may offer some hope although it is still a kludge.
Many thanks
~swg
|||Well just to put the cat among the pigeons, I've just done a small test app and, yes, sure enough everything worked fine. So I revoked the select permission on the table in my original app (the one that caused me to start this thread in the first place) and, yup, that's working fine too. So, God only knows what bug I had in there but it ain't there any more... or maybe I've written a new one in there to make the whole thing work now, who knows!
But certainly, going step-by-step and re-checking the permissions are there (or not there as appropriate) at each stage; it would seem the ownership chain is NOT broken when selecting from the inserted/deleted tables in a CLR trigger.
Cheers
~swg
Addedndum: To be clear, the ownership chain IS still broken if you follow my example (in the original post above) as explained in Steven's reply, because it does not use the inserted/deleted tables
|||Your original example didn't use Inserted/Updated tables, CLR Triggers can access those fine as they are not real tables and don't have the same ownership concept.
However, your CLR trigger will still not be able to access your audit table test2 without doing one of the workarounds I mentioned above. This is not the case for a T-SQL trigger. Here is a full scenario based on your example demonstrating this:
public class triggers
{
public static void Trigger1()
{
using (SqlConnection conn = new SqlConnection("Context Connection = true"))
{
conn.Open();
SqlCommand cmd = new SqlCommand("select count(*) from inserted", conn);
Int32 val = (Int32)cmd.ExecuteScalar();
SqlContext.Pipe.Send("Inserted: " + val.ToString());
cmd = new SqlCommand("insert into test2 values (@.p)", conn);
cmd.Parameters.AddWithValue("@.p", val);
cmd.ExecuteNonQuery();
}
}
}
create assembly SimpleTrigger from 'c:\assemblies\SimpleTrigger.dll'
go
create table test1(c1 int)
go
create table test2(c1 int)
go
create proc InsertTest @.input int
as
begin
insert into test1 values(@.input)
end
go
create trigger Trigger1 on test1 for insert as external name SimpleTrigger.triggers.Trigger1
go
create user user1 without login
go
grant execute on InsertTest to user1
go
execute as user='user1'
go
--CLR Trigger fails to update test2 because it does not have permission
exec InsertTest 1
go
revert
go
drop trigger Trigger1
go
create trigger Trigger1 on test1 for insert
as
begin
insert into test2 select count(*) from inserted
end
go
execute as user='user1'
go
--T-SQL trigger is able to update test2
exec InsertTest 1
go
revert
go
drop user user1
drop proc InsertTest
drop table test1
drop table test2
drop assembly SimpleTrigger
|||Sure thing, Steven
Absolutely agree. I hope you didn't take my last reply as a slur or contradiction of your answer. Most certainly wasn't intended that way. As I said in an earlier reply to Paul, my original post was a bad example; it should have contained inserted/deleted tables. That was why I couldn't understand why the CLR triggers wouldn't work. They would be next to useless if you couldn't access these tables.
My "real" situation was that the CLR trigger could not access the inserted table and granting select permission to the underlying table fixed the problem. There must have been something else at play somewhere though (and I'd like to know what) because I have now revoked select permission from this underlying table and my trigger continues to function correctly.
So, apologies for the confusion. I do appreciate your replies and it has helped me a lot in understanding ownership chains, and the differences between T-SQL and CLR triggers.
Thanks again
~swg
|||No offense taken - I just wanted to make sure it was completely clear both to you and to those playing along at home. What's happening here isn't exactly obvious and I'm don't think it's clearly stated in BOL either.
Steven
