Prevent update on a column - SQL Server Forums

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)

Prevent update on a column

Author

Topic

pug2694328
Posting Yak Master

166 Posts

Posted - 2011-04-07 : 11:46:41

I have a table where I want to prevent any update on the data in a specific column.

So if, when the row is inserted the column contains 'blah' I want to prevent users from successfully changing 'blah' to 'blahblah'.

What's the best way to enforce this rule?

Thanks!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-07 : 12:00:07

You can certainly do it using a trigger on the table - by rolling back the update if the column value has changed. Rolling back in a trigger is like two operations - first the insert/update is done, and then it is undone. So, that may not be the most efficient way, but it is reliable and will work.

The other way is to change the column permissions - but there are holes in that approach, which might still cause the column to be updated as far as I can tell. But some of the people on this forum who are experts on security and permissioning may be able to advice as to how to accomplish that reliably.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-07 : 12:57:55

Denying update permissions is the way to go, example:

CREATE TABLE a(a INT NOT NULL, b CHAR(1) NOT NULL)

GRANT INSERT,SELECT,DELETE ON a TO PUBLIC
DENY UPDATE ON a(b) TO PUBLIC
GRANT UPDATE ON a(a) TO PUBLIC

SETUSER 'guest'        -- remove or comment out this line to test dbo/sysadmin

INSERT a VALUES(1,'A') -- succeeds regardless of login
SELECT * FROM a        -- succeeds regardless of login

UPDATE a SET a=2       -- succeeds regardless of login
UPDATE a SET b='B'     -- fails for guest, succeeds for dbo or sysadmin

DELETE a               -- succeeds regardless of login
SELECT * FROM a        -- succeeds regardless of login

DROP TABLE a           -- clean up

If you DENY permission on a specific column you have to ensure your GRANT that permission on the other columns, otherwise other columns will be DENY'd (at least in my brief testing)

You can also DENY or GRANT permissions to specific logins, rather than public.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-07 : 13:09:58

The thing that I have worried about in denying permissions is that that does not seem to be doable to the user who owns the schema (and may be for other categories of users too)?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-07 : 13:19:10

No, but that's easily remedied by creating everything under a specific login that's never used for any other purpose. As long as normal logins are not members of db_owner the DENY permissions will work.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources