Início / dba / Perguntas / 201776
Accepted
VansFannel
Asked: 2018-03-21 02:53:04 +0800 CST

Conceder usuário do meu banco de dados para executar xp_cmdshell

  • 772

Estou trabalhando com o SQL Server 2016 e preciso permitir que um usuário MyDatabaseexecute xp_cmdshell. Eu faço o seguinte:

USE [MyDatabase]
GO

GRANT EXECUTE ON xp_cmdshell TO myUser
GO

myUseré um usuário de MyDatabasee quando executo o código acima, logado como sa, recebo o seguinte erro:

As permissões em exibições de catálogo no escopo do servidor ou procedimentos armazenados do sistema ou procedimentos armazenados estendidos podem ser concedidas somente quando o banco de dados atual for mestre.

Preciso adicionar o usuário ao masterbanco de dados? Não acho seguro.

Eu preciso disso porque quero escrever um arquivo de texto de um procedimento armazenado. Encontrei o seguinte artigo, http://www.nigelrivett.net/WriteTextFile.html , dizendo que posso escrever um arquivo de texto usando:

You will need to create the data to be output as in dynamic sql statements
The first command here creates or overwrites the file - the rest append to the file.
exec master..xp_cmdshell 'echo hello > c:\file.txt'
exec master..xp_cmdshell 'echo appended data >> c:\file.txt'
exec master..xp_cmdshell 'echo more data >> c:\file.txt'

Pergunto isso porque meu procedimento armazenado gerará uma string grande (nvarchar(max)) json e quando tento anotá-la com C# não recebo nada: nem o arquivo nem uma exceção. Eu pensei em escrever a string json dentro do procedimento armazenado (se eu puder).

O procedimento armazenado termina corretamente, verifiquei no depurador que recebo algo no parâmetro de saída json (eu sei porque o Visual Studio define que não há memória suficiente para mostrar seu conteúdo), o File.WriteAllTexté executado porque tenho um ponto de interrupção em isso, e também, o caminho é absoluto. Mas talvez haja algo que eu ignore e eu tenha que verificar tudo novamente.

sql-server permissions

2 respostas

  1. Best Answer

    Sugiro que você envolva o código que executa xp_cmdshell em um proc armazenado em seu banco de dados de usuário e assine-o com um certificado que tenha as permissões necessárias. Dessa forma, os usuários estão limitados ao procedimento armazenado do usuário e não podem executar comandos ad-hoc xp_cmdshell. As permissões do sistema operacional também são limitadas à conta proxy para membros da função não sysadmin. Essa técnica de certificação é detalhada no artigo Permissões de empacotamento em procedimentos armazenados de Erland Sommarskog .

    Abaixo está um script de exemplo.

    -- Enable xp_cmdshell and create proxy account
USE master;
EXEC sp_configure 'show',1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell',1;
RECONFIGURE; 
EXEC dbo.sp_xp_cmdshell_proxy_account 'YourDomain\YourProxyAccount', 'YourPr0xy@accountPassw0rd';
GO

-- Create certificate in master.
CREATE CERTIFICATE xp_cmdshell_cert
   ENCRYPTION BY PASSWORD = 'All you need is love'
   WITH SUBJECT = 'For xp_cmdshell privileges',
   START_DATE = '20020101', EXPIRY_DATE = '20300101';
GO

-- Create a login for the certificate.
CREATE LOGIN xp_cmdshell_cert_login FROM CERTIFICATE xp_cmdshell_cert;
CREATE USER xp_cmdshell_cert_login;
GRANT EXECUTE ON dbo.xp_cmdshell TO xp_cmdshell_cert_login;
GO

-- Copy cert to user database
DECLARE @cert_id int = cert_id('xp_cmdshell_cert')
DECLARE @public_key  varbinary(MAX) = certencoded(@cert_id),
        @private_key varbinary(MAX) =
           certprivatekey(@cert_id,
              'All you need is love',
              'All you need is love')

SELECT @cert_id, @public_key, @private_key

DECLARE @sql nvarchar(MAX) =
      'CREATE CERTIFICATE xp_cmdshell_cert
       FROM  BINARY = ' + convert(varchar(MAX), @public_key, 1) + '
       WITH PRIVATE KEY (BINARY = ' +
          convert(varchar(MAX), @private_key, 1) + ',
          DECRYPTION BY PASSWORD = ''All you need is love'',
          ENCRYPTION BY PASSWORD = ''All you need is love'')'

EXEC YourUserDatabase.sys.sp_executesql @sql;

ALTER CERTIFICATE xp_cmdshell_cert REMOVE PRIVATE KEY;
GO

USE YourUserDatabase;
GO

CREATE PROC dbo.CreateFile
AS
EXEC master..xp_cmdshell 'echo hello > c:\file.txt';
EXEC master..xp_cmdshell 'echo appended data >> c:\file.txt';
EXEC master..xp_cmdshell 'echo more data >> c:\file.txt';
GO
ADD SIGNATURE TO dbo.CreateFile BY CERTIFICATE xp_cmdshell_cert
   WITH PASSWORD = 'All you need is love';
GRANT EXEC ON dbo.CreateFile TO YourUserOrRole;
GO
ALTER CERTIFICATE xp_cmdshell_cert REMOVE PRIVATE KEY;
GO
    • 5

  2. Com relação ao aspecto da pergunta que trata de permissões relacionadas à execução xp_cmdshell, concordo com (e votei positivamente) a resposta de Dan . No entanto, dadas as novas informações sobre o motivo dessa solicitação (ou seja, escrever documentos JSON grandes), a segurança para xp_cmdshellse torna um ponto discutível, pois xp_cmdshellsó pode lidar com um máximo de 8.000 VARCHARcaracteres (menos se estiver usando um conjunto de caracteres de byte duplo e certos caracteres nele , mas esse provavelmente não é o caso aqui, apenas algo para estar ciente). Além disso, pareço lembrar que certos caracteres não funcionavam tão bem na linha de comando e não podiam ser escapados facilmente (mas atualmente não consigo encontrar nenhum).

    Portanto, talvez seja melhor aceitar a resposta de Dan para essa pergunta (já que está correta para a pergunta conforme declarado) e, em seguida, abrir uma nova pergunta sobre a melhor forma de exportar documentos JSON grandes, e posso transferir essa resposta para essa pergunta .

    Essa operação pode ser tratada facilmente via SQLCLR usando a Fileclasse.

    Por favor, veja a seguinte resposta minha, também aqui no DBA.SE, que inclui alguns exemplos de código para fazer esse tipo de coisa:

    Exportando resultados para XML

    Além disso, para quem não quer mexer na codificação, implantação, etc: existe uma função File_Write disponível na biblioteca SQL# (que escrevi) que trata disso e ainda permite passar a codificação desejada para que o arquivo possa ser salvo como UTF-8 com uma Byte Order Mark (BOM), por exemplo. Observe que esta função está disponível apenas na versão completa, não na versão gratuita. Esta é uma função bastante simples de criar, mas é melhor ter opções.

    • 2

relate perguntas