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

I have a stored procedure which is doing开发者_如何学C 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

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

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

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.

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.

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

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.

Here's yet another approach, which I think is fit for the particular case when a procedure is to be called by a user directly rather than from an application.

I must say, it proposes less trouble for the user while more (maybe, disproportionately) for the developer in comparison with most other suggestions. You decide whether it suits you.

Anyway, here goes.

First, you create a special table, CriticalCalls, to register calls to critical procedures. The table would have a structure like this:

SPID int,
ProcName sysname,
CallTime datetime

Basically, the idea is that a critical SP should be called twice: first it registers its call and informs the user to repeat the call within a certain interval of time as a confirmation of their intention, and with the second call, if made accordingly, it actually proceeds with completing its task.

So the starting part of every critical procedure would have this logic:

IF NOT EXISTS (
  SELECT *
  FROM CriticalCalls
  WHERE SPID = @@SPID AND ProcName = @ThisProcName
    AND GETDATE() - CallTime BETWEEN @LowerCallTimeLimit AND @UpperCallTimeLimit
    /* the actual test for the time interval might be somewhat different */
) BEGIN
  ...  /* upsert CriticalCalls with the current time stamp */
  PRINT 'To proceed, please call this procedure again within...';
  RETURN;
END;

DELETE FROM CriticalCalls WHERE SPID = @@SPID AND ProcName = @ThisProcName;

...  /* proceed with your critical task */

Actually, I think, it would be best to use a dedicated SP (named CheckCriticalCalls below) for all manipulations with CriticalCalls, including all the necessary modifications. CheckCriticalCalls would receive the name of the procedure to be checked and return a sort of flag showing whether the specified procedure should perform its real operation.

So it might look rather like this:

EXECUTE @result = CheckCriticalCalls 'ThisProcedureName';
IF @result = -1 BEGIN
  PRINT 'Call me again';
  RETURN;
END;

...  /* go on with the task */

The idea behind setting the lower limit of the interval is merely to prevent the user from calling a critical procedure twice automatically, i.e. by executing two identical EXECUTE... lines in one batch. The upper limit, of course, is necessary to 1) ensure that the user confirms their very recent intention to perform the critical operation; 2) prevent execution if the existing record in CriticalCalls is actually left there from a past session with the same SPID.

So, basically, an interval of 1-2 seconds to half a minute would seem quite natural to me. You might pick different figures instead.

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.