revoke permissions TO stored procedure

[teogra@yahoo.com]

I have written an stored proc that reads from a text file and executes
the script as dynamic sql.

If the text file contains malicious code,I want to be able to detect it
and prevent the stored procedure from executing.

I've tried revoking delete,insert,u pdate rights all tables in the
database to the user .
I then granted execute rights to the stored procedure for the same
user. But the user is still able to delete a record from the table by
executing the stored procedure.

Is there any means to I revoke,insert,d elete ,update rights to a stored
proc?

[louis]

Re: revoke permissions TO stored procedure

Once a SP is called, it has already been compiled. Changes to it while
it is running has no effect.

Inside the stored procedure, you can write conditional logic to abort
if necessary (using the RETURN or RAISEERROR statements).

However, a patient hacker can try hundreds of ways to bypass whatever
detection logic you write. If this a customer requirement, quote him
the AOL commercial that "he's just asking for his hard drive to make
noises like a yeti..."

[Erland Sommarskog]

Re: revoke permissions TO stored procedure

(teogra@yahoo.c om) writes:[color=blue]
> I have written an stored proc that reads from a text file and executes
> the script as dynamic sql.
>
> If the text file contains malicious code,I want to be able to detect it
> and prevent the stored procedure from executing.
>
> I've tried revoking delete,insert,u pdate rights all tables in the
> database to the user .
> I then granted execute rights to the stored procedure for the same
> user. But the user is still able to delete a record from the table by
> executing the stored procedure.
>
> Is there any means to I revoke,insert,d elete ,update rights to a stored
> proc?[/color]

It's not wholly clear what you are trying to accomplish, but the answer
to the last question is no. You can revoke rights for the procedure
owner.

But for dynamic SQL, it's the rights of the user that applies, so meddling
with the procedure owner won't help. Just grant the user the rights
he needs, but not more.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp