How to limit updates allowed on a table

Question

We have a table that needs to be locked down in a specific way and we are looking for the best (reliability is first priority, performance is second) way to accomplish it. The rules involving this table are that new records can be added freely but once they have been then, with one exception, they should never be changed. There is going to be one valid update command allowed for records in this table, all other updates or deletes should be blocked. Here is a rough example of the approach we have worked out so far, are there any good ways to improve it?

Table looks like this:

CREATE TABLE [dbo].[Table_1](
    ID int IDENTITY(1,1) PRIMARY KEY NOT NULL
    ,data1 varchar(64) NULL
    ,data2 varchar(64) NULL
    ,data3 int NULL
    ,data4 bit NULL
    ,ModifiedBy VARCHAR(32)
    ,ModifiedDtTm DATETIME
)

The only allowable update will be via a proc which resembles this:

UPDATE dbo.Table_1
SET 
    data4 = 1
    ,ModifiedBy = @User
    ,ModifiedDtTm = GETDATE()
WHERE ID = @ID

What we have in mind to lock the table is to create these two triggers:

CREATE TRIGGER [dbo].[Table_1_UpdtLock] 
   ON  [dbo].[Table_1]
   INSTEAD OF UPDATE
AS 
BEGIN
    IF COLUMNS_UPDATED() = 0x70
        UPDATE dbo.Table_1
        SET 
            data4 = I.data4
            ,ModifiedBy = I.ModifiedBy
            ,ModifiedDtTm = I.ModifiedDtTm
        FROM dbo.Table_1 AS T
            INNER JOIN INSERTED AS I
                ON T.ID = I.ID
        WHERE I.data4 = 1
    ELSE
    BEGIN
        RAISERROR ('Table is locked.', 16, 0)
        ROLLBACK
    END
END

and

CREATE TRIGGER [dbo].[Table_1_DelLock]    
   ON  [dbo].[Table_1]
   INSTEAD OF DELETE
AS 
BEGIN
      ROLLBACK
      RAISERROR ('Table is locked.', 16, 0)
END

This has not been implemented yet and we are trying to make sure we have it right the first time since there are potential legal ramifications if this data gets corrupted or lost.

Anyone able to see any holes that would allow someone to edit the data in this table? The only one I am aware of at this point is that the table can be truncated without activating the delete trigger. The best answer I have to that at the moment is to establish a FK reference to this table.

Any potential performance problems that stand out?

Suggestions for a better approach?

Thanks for the assist.

EDIT:

I should note that access to the database will be restricted as tightly as we can make it. However there is always an account with DBO and our SAs generally don't know much about what we are doing with any given database. I know that people with these permissions can circumvent anything we put in place, the goal at this level is to prevent accidents. I should also note that we want it to throw an error if someone attempts an illegal update, it should not fail silently or perform a partial update on the allowed fields.

You say that there's someone who's going to have DBO access, say you're trying to prevent accidents, rather than malice, but then you're also talking about someone truncating the table - surely someone would only attempt a truncate maliciously, and if they're already DBO, as you say, there's no 100% solution. — Damien_The_Unbeliever, Apr 06 '11 at 12:26
@Damien We have had accidental truncates occur in the past via incompetence/ignorance rather than malice. Chalk it up to some under informed/overly paranoid policy makers but the app developers are almost never the DBO and rarely even have access to log onto the prod servers. The people who do have access almost never know what is actually going on with a given database and have managed to screw things up in the past. We are just trying to close as many doors as possible on this one. — Rozwel, Apr 06 '11 at 13:26

score 2 · Accepted Answer · edited May 23 '17 at 10:33

2

Adding a trigger to your table for insert or update may solve this problem. You can also check user account in the database and have restrictions to access the tables.

EDIT:

You can see the pros and cons of having a trigger here. You can also see its performance implications here.

edited May 23 '17 at 10:33

Community

1
1

answered Apr 05 '11 at 19:58

Shankar Raju

4,356
6
33
52

Thanks for the links. Some good info there most of which makes me think we are on the right track. – Rozwel Apr 06 '11 at 11:57

score 0 · Answer 2 · answered Apr 05 '11 at 20:04

0

seems complicated, and possibly having a fuzzy edge.

why not just silently replace all updated values with the old value?

in oracle, on the update trigger, I would write something like this:

:new.id := :old.id;
:new.data1 := :old.data1;

etc.

then make sure no user has delete privilege on the table.

answered Apr 05 '11 at 20:04

Randy

16,480
1
37
55

Failing silently is not an option. If someone attempts an illegal update they need to be notified, not just have all or part of their update ignored. Permissions will be restricted as tightly as we can make them but there is no way to lock absolutely everyone out, and most of those who can not be locked out in our environment won’t have a clue as to what this table is for or the rules involved with accessing it. – Rozwel Apr 06 '11 at 12:02

How to limit updates allowed on a table

2 Answers2