Is there an "are you sure" for stored procedure execution? :)

Question

I have a stored procedure which is doing a lot of delete. Hundreds of thousands of records. It is not going to be runnable from the application, but still, i am concerned, that one of my clients accidentally runs it (i had problems earlier due to their "curiosity") :D

Yes. there are backups and stuff like that, but I was thinking .... not to scare them ... is there a way to ask the user "are you sure?" before executing it? :) thanks

Answer 1

I guess you could have a parameter called "confirmation" that requires a specific string (e,g, "I know what I am doing") to be passed in, if it is not set, or is set incorrectly, just return from the procedure without executing the main code. Not exactly what you wanted, but it is an option.

eg - (untested and probably terrible syntax)

CREATE PROCEDURE dbo.mySproc (
@Confirmation Varchar(100)
) AS
BEGIN
    if(@Confirmation <> 'I know what I am doing')
    BEGIN
        return;
    END
    DELETE from table_name where condition
END

Answer 2

In short, no.

The theory goes that anybody with permissions to find and be able to run a stored procedure, should be allowed. It would be better to restrict permissions so that those with an excess curiosity do not have the permissions to run this.

The other, less secure, option would be to require a pre-defined secret that needs to be passed as a parameter - of course they could just script the stored procedure off to find the secret though...

Of course, the other point would be: If it's not callable, why include it? After all, when you come to do admin type tasks, you can have the statements scripted off as a file that you can keep secure on your own machine

Answer 3

You could use a bit input called @UserKnowsWhatTheyAreDoing and check to see if it is true before executing. If it's false, print a friendly message and return gracefully from the procedure

Answer 4

You can add a @reallyReallyReallyDelete parameter to the sproc which will serve as a safety measure: if it's set to YesYesYes will actually commit the transaction.

Answer 5

The procedure may require an argument with a specific value, like 'Yes I know what I'm doing'. Or it may look for a row an a special table with a similar confirmation and a recent timestamp.

Answer 6

use a multi-tiered pronged approach:

1) control execute security, like:

GRANT EXECUTE ON [dbo].[yourProcedure] TO [userxyz]

2) use a really descriptive/scary procedure name, like

CREATE PROCEDURE Will_Delete_All_Your_Data ...

3) put a large eye catching comment at the start of the stored procedure

--NOTE, will delete all of your data--
--NOTE, will delete all of your data--
--NOTE, will delete all of your data--
--NOTE, will delete all of your data--
--NOTE, will delete all of your data--

4) make the user pass in an obfuscated special access code:

CREATE PROCEDURE Will_Delete_All_Your_Data
(
    @SpecialCode varchar(30)
)

IF @SpecialCode!=CHAR(83)+CHAR(112)+CHAR(101)+CHAR(99)+CHAR(105)+CHAR(97)+CHAR(108)+CHAR(67)+CHAR(111)+CHAR(100)+CHAR(101)
BEGIN
    RETURN 999
END
...

FYI, the special code must be 'SpecialCode' or RETURN 999 is hit.

Answer 7

Do you need to REALLY delete them from the DB? If I can afford the extra space I'll put a 'Deleted' flag in my tables and a last updated column. This way if a record is accidentally deleted at least I can usually track it down and restore it fairly easily. Just a thought.