CODEWITHSUNDEEP
sql-server-2005
pdiddy
pdiddy

Reputation: 6297

Sql prevent row being modified

Is it possible to prevent any changes to a row in sql?

Let's say the record is created. But once it's created I don't want the record to be modified ever.

So the user can still try to do a update table set column = value .. but that would raise an error preventing it to be modified something like that.

Upvotes: 2

Views: 2582

Answers (3)

Ingo
Ingo

Reputation: 36339

The easiest way would be to REVOKE UPDATE ON TABLE, if SQL server allows for such fine grained access control.

Note that a malicious user could still DELETE, then INSERT another "changed" record.

Upvotes: 1

SQLMenace
SQLMenace

Reputation: 135171

yes you could do it with a trigger provided that the user doesn't do something like this

alter table disable trigger
update table...

you can also deny update on that column like this (SQL Server 2005 and up syntax)

DENY UPDATE ON OBJECT::TableName(ColumnName) TO UserNAme;

Upvotes: 3

Randy
Randy

Reputation: 16673

you should put a trigger on the table.

the trigger can either replace any new values with the old values, silently refusing the update, or it can throw an exception to the user saying update is not allowed.

OR

you can assign INSERT privilege to the users, but not UPDATE privilege.

Upvotes: 2

Related Questions