Is there a way to avoid row deletion on an specific table using constrains or triggers?

Question

Is there a way to avoid row deletion on an specific table using constrains?

I'd like to (for example) deny row deletion if the id is 0,1 or 2

This is in order to avoid users deleting master accounts for an application, and I'd like to avoid it even if someone tries it (by mistake) using sql directly.

Thanks!

EDIT:

The whole idea of this question is not to touch the application. It's not a matter of security, I just need to know if It's possible to do what I asked with constrains or any other thing that SQL Server has (It does not need to be an standard db solution).

EDIT 2:

Code samples are very, very appreciated :D

Answer 1

You could try filtering your queries by use of a function that checks to make sure that the user does not try to delete your master account.

Answer 2

You do this by writing a database trigger that fires on DELETE for the table in question. All it needs to do is throw an exception if the ID is invalid.

Answer 3

As far as enforcing this in a constraint, my solution would be to create a dependent table, so referenced rows cannot be deleted.

CREATE TABLE NoKillI (
  id INT NOT NULL, FOREIGN KEY (id) REFERENCES Accounts(id) ON DELETE RESTRICT
);
INSERT INTO NoKillI (id) VALUES (0);
INSERT INTO NoKillI (id) VALUES (1);
INSERT INTO NoKillI (id) VALUES (2);

Now no one can delete rows in Accounts with id values 0, 1, or 2, unless they first delete the corresponding rows in NoKillI. You can restrict deletions against the dependent table using SQL privileges.

Answer 4

If you don't trust your users, add security.

1. Add a stored procedure that allows the users to delete the rows they want with, but disallow whichever you want according to your own rules. Then deny delete access on the table, and allow execute access to the sproc
2. Add a secondary table with foreign key references, call the table MasterAccounts, or similar, deny update/delete access to this table, and add references to it to the accounts in question, this will prevent anyone from deleting an account as long as there is a reference from this table to it
3. Add a trigger, as OrbMan suggests
4. Add a view where they can delete rows through, make the view skip all those accounts they are not allowed to delete, deny delete access to master table, and allow delete access to view

Having said that, if your users have access enough to talk to your database through SQL, then you're really just asking for trouble. You should tighten up security, and only allow access to the database through your application and established protocols. Then you have many options to avoid problems like this.

Answer 5

Are you sure it's true that you will never want anyone to delete these rows? Even yourself, or the dba? Or dbms maintenance jobs?

If it's only some users, then you'll need something like a users table, with permissions, so that it can be queried in the trigger to distinguish unauthorized users from authorized users.

Answer 6

I'm using the following trigger:

CREATE TRIGGER [dbo].[mytable_trd] ON [dbo].[mytable]
WITH EXECUTE AS CALLER
INSTEAD OF DELETE
AS
BEGIN
SET NOCOUNT ON
DECLARE @tn varchar(255)
SELECT @tn = object_name(parent_obj)
FROM sysobjects
WHERE id = @@procid;

SET @tn = 'Deletes not allowed for this table: ' + @tn;
-- Add your code for checking the values from deleted
IF EXISTS(select * from deleted where mycolumn = 1)
  RAISERROR (@tn, 16, 1)    
END
GO

Answer 7

The solution I prefer uses the relational model and its integrity rules.

For each record in Tbl_Account that cannot be deleted, I would add a record in Tbl_AccountMaster where Tbl_Account.id_Account is a foreign key. Tbl_AccountMaster is not made available for update, except by the database administrator. Nobody else can then delete the records from Tbl_Account that are linked to Tbl_AccountMaster.

EDIT: I just noticed the same idea was developed here. Thank you Bill!