Using Triggers to "protect" data

Question:

---

There's a leak in database security, how can I protect myself from foulplay through a hole that is there by design ?

Answer:

---

As you mentioned that is by design and, for reasons everyone understands, I will attempt not to place the actual hack here, but I will show you how you can protect yourself against it.

It is actually pretty simple, you setup a "trigger" that responds to delete actions, for security reasons you might be wise to close the hole up entirely or if you must have the hole open because you give your clients options to filter records through a form that "gets" information then you NEED these triggers for both Update and Delete actions. I will give an example for a table called Employees.

Creating the trigger:

Simply run this script in the query analyzer for each of your tables that need a multi-delete block.

CREATE TRIGGER Employee_Delete ON Employees
FOR DELETE
AS
IF (SELECT COUNT(*) FROM Deleted) > 1
BEGIN
RAISERROR(
 'You cannot delete more than 1 employee at a time !', 16, 1)
ROLLBACK TRANSACTION
END

Note that after you setup these triggers, you cannot use features like delete multiple users or articles, but then you can create a stored procedure that breaks up the list in the where clause and deletes them one at a time.

You can learn more about triggers here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vdtsql/dvconworkingwithtriggers.asp

Ideally you want to catch the hack on the webserver and LOG him, instead of catching him in the database giving the hacker more clues to how you setup your database.