WITH ENCRYPTION e EXECUTE AS (Stored procedure)

Question

Salve galera,

Me pediram para criptografar as sps que estão no banco de dados e colocar o "execute as" para um determinado usuário. Porém, não estou conseguindo utilizar as duas sintaxes juntas e também não se se funciona no MSDE, SQL2000 e no SQL2005.

Alguém tem alguma idéia de como fazer isso ou uma outra forma interessante de bloquear que o usuário veja o conteúdo das sps e até mesmo a execute diretamente pelo banco de dados?

Valeu !!!

Answer 1

Boa Tarde Pablicio,

 

É possível usar o EXECUTE AS com Encryption sem problemas. Só que isso só é válido para o 2005. O SQL Server 2000 não possui EXECUTE AS (embora você possa usar o SETUSER em algumas situações).

 

Code Snippet

CREATE PROCEDURE usp

WITH ENCRYPTION, EXECUTE AS 'dbo'

AS

SELECT 'Texto'

 

 

No SQL Server 2005, para que o usuário veja o conteúdo da SP, ele precisa ter a permissão de View Definition. Caso ele não a possua, não poderá visualizar o código. Para garantir uma segurança ainda maior, você pode usar o Encryption ou Code Siging (esse último é mais complicado).

 

O Execute As não vai impedir a execução diretamente pelo banco de dados. Essa construção apenas vai mudar o contexto de segurança da SP para executar como se fosse outro usuário. Para impedir execuções diretas você pode trabalhar com Application Roles ou certificados (apenas no 2005).

 

[ ]s,

 

Gustavo

 

Answer 2

Gustavo, obrigado pela resposta.

O que entendi é que o EXECUTE AS não funciona em MSDE e 2000. Neste caso não serve para mim, pq tem clientes que tem MSDE e 2000.

Por outro lado, você está dizendo para utilizar Roles ou Certificados, mas também vou cair no mesmo problema (MSDE e 2000).

Então estou achando melhor fazer a criptografia da SP e garantir sua execução através de GRANT´s. É isso?

Hoje, no código das nossas sps, temos a linha:

GRANT EXECUTE ON <procedure> TO PUBLIC

Neste caso como faço para que apenas um usuário ou uma application role execute a sp?

Outra dúvida, a criptografia da sp é segura? Eu consigo barra o acesso do usuário SA as execução, alteração e etc das sps?

A idéia é não deixar ou dificultar com que o cliente tenha acesso as sps que estão no banco.

Valeu !!!

Answer 3

Olá Pablício,

 

Se existem clientes com o SQL Server 2000 e MSDE não poderemos de fato utilizar as funcionalidades do 2005. Recomendaria sugerir aos seus clientes um upgrade já que o 2000 está com os dias contados e no caso do MSDE, o SQL Server Express (substituto) continua gratuito.

 

Estamos falando de coisas diferentes quando falamos do Encryption e do Execute As. O Encryption apenas criptografa o código da SP, ou seja, ninguém (nem mesmo o SA) poderá ver a estrutura da SP. No entanto, permissão de visualização da estrutura e permissão de execução são coisas diferentes. Uma procedure pode estar criptografada e ainda assim habilitada para utilização. Uma procedure pode não estar criptografada e bloqueada para execução. A idéia do Encryption é preservar a inteligência de seu código, de modo que outras pessoas não o copiem e façam mau uso dele.

 

Certa vez vi em uma apresentação de um conceituado consultor em SQL Server a seguinte afirmação: "Se você deseja armazenar algum código de forma inviolável, não o coloque no SQL Server". Não gostaria que fosse verdade, mas infelizmente a criptografia utilizada para esconder o código das SPs, Views, Functions, etc deixa a desejar no SQL Server 2000. Não que o seu usuário vá quebrá-la, mas não é difícil achar alguma programa para fazê-lo. Basta procurar um pouquinho no Google.

 

Voltando ao outro assunto quando falamos de execução, podemos dar permissão a um usuário para executar uma procedure através do comando de GRANT que você descreveu. O problema é que se alguém conseguir um login e uma senha (ou se o login for via Windows) e estiver de posse de um Query Analyser, será possível executar a SP sem passar por sua aplicação. Para evitar que isso aconteça utilizamos as Application Roles.

 

Nesse caso, o usuário conecta-se ao banco de dados, mas não tem permissão de fazer nada. Após conectar-se, a Application Role é ativada através do comando sp_setapprole. A ativação da role dependerá de uma outra senha. Após a role ser ativada, valem as permissões da role e não mais do usuário. Isso significa que se a permissão de execução das procedures estiverem apenas para a Application Role, o usuário pode até conectar-se, mas não conseguirá executar nada. Na sua aplicação você coloca a ativação da Application Role e dessa forma, apenas passando por sua aplicação as procedures podem ser executadas.

 

Não sei se fui claro, mas qualquer coisa retorne. Evite também dar privilégios para a role public

 

[ ]s,

 

Gustavo

Answer 4

Gustavo,

Acredito, que diante desta bela explanação, a melhor saída seja a Application Role. Vou pesquisar um pouco mais sobre ela e ver o que consigo.

Quanto ao MSDE vou ver se consigo para que troquem pelo SQLExpress. Em termos de recursos de máquinas muda um pouco, por esse motivo acho que terei um pouco de dificuldade, mas se a application role funcionar já ajuda bastante.

Se tiver algo sobre Application Role e puder mandar eu agradeço.

Muito obrigado, mais uma vez.

Abraço

Pablicio

Answer 5

Olá Pablício,

 

Fiz um pequeno script para mostar como ela funciona. Execute-o passo a passo:

 

Code Snippet

-- Criação do banco

CREATE DATABASE BD

GO

 

-- Criação do Login

EXEC sp_addlogin 'usrApp','SenhaApp'

 

-- Criação do Usuário

USE BD

GO

sp_grantdbaccess 'usrApp','usrApp'

GO

 

-- Criação da Application Role

sp_addapprole 'AppRole' , 'SenhaAppRole'

 

-- Criação da tabela

CREATE TABLE tbl (CODIGO INT)

INSERT INTO tbl VALUES (1)

INSERT INTO tbl VALUES (2)

INSERT INTO tbl VALUES (3)

INSERT INTO tbl VALUES (4)

 

-- Dá as permissões para a Role AppRole

GRANT SELECT ON tbl TO AppRole

 

-- Mude o contexto do usuário

SETUSER 'usrApp'

 

-- Tentativa de leitura na tabela (Acesso negado)

SELECT CODIGO FROM tbl

 

-- Ativação da Role

sp_setapprole 'AppRole', 'SenhaAppRole'

 

-- Tentativa de leitura (Acesso concedido)

SELECT * FROM tbl

 

 

Como você pode ver, apenas se a role for ativada, o acesso a tabela é concedido. Após um usuário utilizar a Aplication Role, ela fica ativa até o final da conexão. Não é possível voltar ao contexto anterior. O interessante é que mesmo que o acesso ao banco seja via Windows, apenas a aplicação irá ativar a Aplication Role e como o usuário comum não irá conhecer a senha, ele não conseguirá executar nada senão for pela aplicação.

 

[ ]s,

 

Gustavo

Answer 6

Gustavo, obrigado pela dica. Vou promover essa alteração aqui.
Valeu !!!!