Hi all,

I've looked around the web to find out how to disable updates to one column only. Most of what I've seen has to do with permissions - granting select and insert ONLY to that column to users (deletion from the whole table is disabled).

Thing is, I haven't reached that stage of development where I create user roles. in fact, I was planning to have the application front end deal with authentication and permissions.

However, it is vital that this column not be updated for whatever reason, so I would be glad to here your solutions to this problem.

Thanks.

Hi all,

Sorry for the bump. Let me put it this way ... are altering permissions to a column as per user/role the only way to restrict updates to a column? If not, then please let me know what other method I could use.

Thanks.

If you literally want to "disable" updating a column, you can always write an ON UPDATE trigger that always sets the NEW value back to the OLD value. The value of the column would presumably need to be initialized upon INSERT, but then could never be changed. Somehow I doubt this is what you want, but then again....

If you're looking for some sort of role-based access control of who can update the column, then setting column permissions is the way to go. However, if you're handling authentication and authorization in the application layer and always connecting to the database as the same user, this will probably not be very useful; you will likely need to control who can update the column in the application layer as well.

Although, it also occurs to me, you could try disabling direct access to the table and control access to it using one or more stored procedures. You could then pass in the necessary parameters (such as the user role) and implement the necessary update logic in the stored procedure. Keep in mind the procedure would need to be owned by a role that IS able to update the table and would need to be defined as SECURITY DEFINER. If I'm not mistaken, the documentation has some cautionary notes about writing SECURITY DEFINER functions.