Prevent Accidental Table Data Deletion – How do we secure our production database against simple errors that could cause quite a problem. As a developer, I mainly work on my database while being in a development environment. It is only after the development is one that some of the work is done against a live database. Let’s have a look at some solutions using SQL Server 2012.
Prevent Accidental Table Data Deletion
Okay, here is an example of an error that could occur. But first: All characters appearing in this work are fictitious. Any resemblance to real persons, living or dead, is purely coincidental. Right, now that we have this out of the way, we can continue. Assume that Bill works for ACME Research Company that provides medical equipment to reps. He isn’t a database admin per se, but does support the database to an extent. Bill has to remove an entry from the Sales Order Details table. The reason for this is as a result of an over zealous developer who performed an erroneous insert directly into the table while testing. The developer inserted into Live instead of development (He probably didn’t read Customizing SQL Server Status Bar – Know Your Connection).
So Bill identifies the incorrect data entry’s Sales Order ID and performs the delete. The only problem was that Bill didn’t notice that he accidentally selected part of the T-SQL Delete statement.
The result is catastrophic. At this point, ACME Research is in a world of hurt. Bill is considering taking an extended lunch and never returning. I know what you are thinking. They can probably restore from the latest backup. Sure they can, but consider the exercise in doing that. Assuming that the latest backup even restores correctly, they would probably need to halt production. Then they need to try and recover all the sales orders captured since the last backup was performed. The point I want to make is that prevention is better than cure. So what can you do?
SET NOEXEC ON
There is a nice article by Pinal Dave here that explains this option. In a nutshell, NOEXEC can be used to validate the syntax of a SQL statement without executing it.
This could be used to validate that the syntax is correct, but I’m not sure that this could prevent the accidental deletion of a whole table of data. It is a great tip when running an update on a table. Updates could potentially also be a dangerous operation to perform.
Another option (and quite a nice one in my opinion) is to set Implicit Transaction on in SQL Server Options (Query Execution -> SQL Server -> ANSI).
Lets try and delete some data from our Customer table. As you can see, we are deleting CustomerID 1 and then doing a Select statement.
After we have run the delete statement, we can see that CustomerID 1 has been deleted. It is at this point that Bill was contemplating that extended lunch.
If Bill had Implicit Transactions switched on, he could have simply closed the query window. SQL Server Management Studio would then ask him if he wanted to commit the transaction. He could then simply click on the ‘No’ button and sigh a sigh of relief.
After clicking on the ‘No’ button, selecting the Customer record with CustomerID 1 shows us that the delete transaction has been rolled back and the record that was deleted is still safe and sound.
There are a host of scenarios where this could come in very handy. I would think that this option is best suited in a live or production database. Doing this in a development database might be a bit overkill. For more on Implicit Transactions, see the MSDN article.
It seems that the general consensus among the SQL Community is to create a trigger on important tables. These triggers are called prevention triggers. The first and most comprehensive article I read was written by Atif Shehzad. Seeing as my article only refers to preventing deletions, I’ll only show you this trigger. To see how to prevent accidental updates on all rows in a table, please head on over to Atif’s article on the subject. I have created the following trigger on the Customer table.
What is great about the trigger is that it will allow me to delete a single row, or a few rows. As long as there is a WHERE clause, the delete will work.
But as soon as the WHERE clause is omitted from the Delete statement, or where the rows to delete are equal to the row count in the table, the trigger will prevent this deletion from taking place.
This means that we can’t delete everything from this table. So far we have seen a few options for preventing whole table deletes. Whichever method you decide to use, should be based on the level of security you need. Admittedly you will not give all users administrative rights to your live database. The thing is this, SQL admins are sometimes only human too. And humans make mistakes.