Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2012
    Posts
    2

    Unanswered: Disable updates on a column

    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.

  2. #2
    Join Date
    Jan 2012
    Posts
    2

    Bump

    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.

  3. #3
    Join Date
    May 2008
    Posts
    277
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •