Preventing manipulation of data
-
I have a database where certain tables store information which are critical in nature. They represent commercial transactions which have taken place during the day. Now, I want to prevent anybody (even the admin!) from being able to manipulate the data in any manner. If I deny such access to all the users (can I:confused
then how can I do the transaction logging from my application? Essentially, what I require is that everybody except my application should have read-only access to these tables.How can I achieve the same?
Thanks,
Krishnan
-
Hello,
It is not posiible.
You can implement a sort-of solution by using Application Role but you still have sysadmins on the server, SA for example. Additionally you will have an application developer who implements the application role and nothing will prevent this developer to use it from his own little application.
This is a theoretical question on the interaction of basic concepts: Security and System Administration. Someone (sysadmin) has to be able to recover the data if needed. The application is not able to do that on its own.
Another thing: if the data are critical in nature, did you implement password-protected backups?
Yelena
Regards,Yelena Varsha
-
Krishnan -
Yelena is right, there is no way to completely remove the ability to modify the data. Someone has to be able to get in and work with the plumbing!
If it is essential that you have a way to validate that the data has not been changed then you need to implement an auditing solution. Though there are probably folks out there that could "work" around that as well given the motivation to do so.
Joe
-
You could implement a trigger that would just rollback any sort of changes.
But to be sure I would suggest keeping a pristine copy of the data offline (Maybe in an access DB or flat file) and then loading the data every night. Are you talking about a lot of data?
-
This is where company confidentiality agreements come in. If the data really needs to be kept confidential, you should have your sysadmins sign an agreement that they will not look at the data unless asked.
As stated above, the database can then be audited. This solution of course doesn't really lock anybody out, it simply says "if you look, we'll find out and you can be fired for doing so".
The only other way I can think of would be to create the database on its own sql server instance for which you would have no admin users but yourself. This is not a very good use of a sql server licensing fee though.
-
If you don't want Sysadmins being able to view the data, encrypt it, and don't tell them the key.
Essentially all users of a system should have separate logins. If your using windows authentication, use groups to simplify applying security. Then GRANT the UPDATE, INSERT, DELETE permissions to the login your application uses. DENY INSERT, UPDATE, DELETE to the logins that you dont want to be able to change the data.
Use Security to prevent users making changes.
Implement encryption to prevent unwanted eyes from seeing data.
Implement auditing, reports, and alerts if you want to see what users do, and be alerted to bad behaviour.
Its really the combination of all three that can save you (and the company) if someone does something wrong.
Julian Kuiters
juliankuiters.id.au -
Thanks for the replies.
I too felt the same that, restricting "everybody" from manipulating the database is not possible. The next thing that came to my mind was encryption. However, by encrypting the data, I cannot generate reports and other stuff directly by issuing plain SELECT statements. I need a middle-tier logic which could handle the encryption and regenerate the data in some temporary table or a view. As of now, I am keeping this as the last option.
Regarding the other suggestions made, I feel the simplest way out would be to implement the auditing option. However, I am not completely conversant with this. Furthermore, my application primarily works on MSDE as the database size requirements of our customers aren't that great. Does MSDE support auditing?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply