Only allow updates from a specific trigger event

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Only allow updates from a specific trigger event

Author

Topic

jholovacs
Posting Yak Master

163 Posts

Posted - 2009-04-03 : 15:01:22

I am making a reference table that I want maintained by a distinct set of triggers. If one of these parent tables experiences a modification that meets certain conditions, it needs to make DML changes to this reference table.

What I'd like to do is ONLY allow DML changes to this reference table from this distinct set of triggers... kinda like making a complex foreign key relationship, but because the data is transformed pretty significantly, no such foreign key can exist.

What I'd like to do is create a trigger for INSERT, UPDATE, DELETE on the reference table and bomb out if the calling trigger is not one of three distinct triggers in the same database and schema, or if some idiot tries to manually update the table. I'm not real clear if the trigger_nestlevel() function will do what I want... the documentation isn't very deep.

Thoughts?

SELECT TOP 1
w.[name]
FROM
dbo.women w
INNER JOIN
dbo.inlaws i
ON
i.inlaw_id = w.parent_id
WHERE
i.net_worth > 10000000
AND
i.status IN ('dead', 'dying')
AND
w.husband_id IS NULL
ORDER BY
w.hotness_factor DESC

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-03 : 15:29:32

>>or if some idiot tries to manually update the table

Can you achieve your objective through security? ie: make sure none of you "idiots" have access to this table? If an admin or dbo really wants to update a table I don't think there is any way to prevent that. So don't allow write access to anything - but grant exec privs on SPs. That way only the (parameterized) SP code can affect any of your data.

Be One with the Optimizer
TG

jholovacs
Posting Yak Master

163 Posts

Posted - 2009-04-03 : 16:26:57

Unfortunately no.

You see the problem is, the users have access to change the base tables; they need to, it's part of the job description. If I auto update the reference table with triggers linked to the base tables, then the users will by necessity require the update permissions on the reference table, as they will, for all intents an purposes, be updating that table under their own credentials. I therefore need to restrict their ability to update the reference table based on the METHOD of update rather than the permissions.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-03 : 16:36:57

>>the users will by necessity require the update permissions on the reference table, as they will, for all intents an purposes, be updating that table under their own credentials

Not sure that is accurate. But even so, you could include in the trigger the "execute as" clause.

Be One with the Optimizer
TG

jholovacs
Posting Yak Master

163 Posts

Posted - 2009-04-03 : 16:52:07

If that is not accurate, I sure would like to know about it. SQL Profiler filters on my credentials, and it captures nested triggers for me. I cannot imagine what other credential it could use to make changes, nor can I imagine a scenario where I would want that credential to be replaced with something a bit more obfuscated. That would sure make tracking a nested process difficult.

But it seems to me that you should be able to tell the parent trigger/ procedure firing a child procedure/ trigger from inside the child. This is common logic in most scripting and programming languages, and I will be rather annoyed (again) at the limitations of T-SQL if there is not a way to do it in SQL Server 2005.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-03 : 16:56:25

I just did a little test: I created two tables (table1, table2). Table1 has a trigger that modifies table2. I created a login / dbuser called [junkUser]. I then: GRANT select,insert,update,delete on table1 to a junkUser.

Junk user can modify and read from table1 directly and CANNOT modify or read from table2 directly. However the trigger code successfully modifies table two when the junkUser modifies table1. This can happen because both tables are in the same database and there is no dynamic sql used to modify table2.

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-03 : 17:16:05

sorry should have included my test code:
This code assumes you have a DB called [Junk]


--logged in as an admin:
use master
go
create login junkUser with password='junk', check_policy=off
go
use junk --I already had this db created
go
create user junkUser from login [junkUser]
go

create table table1 (i int)
create table table2 (i int)

grant select, insert, update, delete on table1 to junkUser

go

create TRIGGER trig_JunkTable1 ON table1
after DELETE 
AS

DELETE FROM t2
from   table2 t2
join   deleted i on i.i = t2.i

go
insert table1 (i) values (1)
insert table1 (i) values (2)
insert table1 (i) values (3)

insert table2 (i) values (1)
insert table2 (i) values (2)
insert table2 (i) values (3)

go

--CHANGE CONNECTION to connect as JunkUser
use junk

--errors out with permission errors
delete table2 where i < 3

--successfull
--notice 2 sets of "(2 rows(s) affected)"
delete table1 where i < 3


--CHANGE CONNECTION back to an admin
use junk

select * from table1
select * from table2

drop table table2
drop table table1
drop user junkUser
drop login junkUser

Be One with the Optimizer
TG

jholovacs
Posting Yak Master

163 Posts

Posted - 2009-04-06 : 11:41:40

That looks like it would be effective, but I really can't use user-level security here. There are quite a few people that have sa access (please no crap about that; it's a management/ politics thing and there's no way I can change it). Is there a way to detect the calling procedure in T-SQL?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-06 : 13:05:17

>> There are quite a few people that have sa access
wow! does "management" know that that basically give complete access to the entire system (not just sql server)? I hope, as a DBA, you can at least advise management about these security lapses.

>> Is there a way to detect the calling procedure in T-SQL?
There is not fool proof way to prevent SAs from manually updating any table. Whatever you put in place they can get around and/or disable.

One thing you could do to slow someone down is to put an INSTEAD OF trigger on the table you are trying to protect. You can have the maintainence trigger(s) set a CONTEXT_INFO value. Then the instead of trigger can check the context info value and only perform the DML if the context info is a certain value. Again, that is not fool proof but could prevent innocent attempts to directly update the table.

Be One with the Optimizer
TG

jholovacs
Posting Yak Master

163 Posts

Posted - 2009-04-06 : 13:27:33

Thanks that may be exactly what I am looking for.

Regarding management, I have decried the risk exposure inherent in our "insecurity model" but until something really bad happens, it won't change. It's an uncomfortable place to be, but the best I can do is cover my butt and do what I can to mitigate/ minimize damage.

jholovacs
Posting Yak Master

163 Posts

Posted - 2009-05-20 : 12:43:16

that worked like a champ, btw. Thanks.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-05-20 : 13:50:19

Cool! thanks for the update.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources