Allow user to update some records in table, but not all?

Question

Suppose you have a lookup table that has a couple default options that your Business Logic layer depends on, but you want to allow the users to add/update/delete records in that table. For example, a CurrentStatus table that will be pre-populated with the following, but allow the user to update the text of those records as well as add/update/delete extra records:

ID  | StatusName
-------------
1   | New
2   | Inactive
... | ...

What approaches could be taken to allow this flexibility? I don't want to "hard code" the pre-populated records because the users may want to redefine it down the road. Predominately looking at SQL Server, but ideally the approach should be server independent.

Answer 1

This type of permissions logic cannot be implemented using ms sql server let alone a generic rdbms. This type of logic has to be enforced at the application level.

You could probably do something with triggers as a deterrent, but that would only protect against accidental changes.

Answer 2

The easiest way would be to go through a stored procedure for all writes to this table-- you could enforce restrictions in the stored proc's logic. You can GRANT different rights to the stored proc vs. the underlying table, so that your application's users can only make changes via the sproc. See http://www.sommarskog.se/grantperm.html

You could also probably create a view and do writes through the view, although I've never tried this approach before so it may not work.

A trigger may work too, although triggers tend to introduce additional complexity into apps-- so I generally like to avoid them.

The core element of any of these solutions is that nothing is foolproof-- a determined user with sufficient privileges can change what he wants. But as long as you can constrain your app to a particular user (or set of users), and you can trust your high-privilege users to not mess things up using ad-hoc queries, then you're probably OK.

Of course, you also need to change your schema so that, however you choose to enforce restrictions, your code can can know what rows to restrict. The "additional column" or "additional table" approaches recommended in other answers are two sensible options for this additional schema. The "additional table" approach seems theoretically cleanest to me, but might result in sub-optimal query plans since every query would require a UNION across the two. It's the kind of thing you'd want to test before using.

Answer 3

Add a field called "read-only" to the table and check it in the application. Don't allow updates/deletes on "read-only" fields. New fields can be added easily. Short sweet and simple.

Answer 4

A couple options--you could wrap up the Insert/Update/Delete in stored procedures, which check for the "special" values you don't want the user to overwrite. All edits against your lookup table would have to drive through your stored procedures.

Another option would be to add a column to the table, call it "standard" and make it a yes/no flag. If it is yes (indicating a standard item you don't want the user to change), you can easily disallow updates or deletes. If it is no (indicating that it's a value the user has created), you can allow edits to pass through. This gives you the ability to define the data without having to hard code anything special with regard to item ID's.

Answer 5

1. Add one more column, "AllowChange"
2. Add INSTEAD OF UPDATE TRIGGER on the table to intercept the action.
3. Read the AllowChange column and update or discard the record accordingly.

INSTEAD OF UPDATE TRIGGER fires before the update action, so you have full control of what to do.