February 12, 2014 at 8:20 am
Hi
I'm talking about the default setting for EXECUTE AS when a DML trigger is created. According to msdn:
However, I have found that a user has greater permissions whilst inside a trigger. I have used triggers to perform inserts on tables where my user does not have the insert permission. The trigger in question was created without the EXECUTE AS condition so it should be using the default of CALLER.
Is this a mistake in msdn? or can somebody explain this behaviour?
here is a script to replicate (2008)
-- Create a table that our test user will have insert permissions for
if object_id('dbo.UnrestrictedTable') > 0
begin
drop table dbo.UnrestrictedTable
end
create table UnrestrictedTable (id int, userName varchar(max) not null default user_name())
go
-- Create a table that our test user will not have insert permissions for
if object_id('dbo.RestrictedTable') > 0
begin
drop table dbo.RestrictedTable
end
create table RestrictedTable (id int, userName varchar(max) not null default user_name())
go
-- Create a trigger on insert of the unrestricted table that causes an insert on the restricted table
if exists(select * from sys.objects where name = 'TestInsertTrigger')
begin
drop trigger TestInsertTrigger
end
go
create trigger dbo.TestInsertTrigger on UnrestrictedTable
for insert
as
begin
insert into dbo.RestrictedTable (id)
select id from inserted
end
go
-- create a new user
begin try
drop user restrictedUser
end try begin catch end catch
begin try
create login restrictedUserLogin with password = '123QWERTYasdfg'
end try begin catch end catch
create user restrictedUser for login restrictedUserLogin
-- give user insert permission on just one of the tables
grant insert on dbo.UnrestrictedTable to restrictedUser
-- impersonate the new user
execute as user = 'restrictedUser'
-- try and fail to insert into the restricted table
begin try
insert into dbo.RestrictedTable(id)
values (54)
end try
begin catch
select 'Error whilst inserting into RestrictedTable', error_message()
end catch
-- successfully insert in both tables using the trigger
insert into dbo.UnrestrictedTable(id)
values (42)
revert
select'UnrestrictedTable', *
fromdbo.UnrestrictedTable
select'RestrictedTable', *
fromdbo.RestrictedTable
February 12, 2014 at 8:26 am
ownership chaining is what is happening, i'm pretty sure.
i believe that in this case, because all the objects are owned by the same schema(dbo), it's the same logic that follows giving execute permissions on a stored proc: the trigger is allowed to do any work it wants(insert/update/delete, etc) the insert into a table the caller does not have explicit access to, due to ownership chaining.
the owner dbo owns both tables, so code executed in modules, as long as they don't touch objects outside the schema or database, still perform the work.
Lowell
February 12, 2014 at 8:37 am
Oh ok that makes sense.
This also explains why it WASN'T giving elevated permissions on tables in a different database. Which is why I started looking into this in the first place.
Many thanks
February 12, 2014 at 8:50 am
Lowell is correct. To show the point I have created a user without a login to change the ownership of the RestrictedTable:-- Create a table that our test user will have insert permissions for
if object_id('dbo.UnrestrictedTable') > 0
begin
drop table dbo.UnrestrictedTable
end
create table UnrestrictedTable (id int, userName varchar(max) not null default user_name())
go
-- Create a table that our test user will not have insert permissions for
if object_id('dbo.RestrictedTable') > 0
begin
drop table dbo.RestrictedTable
end
create table RestrictedTable (id int, userName varchar(max) not null default user_name())
go
-- Create a trigger on insert of the unrestricted table that causes an insert on the restricted table
if exists(select * from sys.objects where name = 'TestInsertTrigger')
begin
drop trigger TestInsertTrigger
end
go
create trigger dbo.TestInsertTrigger on UnrestrictedTable
for insert
as
begin
insert into dbo.RestrictedTable (id)
select id from inserted
end
go
-- create a new user
begin try
drop user restrictedUser
end try begin catch end catch
begin try
create login restrictedUserLogin with password = '123QWERTYasdfg'
end try begin catch end catch
create user restrictedUser for login restrictedUserLogin
-- give user insert permission on just one of the tables
grant insert on dbo.UnrestrictedTable to restrictedUser
/* Break the ownership chain */
begin try
create user BreakChain without login;
end try begin catch end catch
go
alter authorization on object::dbo.RestrictedTable to BreakChain;
go
-- impersonate the new user
execute as user = 'restrictedUser'
-- try and fail to insert into the restricted table
begin try
insert into dbo.RestrictedTable(id)
values (54)
end try
begin catch
select 'Error whilst inserting into RestrictedTable', error_message()
end catch
-- successfully insert in both tables using the trigger
begin try
insert into dbo.UnrestrictedTable(id)
values (42)
end try
begin catch
select 'Error whilst inserting into RestrictedTable using trigger', error_message()
revert
end catch
revert
select'UnrestrictedTable', *
fromdbo.UnrestrictedTable
select'RestrictedTable', *
fromdbo.RestrictedTable
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply