Prevent Truncate, Delete, Update statements
-
Hi,
We recently had an incident where a co worker issued a Truncate Table on the the incorrect table by mistake.
I landed up having to restore a huge backup to get the single table back...what a story...
I would like to prevent commands like Truncate Table, Delete and Update statements from being issued on a database.
What would be a cleaver way of trying to prevent this from happening again?
ie. making a users with db_datareader database role and then if you need to do a Truncate, Delete, Update use a user with db_owner, sysadmin rights
Cheers,
-
- also keep in mind, you'll have to grant exec-rights for the storedprocs and functions.
- probably developers will just end up using the overauthorized user unless you change it's password every week or so.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
No reason to make anyone execept DBAs/Administrators members of db_owner or sysadmin. Users should be able to do everything they need through stored procedures for which they only need EXECUTE permission. Developers could be members of db_datareader, db_datawriter, possibly db_ddladmin in dev only (not prod).
Greg
Greg
-
The minimum permissions would be db_datawriter for deletes and updates, and db_ddladmin for truncates. Or you can create remove all access to the tables and only grant execute permissions to procedures that do the work.
The most locked-down environment I worked in forced developers to create change scripts for everything. One QC person was the official change promotion person and would run all scripts on a test system (separate from the dev system). If everything passed QC the same person would run the change scripts on production. Developers were not allowed to touch test or production. This is expensive in terms of manpower and hardware, but it is pretty reliable.
You can't prevent people from being boneheads, but you can get backup software that lets you perform object-level restores instead of having to restore the entire database when they screw up. Quest's SQL LiteSpeed is one, there are others but I don't know exactly which ones have this capability. There are also products that focus on the transaction log, such as LogPI, that may be able to undo a specific action.
-
I'll second the point that access to change data on a production database should be severely restricted. Our DBA's can only change data if they login with a special ID - their everyday ID's do not have write access to production data. Anytime users ahve full access to production data, you run the risk of inadvertant data loss. If you work for a company that is subject to SEC regulations, unlimited access to production data could be seen as violating Sarbanes-Oxley rules.
As a reminder, TRUNCATES aren't logged, so log recovery packages won't be able to undo them.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply