February 9, 2009 at 12:19 am
Comments posted to this topic are about the item Filtering DML Statements
February 9, 2009 at 7:00 am
the column filtering solution is incomplete. consider this update statement:
update tableA set name ='Jane',contactnumber='baddata' where id=8
since column(name) comes up true, it still executes. column() doesn't mean only that column, just that it has to be included.
here's some logic i've used to make sure that the number of columns matches what i want:
declare @mc int
set @mc=0
declare @cu int
set @cu=columns_updated()
while @cu>0
begin
if (@cu & 1)>0
begin
set @mc=@mc+1
set @cu=@cu-1
end
set @cu=@cu/2
end
if @mc=1 and update(name)
February 9, 2009 at 8:51 am
Although the article shows a simple technique there are many things that were omitted.
This is NOT a way to handle such things if there are strict requirements from security standpoint.
1. You could potentially read the record, delete it and insert it back again with the changes you want.
The update trigger will never Know:(
2. Workstation_id and application_name are values "settable" on the connection string and can be manipulated.
3. To Rollback in a trigger is a very expensive proposition for a busy system.
4. This kind of control should be performed using other security mechanisms.
All in all the article shows "a way" to do it I just wanted to point out that this is by no means THE way of doing such things if strict requirements are needed.
* Noel
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply