none
Dúvida sobre a permissão EXECUTE RRS feed

  • Pergunta

  • Olá pessoal!
        Tenho uma situação e gostaria da opinião de vocês à respeito.
        Em um banco tem dois usuários, o primeiro, para uso da aplicação, está com permissão para executar procedures e não tem permissões em tabelas, através de uma role.
        E o segundo usuário, no mesmo banco, está com permissões read e write nas tabelas. Esse usuário está inativo e sem permissão de conexão.
        Em procedures com sql dinâmico o segundo usuário é contextualizado (WITH EXECUTE AS [user]) dentro da procedure para executar comandos read e write. Em algumas dessas procedures, tem execução aninhada de outras procedures.
       A questao é : Porque que um mesmo usuário sem permissão de execute não pode executar uma procedure diretamente, mas contextualizado em outra procedure, ele pode executar?
       Agradeço as contribuições.

    Alex Elias

    segunda-feira, 15 de dezembro de 2014 19:21

Respostas

  • Solucionado.

    Basicamente, solucionei com dois logins, um com permissões mínimas, inclusive em objetos do sys e information_schema, e outro inativo e sem permissão de conexão para executar querys dinâmicas dentro das procedures através do EXECUTE AS LOGIN somente na execução da string.

    É claro que mais detalhes à respeito de permissões e restrições foram aplicados.


    Alex Elias

    • Marcado como Resposta Alex Elias quinta-feira, 26 de fevereiro de 2015 18:12
    quinta-feira, 26 de fevereiro de 2015 18:12

Todas as Respostas

  • Melhorando a questão...

        Estou executando a mesma procedure, com o mesmo usuário, que é o usuário contextualizado dentro da procedure.
    Assim :
        São dois usuários : usu_app, com permissão EXECUTE e usu_app_withexecute com permissões READ e WRITE;
        Uma execução : Com o usuário usp_app executo a procedure usp_filtro, que tem WITH EXECUTE AS 'usu_app_withexecute' no início e no meio do código tem a execução de outra procedure, usp_insere. Essa execução dá certo;
        Outra execução : Ativo e logo no Studio com o usuario usu_app_withexecute. Executo a procedure usp_insere. Essa execução dá erro de permissão.
         Acredito que tenha haver com validações na cadeia de permissões ao iniciar cada execução.


    Alex Elias

    terça-feira, 16 de dezembro de 2014 18:41
  • Alex,

    No momento que você atribui ao user executar a procedure dentro do mesmo contexto, o procedimento correto é que o script será executado apenas sob às permissões concedidas ao "user" definido no "WITH EXECUTE".

    Como você possui SQL Dinâmico em algumas procedures, este processo não se aplica. Independentemente do contexto, veja a seção "Comentários" no BOL abaixo:

    http://msdn.microsoft.com/pt-br/library/ms188354.aspx

    Se ajudou na sua solução, não esqueça de marcar como resposta !

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA | MCSA - SQL Server 2012 | MCSE - Data Platform
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"

    quarta-feira, 17 de dezembro de 2014 11:15
  • Alex,

    Neste caso, se você deseja fazer uso de SQL Dinâmicos, talvez seja necessário dentro da sua Query dinâmica, fazer uso do comando Execute As mudando o contexto da sessão, mas isso é algo que poderá impactar totalmente no seu trabalho.

    Veja abaixo um exemplo de como utilizar o comando Execute As:

    USE AdventureWorks;
    GO
    -- Criando dois Logins de Acesso --
    CREATE LOGIN login1 WITH PASSWORD = 'J345#$)thb';
    CREATE LOGIN login2 WITH PASSWORD = 'Uor80$23b';
    GO
    
    -- Criando duas contas de usuário vinculadas aos Logins --
    CREATE USER user1 FOR LOGIN login1;
    CREATE USER user2 FOR LOGIN login2;
    GO
    
    --Give IMPERSONATE permissions on user2 to user1
    --so that user1 can successfully set the execution context to user2.
    GRANT IMPERSONATE ON USER:: user2 TO user1;
    GO
    
    -- Exibindo o Contexto atual de conexão --
    SELECT SUSER_NAME(), USER_NAME();
    
    -- Mudando o Contexto de conexão para o Login1 --
    EXECUTE AS LOGIN = 'login1';
    
    -- Verificando o contexto de conexão --
    SELECT SUSER_NAME(), USER_NAME();
    
    -- Mudando o Contexto de conexão para o Login2 --
    EXECUTE AS USER = 'user2';
    
    -- Exibindo o Contexto atual de conexão --
    SELECT SUSER_NAME(), USER_NAME();
    
    -- Revertendo o Contexto de Conexão --
    REVERT;
    
    -- Exibindo o Contexto atual de conexão --
    SELECT SUSER_NAME(), USER_NAME();
    
    -- Revertendo o Contexto de Conexão --
    REVERT;
    
    -- Exibindo o Contexto atual de conexão --
    SELECT SUSER_NAME(), USER_NAME();
    
    -- Excluíndo os Logins --
    DROP LOGIN login1;
    DROP LOGIN login2;
    
    -- Excluíndos os Usuários --
    DROP USER user1;
    DROP USER user2;
    GO


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | SoroCódigos] @JuniorGalvaoMVP | pedrogalvaojunior.wordpress.com

    quinta-feira, 18 de dezembro de 2014 12:12
    Moderador
  • Durval e Pedro, agradeço a contribuição.

    Escrevi um script exemplificando o cenário da questão. O termo "[nnn]" é o nome de um DB qualquer aonde possa executar o script.

    O ponto da dúvida é que um mesmo usuário pode executar procedure quando contextualizado em outra procedure. Mas quando na tela do Studio, por exemplo, ele não pode executar a mesma procedure.

    --Permissões
    	--usuario1 : GRANT EXECUTE;
    	--usuario2 : ADD ROLE db_datareader; ADD ROLE db_datawriter;
    
    -- Usuários
    -- usuario1
    	USE [master];
    	GO
    	CREATE LOGIN [usuario1] WITH PASSWORD=N'123456', DEFAULT_DATABASE=[nnn], CHECK_POLICY = OFF;
    	GO
    	USE [nnn];
    	GO
    	CREATE USER [usuario1] FOR LOGIN [usuario1];
    	GRANT EXECUTE TO [usuario1];
    	GO
    
    -- usuario2
    	USE [master];
    	GO
    	CREATE LOGIN [usuario2] WITH PASSWORD=N'123456', DEFAULT_DATABASE=[nnn], CHECK_POLICY = OFF;
    	GO
    	USE [nnn];
    	GO
    	CREATE USER [usuario2] FOR LOGIN [usuario2];
    	EXEC sp_addrolemember @membername = 'usuario2', @rolename = 'db_datareader';
    	EXEC sp_addrolemember @membername = 'usuario2', @rolename = 'db_datawriter';
    	GO
    
    
    
    --Objetos
    USE [nnn];
    GO
    CREATE TABLE dbo.Cadastro(
    	Id INT IDENTITY(1,1) NOT NULL,
    	Nome VARCHAR(50) NOT NULL,
    	CONSTRAINT PK_Cadastro PRIMARY KEY CLUSTERED(Id)
    );
    GO
    
    GO
    /* EXEC dbo.usp_Cadastro_Sel; */
    CREATE PROCEDURE dbo.usp_Cadastro_Sel
    AS
    BEGIN
    	SET NOCOUNT ON;
    	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    	
    	SELECT [Id] ,[Nome] FROM dbo.Cadastro;
    END;
    GO
    
    GO
    /* EXEC dbo.usp_Cadastro_Ins @str_Nome = NULL; */
    CREATE PROCEDURE dbo.usp_Cadastro_Ins(@str_Nome VARCHAR(50))
    WITH EXECUTE AS 'usuario2'
    AS
    BEGIN
    	SET NOCOUNT ON;
    	
    	-- com sql dinâmico
    	DECLARE @cmd NVARCHAR(80), @prm NVARCHAR(25);
    	SET @cmd = N'INSERT INTO dbo.Cadastro([Nome]) VALUES(@str_Nome);';
    	SET @prm = '@str_Nome VARCHAR(50)';
    	EXEC sp_executesql @cmd, @prm, @str_Nome;
    	
    	-- sem sql dinâmico
    	--INSERT INTO dbo.Cadastro([Nome]) VALUES(@str_Nome);
    
    	EXEC dbo.usp_Cadastro_Sel;
    
    END;
    GO
    
    
    --Execuções
    -->> Logar com usuario1 e executar. Sem erro de permissão.
    	EXEC dbo.usp_Cadastro_Sel;
    	EXEC dbo.usp_Cadastro_Ins @str_Nome = 'João';
    
    -->> Logar com usuario2 e executar. Com erro de permissão.
    	EXEC dbo.usp_Cadastro_Sel;
    	EXEC dbo.usp_Cadastro_Ins @str_Nome = 'Maria';
    
    
    --Erros apresentados para a execução com o usuario2
    	--Msg 229, Level 14, State 5, Procedure usp_Cadastro_Sel, Line 1
    	--The EXECUTE permission was denied on the object 'usp_Cadastro_Sel', database 'nnn', schema 'dbo'.
    
    	--Msg 229, Level 14, State 5, Procedure usp_Cadastro_Ins, Line 1
    	--The EXECUTE permission was denied on the object 'usp_Cadastro_Ins', database 'nnn', schema 'dbo'.
    
    /*
    --DESFAZER TUDO
    USE [nnn];
    GO
    DROP PROCEDURE dbo.usp_Cadastro_Sel, dbo.usp_Cadastro_Ins;
    DROP TABLE dbo.Cadastro;
    DROP USER [usuario1];
    DROP USER [usuario2];
    USE master;
    GO
    DROP LOGIN [usuario1];
    DROP LOGIN [usuario2];
    GO
    */



    Alex Elias

    sexta-feira, 19 de dezembro de 2014 18:50
  • Alex,

    Se eu entendi bem, você esta tentando dentro do sua Stored Procedure mudar o contexto de conexão do usuário dentro da Stored Procedure para um outro contexto e fazer a execução.

    É isso? Se for, realmente este tipo de implementação não será aplicada de forma correta e na verdade acaba sendo inconsistente para o SQL Server.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | SoroCódigos] @JuniorGalvaoMVP | pedrogalvaojunior.wordpress.com

    terça-feira, 23 de dezembro de 2014 12:13
    Moderador
  •     Bom dia, Pedro. É isso mesmo!

        Como o usuario1 não não tem permissão SELECT, a sp_executesql retorna um erro ao executar a consulta. Então eu alterei o contexto para usuario2, que tem permissão SELECT, assim foi possível executar a consulta.

        O que eu não entendi é a execução usp_Cadastro_Sel ser possível no contexto do usuario2 dentro da procedure, já que o usuario2 não tem permissão EXECUTE.

        Você disse que essa implementação é inconsistente. Você pode me explicar essa inconsistência e os motivos?

        E, qual a forma para eu implementar esse cenário de forma consistente?


    Alex Elias

    terça-feira, 23 de dezembro de 2014 12:50
  • Alex,

    Então, a incosistência ocorre justamente por você esta conectado com um determinado usuário que possui um nível de permissão maior que o outro durante a execução da procedure, ao fazer o Execute o SQL Server esta mantendo esta mantendo este nível de permissão dentro do contexto de execução da Sessão.

    Você destacou que não entendeu como a execução do procedure foi realizado no contexto do usuário 2 sendo que o mesmo não tem permissão de execute, então isso é um cenário que o Execute As Login vai ter um impacto diferente do Execute As User, não se esqueça que um User só existe a partir do vínculo de um Login!!!

    Ao meu ver a forma mais indicado e consistente se resumo na implementação de uma conta de usuário específica para fazer execução e criação de recursos e objetos e uma conta de usuário que somente possa fazer acesso aos dados.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | SoroCódigos] @JuniorGalvaoMVP | pedrogalvaojunior.wordpress.com

    • Sugerido como Resposta Durval Ramos terça-feira, 30 de dezembro de 2014 22:41
    terça-feira, 23 de dezembro de 2014 13:35
    Moderador
  • Boa tarde!

    Então seria algo como um usuário com permissões "db_ddladmin" e "EXECUTE" e outro com permissões "SELECT", "INSERT", "UPDATE", "DELETE"?

    Considerando isso:
    Qual seria o usuário da aplicação? E o outro usuário, certamente seria contextualizado na procedure.

    Vou explicar o cenário geral.

    Estou implementando uma política de segurança aonde o usuário da aplicação deve ter somente permissão "EXECUTE", não pode ter acesso aos dados e não pode ter acesso aos objetos de sistema do SQL.
    As procedures que usuário da aplicação executa tem alguns detalhes :
    - Usam sql dinâmico (sp_executesql @cmd, @prm, ..., exec '...' e execute '...');
    - Acessam table functions em outro database;
    - Acessam e manipulam dados de tabelas em outro database;
    - Executam procedures aninhadas no mesmo database;
    - Executam procedures aninhadas em outro database:
    - Executam procedures em outra instância através de linked server. Essas procedures em outra instância tem os mesmos detalhes;
    - Utilizam certificado e chave de criptografia.

    Obs: Claro que, esses detalhes não estão todos em todas as procedures. Tem procedures que não tem nenhum, outras que tem somente um deles e outras que tem mais de um combinado e/ou não combinado.


    Alguma sugestão?


    Alex Elias

    terça-feira, 6 de janeiro de 2015 18:46
  • Solucionado.

    Basicamente, solucionei com dois logins, um com permissões mínimas, inclusive em objetos do sys e information_schema, e outro inativo e sem permissão de conexão para executar querys dinâmicas dentro das procedures através do EXECUTE AS LOGIN somente na execução da string.

    É claro que mais detalhes à respeito de permissões e restrições foram aplicados.


    Alex Elias

    • Marcado como Resposta Alex Elias quinta-feira, 26 de fevereiro de 2015 18:12
    quinta-feira, 26 de fevereiro de 2015 18:12