Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2008
    Posts
    3

    Unanswered: protecting records from being updated and deleted

    Hi I am using sql server 2005 express and would like to keep all my fields from being both updated and deleted.

    In other words, once I create a new record, I would like to have it protected from being deleted and I dont want the field values to be updated/changed from the values initially entered. Is there a way to this without running triggers or changing database permissions and user roles?

    I tried making the database read-only, but then of course i cant add new records.

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There is no way to prevent the administrator and/or database owner from making changes, since they actually control the permissions within the database. You could certainly do this for "ordinary" users easily enough.

    Is this a homework question?

    -PatP

  3. #3
    Join Date
    Feb 2008
    Posts
    3
    no this isnt a homework question. im just new to sql server 2005 (only been on it for about a week now).

    Anyways, here's the situation in detail.

    I work in a research lab where one of our machines automatically collects data and and writes it into a sql server database. The problem is that in order for this data to be approved of by the FDA, nobody should be able to delete the data or change it in any way; only add on to it. I was looking for a way to do this without changing permissions. The reason for this is because everyone in the lab uses this single computer and it is always signed on as the administrator.

    Is there anyway to change the permissions of the database administrator to deny them access to delete and update? Can anything be denied to the administrator?

    Or will we have to create a seperate windows user/login and deny permissions to them?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    No, by definition there is no way to remove permissions from the SQL Server Administrator. There are many options that would get the same net effect, but those all have one or more drawbacks that are not good ideas.

    My very strong suggestion would be to put the database on a server, a machine with few if any administrators. Grant the login used by the data gathering software permission to INSERT data. Grant the login(s) used by the researchers permisson to SELECT data. Do not grant any login explicit UPDATE or DELETE privleges.

    There is a short section in FIPS that describes the appropriate permissions, and if I remember correctly there are even sample scripts for SQL 2000 that would work just as well in the SQL 2005 environment.

    -PatP

  5. #5
    Join Date
    Feb 2008
    Posts
    3
    Sounds good Pat. ill check those out. Thanks for your help and suggestions!

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    My initial reaction to this thread was
    INSTEAD OF TRIGGER
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by bkuhl09
    The reason for this is because everyone in the lab uses this single computer and it is always signed on as the administrator.
    I think that is your problem right there. We have a different compliance requirement (ISO9001) but if we had anything like that we would have to change it or risk losing our accreditation. I would go as far to say this should be considered a problem wider than just SQL Server.

    Just my 2 cents - I really think you should review this, perhaps implementing some or all of Pat's suggestions.

  8. #8
    Join Date
    May 2004
    Location
    Columbus, OH
    Posts
    59
    My 0.02 worth...

    I don't know what level of access you have to the source code of the application that you are using, but if you control the application you control what the users can do to the data....from the application of course<g>

    Controlling what the user can do in the application is easy. Having them accept it can be hard. You could remove the <delete> and <save> buttons and only leave them with <insert>/<create>.

    Just as a side thought(...and one that could be considered a bit crazy), a more extreme solution would be to re-design the database. You could have a table where all of the data goes in originally and a second table that contains any modifications to the original table. I have not given this a bunch of thought, but it seems do-able.

    But, if you can not change the application then a redesign of the database is really out of the question.
    Life....Just another opportunity to live another day like a pirate....

  9. #9
    Join Date
    Dec 2002
    Posts
    1,245
    Create a Windows account (Domain\SQLAdmin, or Machine\SQLAdmin). Only you get the password to that account.

    Add it to the logins for SQL Server. Set it as sysdba.

    Remove the Builtin\Administrator account from the logins (or, simply uncheck the sysadmin option on the Builtin\Administrator account).

    A couple of other steps that you might need:
    1) If everyone is signed is as administrator and that's the way that they are inheriting permissions to update the database, you will need to create a new Windows group, add everyone to the Windows group that needs access and assign appopriate privileges to the group

    2) Watch out for other services/processes that use the BUILTIN\Administrator account. Some services that run as SYSTEM get into the database through the BUILTIN account. You can add an account called NT AUTHORITY\SYSTEM to get around this.

    Regards,

    hmscott
    Have you hugged your backup today?

  10. #10
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    I'm with Pootie on this one. I can't imagine that if the FDA requires what you say they do, that they would feel comfortable with the data on a PC that "everyone" uses and signs on as administrator. Seems to me that no matter what you do (short of changing the way the other users log on) that any change you might make could be undone by anyone else that chooses to log on as admin and do so. Probably they wouldn't, or might not be well-versed enough to know how, but people learn stuff over time, and I can't imagine that any certifying body would allow general users to log on as administrators on a machine that is used for sensitive data storage.

    Even if you get away with it to start, they are likely to audit at some point, and that probably wouldn't go well.

    I'd suggest getting a separate server and set permissions appropriately, including limiting the use of the administrator account(s), as has been suggested, or make similar changes to the existing server and inconvenience your "other users" for a while until they get used to the different requirements.

    Besides, it's kinda fun to piss people off by changing little things like logon procedures, especially if you can make them believe that the change has been made for some random and non-critical reason.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

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