Grant and Revoke excute permission on xp_cmdshell from a SP

[Zukkster]

One of my stored procedures uses the xp_cmdshell to export data usig the following code

SET @cmdline = 'bcp reporter..' + @SummaryTableName + ' OUT "\\cmcukfs01\Userdata\Reporter\AccessApplication\Import Data\SummaryFile_' + @UserID + '.txt" -T -t "," -c'

EXEC master..xp_cmdshell @cmdline

This was written before we realised that granting execute permission to the public role for xp_cmdshell was not the best idea. My hope therefore was to surround this code with a grant and revoke execute permissions piece of code to the xp_cmdshell system stored procedure from within my stored procedure. Like this

SET @strSQL12 = 'Use master GRANT EXECUTE on master..xp_cmdshell to Public'
EXECUTE(@strSQL12)

SET @cmdline = 'bcp reporter..' + @SummaryTableName + ' OUT "\\cmcukfs01\Userdata\Reporter\AccessApplication\Import Data\SummaryFile_' + @UserID + '.txt" -T -t "," -c'

EXEC master..xp_cmdshell @cmdline

SET @strSQL13 = 'Use master REVOKE EXECUTE on master..xp_cmdshell to Public'
EXECUTE(@strSQL13)


The thinking being that rather than have a hole in my security by having xp_cmdshell permissions switched on permanently only users with execute permissions on the stored procedure would be able to briefly grant access.

The thing is I've given execute permissions to the public role and I'm not sure how to set the security in the master db to allow the stored procedure to do what I want while and not just end up creating the same security hole a different way - with potentially wider security implications.

Advice appreciated.

p.s. I've considered DTS to export but it creates different issues, so I'd prefer to use the exsisting code and sort out the security correctly rather than change the export process completely. Although I'll keep an open mind on that.

 

[dbomrrsm]

do you need to grant it to public - if it is needed by registered and authorised users then you could do a check to see if the person trying to run the proc is an authorised user - if they are grant them access to run and then revoke it.

Something like

declare @user varchar (25)
set @user = connected user
if connected user in (Select all allowed users from systemtable)
then 

SET @strSQL12 = 'Use master GRANT EXECUTE on master..xp_cmdshell to '+@user

[blue]DBomrrsm[/blue]