Read Only SQL Table



  • This gem was unearthed while performing some maintenence on an old application from a long-departed developer. Apparently, managing access via user permissions was a bit too subtle for his taste....

    readonly trigger

     



  • Is there a better way to guard against a user with db_datawriter inadvertently editing the table? (Even if there isn't, the error message should ideally explain how/where the application code depends on it. Well, 30007 suggests that they might use different error numbers and maintain a central list of which ones pertain to what.)

     



  • Definitely not subtle enough. A better solution would be to accept the insert/edit/delete, and then silently undo it.



  • @LoremIpsumDolorSitAmet said:

    A better solution would be to accept the insert/edit/delete, and then silently undo it.

    As I can't hit you over the internet, where are you located?



  • @locallunatic said:

    @LoremIpsumDolorSitAmet said:
    A better solution would be to accept the insert/edit/delete, and then silently undo it.
    As I can't hit you over the internet, where are you located?
    Blakey's house.



  • @lscharen said:

    This gem was unearthed while performing some maintenence on an old application from a long-departed developer.

    Got something similar on our db right now.  Background: Primary config table keeps getting changed to test multiple configurations.  Multiple people are working on it and quite a bit of time was being spent between people fighting configuration settings.  Revoked insert/update/delete privileges on the table and maintained changes to it via a ticket system.  Things have settled down since then and insert/update/delete privilege has been returned.

     @LoremIpsumDolorSitAmet said:

    Definitely not subtle enough. A better solution would be to accept the insert/edit/delete, and then silently undo it.

    Definately have this.  The admin account is subject to the same restrictions as everything else, complete with password expiration after a certain amount of time.  When the password expires, the user is prompted to change the password.  And again, with multiple people using the same account it got locked very quickly by failed logins when the password changed.  Initial solution of having unique logins didn't work.  Ultimately, I "solved" the problem by emailing out that the admin account's password could no longer be changed nor expired.  Application still has to be able to update the fields, so, there's a trigger at the db level that silently undoes any update.  Works quite well for our purposes and not considered a security issue as the server is development only anyway.

    ------

    So my takeaway from Ischaren's post... that table is critical in some fashion and should not be updated unless you *really* know what you're doing, the trigger is acting as a not so subtle reminder.


Log in to reply