How to create an uneditable database ?
-
Hi: I am given the task of conceptualizing a database for medical purposes and a major requirement is: All records once entered cannot be edited, not even by the 'owner'. How do I go about it?, Is it possible? Are tools available to prevent the database from being edited? Any help will be much appreciated, TIA
-
When READ_ONLY is specified, the database is in read-only mode. Users can retrieve data from the database, but cannot modify the data. Because a read-only database does not allow data modifications
EXEC sp_dboption 'pubs', 'read only', 'TRUE'
-
Hi, thanks... but would it then be possible for the db owner to reset the database to 'edit' mode? If it is possible then it is not a viable solution. In essence, the users want a database that cannot be edited by anybody once the records are in... the people using it (doctors) are adamant that under no circumstances must the records be ediatble at all. TIA
-
I haven't tried it, but can a DB on a read only media like a CD be attached?
Once you understand the BITs, all the pieces come together
-
Hi, I work with Access for many of the small projects and yes you are right... data in a CD-ROM is a read only db, but Access isn't the right db for this job. I do not know about SQL server but someone I spoke with told me that there is a tool that could 'secure' a SQL server database to prevent any edits... TIA
-
You could put an Update trigger on each table, and have it raise an error. With the trigger in place, records could be added, but not modified.
- Art
-
Why don’t you restrict permissions to the users?
Per table/user you can give permissions to DELETE, SELECT, UPDATE and INESRT. With this, give permissions only to SELECT and INSERT records
Sometimes restricting access to information is done on the client application and not on the permissions of each user. This is a mistake, ... if you have a ODBC connection, you can use it anywhere to get access to all the information on the database, you just have to use something like Excel, ...
If a user must not see data from a table, take all his permissions for that table
-
As far I can tell, there is no way to completely prevent sysadmins and dbowners from changing records. There are ways to get around this.
Some ideas:
1. Gather all information twice or more. The other copies can be used to detect breach.
2. Set database to readonly. Have someone other than the sysadmin audit changes to the readonly status.
3. (From Art) Throw an error from an Update trigger. I would add that you need to audit when that trigger is changed.
4. Would some other database product do what you want?
I think that we would all like a world in which we do not have to rely on the honesty of others. That isn't this world. The best that we can do is to make sure that someone is looking over every other person's shoulders. You set in place controls that prevent the changing of data and then provide for some means to audit. Any unauthorized change from this protected state is grounds for immediate firing.
Russel Loski, MCSD
Russel Loski, MCSE Business Intelligence, Data Platform
-
I don't think you can secure it completely but you can detect changes. A trigger as suggested will work except with outright sabotage where someone disables the trigger! To cover that, have an encrypted validation field (a hash total or something) which you gui compares with the actual data. Any discrepancy then generates an alert and you will need to do a restore (or look it up in a 'shadow' database on a different server with completely different permissions).
-
Let me put this Plain and Simple, a SysAdmin CAN'T Be prevented from changing the Data. Period
sysadmins are the DB GODS if you want to have an analogy!
The reason is simple, What would you do if something goes wrong? At that point you want to have all the tools AND permissions to fix it.
Same thing happends with Network Admins.
So you will have to trust someones integrity and a team that wach each other!
Sorry but that's how it works!
Now there are alot of things that you could do to make it harder Like above suggestion I could add setting the sa password to a long random value unknown or difficult to remember but even that has some workarounds.
Just my $0.02
* Noel -
Hi joegrass,
As a physician, and a SQL Server programmer, I have a somewhat different view of things.
First of all, you need to be very concerned about the FDA's 21CFR part 11 regulations for clinical software.
Second, in the real world, medical personnal can freely change or add to paper records. These changes may be very difficult to detect, especially when using the same pen, and making the edits on the same day.
Computers are supposed to prevent the second problem by auditing all changes made to the records. This means a lot of extra code, preferably in triggers. Then you will also need way for users to read the audit data.
To avoid this problem, many people opt for the quick and easy approach, which is: allow inserts, but never allow edits or deletions. Then you may not need auditing. This approach will not endear you to the users of your software, however. Everyone makes mistakes in (or additions to) data entry, and there is a real need to provide edit or delete capability for some level of user. Of course, this means auditing is needed again.
It's true, there is no way to prevent a sysadmin / database owner form editing anything he wants. It's a really bad idea to have a database that can never change, or data that can't be cleaned. I can imagine all sorts of disasterous scenarios...
However, it is simple to give only INSERT/SELECT perms to users. Sysadmins and dbo's will still be able to edit the data. In practice, I suggest that you include auditing, and also make regular read-only backups of your data, and archive these backups in a safe place offsite. If you have these in place, then you can have the option of editing data, even if you only allow this access to administrators.
If your client is really insistent, you can install the system onsite yourself, and not give dbo or sysadmin access to anyone at the institution. You will have to make sure the SQL Server is locked down as much as possible, and give some management perms (backup, etc) to someone. Or create SPs that wrap maintenance functions, and give them EXEC perms on the SPs. And you can lock down the computer box, prevent them from logging on with any permissions, and control it remotely (PC Anywhere, etc). From their point of view, the system will then be read-only, but you will still be able to put out fires (for a fee of course).
HTH,
Rich
-
Also, what is considered an EDIT? If I delete a record, usually that's considered editting it. If a database can't be editted, the DBA or anyone else will never be able to delete records. The disks will get full and you'll have to detach the database, delete the .mdf/.ldf files and recreate it.
Bottom line is, SOME ONE is going to have to be able to edit the database. Even inserting a new record is a form of editting.
-SQLBill
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply