none
Grant para Procedure RRS feed

  • Pergunta

  • Caros,

          fiz uma procedure que restaura o banco automaticamente através de um arquivo .bak, porem para se restaurar um banco é necessário grants de dbcreator, o qual eu não gostaria de atribuir para o usuário. Gostaria de saber se existe alguma forma de que este usuário exsecute está procedure apenas com grant de EXECUTE nela.

           Por exemplo, no orcale (me corrija se eu estiver errado) se o usuário da procedure possuir grants de "dbcreator" qualquer pessoa que possuir grant de execute na procedure poderá executa-la, mesmo não possuindo diretamente grant de "dbcreator". Simplificando, gostaria de dar grant de DBCREATOR para a procedure e não para o usuário que vai executá-la.


    Igor Auler

    • Movido Roberson Ferreira _ sexta-feira, 21 de setembro de 2012 22:31 (De:SQL Server - Desenvolvimento Geral)
    sexta-feira, 21 de setembro de 2012 19:53

Respostas

  • Igor, no SQL Server você também tem toda essa possibilidade de permissionamento.

    Você, que provavelmente é o sysadmin do servidor, pode criar uma procedure "dizendo" pro SQL que esta procedure sempre será executada como se quem estivesse chamado ela fosse você (ou fosse um outro usuário com permissão de restore). Com isso, desde que o usuário tenha permissão de execução nesta procedure, consequentemente o conteúdo da procedure será executado, mesmo que ele não tenha diretamente as permissões necessárias.

    Acho que é isso que você quer.

    Segue exemplo:

    1) Crie a procedure (veja que "digo" pro SQL que esta procedure sempre será executada pelo Owner - que no caso sou eu mesmo).

    Create Procedure stp_RestauraBackup WITH EXECUTE AS OWNER as
    Begin
      RESTORE DATABASE [Homologacao] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012DEVELOPER\MSSQL\Backup\Teste.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 5
    End

    Dê permissão ao usuário de execução desta procedure

    Grant Execute On stp_RestauraBackup To Pedro

    Pronto! Agora o usuário Pedro, que não está na role dbcreator, consegue restaurar o backup.


    Roberson Ferreira - Database Developer
    Acesse: www.robersonferreira.com.br
    Email: contato@robersonferreira.com.br

    Se esta sugestão for útil, por favor, classifique-a como útil.
    Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    • Sugerido como Resposta Roberson Ferreira _ sexta-feira, 21 de setembro de 2012 22:30
    • Marcado como Resposta Igor Auler segunda-feira, 24 de setembro de 2012 15:31
    • Não Marcado como Resposta Igor Auler segunda-feira, 24 de setembro de 2012 15:47
    • Marcado como Resposta Igor Auler segunda-feira, 24 de setembro de 2012 18:47
    sexta-feira, 21 de setembro de 2012 22:30
  • Tive que setar a base como TRUSTWORTHY com o comando abaixo e então funcionou perfeitamente.. obrigado!

    ALTER DATABASE master SET TRUSTWORTHY ON;
    GO


    Igor Auler

    • Marcado como Resposta Igor Auler segunda-feira, 24 de setembro de 2012 18:47
    segunda-feira, 24 de setembro de 2012 18:47

Todas as Respostas

  • Igor Auler,

    Sem a fixed role DBCREATOR, impossível restaurar o banco de dados.


    []'s

    sexta-feira, 21 de setembro de 2012 20:09
  • Isso eu sei, a minha pergunta foi:

    • Tem como eu dar privilegio para a procedure de DBCREATOR e depois dar grant de execute para um usuário que não possui grant de DBCREATOR e ele conseguir executar esta procedure?

    No meu modo de ver, isto é uma falha de segurança gravíssima, objetos de banco de dados servem para criar segurança ao ambiente. Qualquer SGB trabalha da seguinte forma, o objeto possui um owner, que por sua vez possui privilégios, e os usuários que possuem grant de execução destes objetos não deveriam precisar de outros grants para execução dos comando encapsulados em um objeto.

    Ou seja, se eu possuo a view descrita abaixo e eu dou grant de SELECT para o usuário "A", ele não deveria precisar de grant na tabela "X" para dar select nesta view!

    CREATE VIEW dbo.EXEMPLO AS SELECT * FROM X

    GRANT SELECT ON dbo.EXEMPLO TO A


     


    Igor Auler

    sexta-feira, 21 de setembro de 2012 20:20
  • Quem deveria executar os comandos da procedure, deveria ser o owner dela e não o usuário que a está executando.

    Igor Auler

    sexta-feira, 21 de setembro de 2012 20:21
  • Igor, no SQL Server você também tem toda essa possibilidade de permissionamento.

    Você, que provavelmente é o sysadmin do servidor, pode criar uma procedure "dizendo" pro SQL que esta procedure sempre será executada como se quem estivesse chamado ela fosse você (ou fosse um outro usuário com permissão de restore). Com isso, desde que o usuário tenha permissão de execução nesta procedure, consequentemente o conteúdo da procedure será executado, mesmo que ele não tenha diretamente as permissões necessárias.

    Acho que é isso que você quer.

    Segue exemplo:

    1) Crie a procedure (veja que "digo" pro SQL que esta procedure sempre será executada pelo Owner - que no caso sou eu mesmo).

    Create Procedure stp_RestauraBackup WITH EXECUTE AS OWNER as
    Begin
      RESTORE DATABASE [Homologacao] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012DEVELOPER\MSSQL\Backup\Teste.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 5
    End

    Dê permissão ao usuário de execução desta procedure

    Grant Execute On stp_RestauraBackup To Pedro

    Pronto! Agora o usuário Pedro, que não está na role dbcreator, consegue restaurar o backup.


    Roberson Ferreira - Database Developer
    Acesse: www.robersonferreira.com.br
    Email: contato@robersonferreira.com.br

    Se esta sugestão for útil, por favor, classifique-a como útil.
    Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    • Sugerido como Resposta Roberson Ferreira _ sexta-feira, 21 de setembro de 2012 22:30
    • Marcado como Resposta Igor Auler segunda-feira, 24 de setembro de 2012 15:31
    • Não Marcado como Resposta Igor Auler segunda-feira, 24 de setembro de 2012 15:47
    • Marcado como Resposta Igor Auler segunda-feira, 24 de setembro de 2012 18:47
    sexta-feira, 21 de setembro de 2012 22:30
  • Roberson, 

    Se não me falha a memória, mesmo concedendo permissões para o usuário na stp pelo comando EXECUTE AS, ações com impacto muito forte no banco não fará que ele execute tais ações, que eu me lembre o EXECUTE AS tem limitações que aplica em apenas em instruções DML: select , update, insert e delete, Desculpe se estiver errado, pois  essa resposta foi apenas no conhecimento digamos que na (Teoria e não tão aprofundado como devia) e não na prática.


    []'s

    sábado, 22 de setembro de 2012 01:27
  • Fernando, Execute as realmente é utilizado para DML na maioria das vezes. Mas não se restringe somente a DML.

    Na questão do Restore eu criei um login chamado 'Pedro' e um user na master, também chamado 'Pedro'. A única permissão que ele tinha era a de execução na procedure que restaurava o Backup. E esta procedure foi criada com Execute as Owner.

    Não tive problemas em restaurar o backup com o login 'Pedro'.

    As algumas opções de banco podem ser usadas com Execute as: Restore Database, Create Database, Drop Database, etc.

    Faça este teste e veja se consegue reproduzir esta situação.


    Roberson Ferreira - Database Developer
    Acesse: www.robersonferreira.com.br
    Email: contato@robersonferreira.com.br

    Se esta sugestão for útil, por favor, classifique-a como útil.
    Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    sábado, 22 de setembro de 2012 16:18
  • Depois que alterei a procedure para "EXECUTE AS OWNER" a procedure começou a apresentar o seguinte erro:
    "Access to the remote server is denied because the current security context is not trusted."

    e  estou lendo o seguinte thread neste momento:

    http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/512991fd-af88-4a87-8b3e-39e0e346040e


    Igor Auler


    • Editado Igor Auler segunda-feira, 24 de setembro de 2012 18:13
    segunda-feira, 24 de setembro de 2012 18:12
  • Na verdade a proceudre não faz apenas o restore da base, ela faz os seguintes passos:

      • Vai em outro servidor (através do xp_cmdshell) pega o ultimo Backup feito da base e copia o arquivo (.bak) para o servidor que a procedure se encontra.
      • Executa um restore a partir deste arquivo de Backup.
      • Executa EXEC sp_change_users_login 'Update_One' ... para todos os usuarios da base.
      • Deleta o arquivo copiado para o servidor (através do xp_cmdshell).

    Segue o código da procedure que eu fiz para avaliações e futuros usos.

    Cuidado que, como todas as minhas bases só possuem um arquivo de dados e um de log eu não fiz um cursor para montar o restore, ou seja ele faz o comando de restore com apenas um arquivo de dados e um de log.

    USE [master]
    GO
    /****** Object:  StoredProcedure [dbo].[sp_AtualizaBase]    Script Date: 09/24/2012 15:12:15 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER PROCEDURE [dbo].[sp_AtualizaBase] 
    	@DBName		VARCHAR(50) -- Nome da base que será feito o restore
    --WITH EXECUTE AS OWNER 
    AS
    BEGIN
    
    	DECLARE @Diretorio	VARCHAR(500)
    	DECLARE @cmd		VARCHAR(1000)
    	DECLARE @Data		DATETIME
    	DECLARE @NomArquivo	VARCHAR (300)
    	DECLARE @SQL		VARCHAR(MAX)
    	
    	--Seta a data em uma variável
    	SET @Data = GETDATE()
    	
    	--Seta o nome do arquivo que será copiado no servidor
    	SET @NomArquivo = 'NOME_DO_DIRETORIO_ONDE_SERÁ_SALVO_O_NOVO_ARQUIVO' + 
    		@DBName + '_' + CONVERT(VARCHAR(50), @Data, 112) + 
    		'_' + CONVERT(VARCHAR(50), DATEPART(HH,@Data), 112) + 
    		'_' + CONVERT(VARCHAR(50), DATEPART(MI,@Data), 112) + 
    		'_' + CONVERT(VARCHAR(50), DATEPART(SS,@Data), 112) + 
    		'.bak'
    	
    	--Seleciona o ultimo backup da base feito pelo SQL Server Agent
    	SELECT TOP 1
    		@Diretorio = '\\NOME_SERVIDOR\BACKUP\' + @DBName + '\' + MYSQL.name + '.bak'
    	FROM OPENQUERY([NOME_LINKED_SERVER], 
    		'SELECT 
    			sdb.name as dbname,
    			bus.name,
    			SDB.dbid,
    			BUS.backup_finish_date
    		FROM 
    			sys.sysdatabases sdb
    			LEFT OUTER JOIN msdb.dbo.backupset bus 
    				ON bus.database_name = sdb.name
    		WHERE
    			BUS.is_copy_only = 0
    			AND bus.user_name = ''NT AUTHORITY\SYSTEM''') MYSQL -- NT AUTHORITY\SYSTEM SERVE PARA PEGAR APENAS O ULTIMO BACKUP FEITO PELO AGENTE E NÃO POR ALGUM USUÁRIO
    	WHERE
    		MYSQL.dbname = @DBName
    	ORDER BY 
    		MYSQL.backup_finish_date DESC
    	
    	--Cria o comando CMD a ser executado para copiar o arquivo
    	SELECT @cmd = 'COPY "' +  @Diretorio + '" "' + @NomArquivo + '"'
    
    ------------------------------------------------------------
    PRINT 'Arquivo ' + @Diretorio + ' sendo copiado'
    ------------------------------------------------------------
    
    	--Copia o arquivo para o servidor
    	EXEC master..xp_cmdshell @cmd
    	
    ------------------------------------------------------------
    PRINT 'Arquivo copiado para o diretório ' + @NomArquivo	
    ------------------------------------------------------------
    
    	DECLARE @LogName	VARCHAR(50)
    	DECLARE @DirLogName	VARCHAR(50)
    	DECLARE @DataName	VARCHAR(50)
    	DECLARE @DirDataName	VARCHAR(50)
    	
    	--Seta o nome logico e fisico do arquivo .mdf	
    	SELECT 
    		@DataName = name,
    		@DirDataName = physical_name
    	FROM 
    		sys.master_files
    	WHERE
    		database_id = DB_ID(@DBName)
    		AND TYPE = 0
    	
    	--Seta o nome logico e fisico do arquivo .ldf		
    	SELECT 
    		@LogName = name,
    		@DirLogName = physical_name
    	FROM 
    		sys.master_files
    	WHERE
    		database_id = DB_ID(@DBName)
    		AND TYPE = 1
    
    	--Faz Restore da base passada por parametro para a procedure
    	SET @SQL = 'ALTER DATABASE [' + @DBName + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;'
    	EXEC (@SQL)
    
    ------------------------------------------------------------
    PRINT 'Base setada como SINGLE_USER'	
    ------------------------------------------------------------
    
    ------------------------------------------------------------
    PRINT 'Iniciando processo de restauração da base ' + @DBName
    ------------------------------------------------------------
    	
    	SELECT @SQL =
    	'RESTORE DATABASE [' + @DBName + '] FROM  DISK = N''' + @NomArquivo + ''' WITH  FILE = 1,
    	MOVE N''' + @DataName + ''' TO N''' + @DirDataName + ''',
    	MOVE N''' + @LogName + ''' TO N''' + @DirLogName + ''',
    	NOUNLOAD,  REPLACE,  STATS = 10;'
    	EXEC (@SQL)
    
    ------------------------------------------------------------
    PRINT 'Finalizado o processo de restauração da base ' + @DBName
    ------------------------------------------------------------
    	
    	SET @SQL = 'ALTER DATABASE [' + @DBName + '] SET MULTI_USER WITH ROLLBACK IMMEDIATE;'
    	EXEC (@SQL)
    
    ------------------------------------------------------------
    PRINT 'Base setada como MULTI_USER'	
    ------------------------------------------------------------
    
    ------------------------------------------------------------
    PRINT 'Reponterando os usuários da base ' + @DBName
    ------------------------------------------------------------
    
    	--Atualiza os usuarios da base	
    	SELECT
    		@SQL = 'USE ' + @DBName + ';'
    	
    	SELECT
    		@SQL = @SQL + 
    	'SELECT
    		U.name
    	INTO ##Users
    	FROM
    		SYS.sysusers U
    	WHERE
    		U.status = 0
    		AND U.hasdbaccess = 1
    		AND U.name <> ''dbo''
    		AND U.name NOT LIKE ''#%'''
    	EXEC (@SQL)
    	
    	SELECT
    		@SQL = 'USE ' + @DBName + ';'
    		
    	SELECT
    		@SQL = @SQL + 'EXEC sp_change_users_login ''Update_One'', ''' + U.name + ''', ''' + U.name + ''';'
    	FROM
    		 ##Users U
    	EXEC (@SQL)
    	
    	DROP TABLE  ##Users
    
    ------------------------------------------------------------
    PRINT 'Deletando o arquivo ' + @NomArquivo
    ------------------------------------------------------------
    	
    	SELECT @cmd = 'DEL "' + @NomArquivo + '"'
    	EXEC master..xp_cmdshell @cmd
    
    ------------------------------------------------------------
    PRINT 'Base atualizada'
    ------------------------------------------------------------
    	
    END

    É preciso alterar no código as variáveis NOME_DO_DIRETORIO_ONDE_SERÁ_SALVO_O_NOVO_ARQUIVO e NOME_SERVIDOR.

    Abs,


    Igor Auler


    • Editado Igor Auler segunda-feira, 24 de setembro de 2012 18:34
    segunda-feira, 24 de setembro de 2012 18:32
  • Tive que setar a base como TRUSTWORTHY com o comando abaixo e então funcionou perfeitamente.. obrigado!

    ALTER DATABASE master SET TRUSTWORTHY ON;
    GO


    Igor Auler

    • Marcado como Resposta Igor Auler segunda-feira, 24 de setembro de 2012 18:47
    segunda-feira, 24 de setembro de 2012 18:47
  • Existe alguma contraindicação para não setar a base como TRUSTWORTHY?

    Igor Auler

    segunda-feira, 24 de setembro de 2012 18:52
  • Existe. Mas são poucas, apesar de importantes.

    Leia em: http://msdn.microsoft.com/pt-br/library/ms187861.aspx


    Roberson Ferreira - Database Developer
    Acesse: www.robersonferreira.com.br
    Email: contato@robersonferreira.com.br

    Se esta sugestão for útil, por favor, classifique-a como útil.
    Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    segunda-feira, 24 de setembro de 2012 19:42