SQL Server – Como utilizar o EXECUTE AS para executar comandos como outro usuário (Impersonate login e user)

Existem algumas formas de realizar o comando EXECUTE AS, onde você pode escolher quem são os usuários ou logins que serão personificados pela instrução. Vou descrever abaixo esses tipos nos 2 cenários em que o EXECUTE AS é utilizado.

EXECUTE AS em consultas/instruções Ad-Hoc

• LOGIN: Permite personificar um login do SQL Server, obtendo todas as permissões a nível de instância, herdando as permissões como CONTROL SERVER e server roles como a sysadmin e securityadmin, por exemplo.
• USER: Permite personificar um usuário do SQL Server, obtendo todas as permissões a nível de database, herdando as permissões como SELECT em uma tabela e database roles, como db_datareader, por exemplo.

Lembrando que, mesmo que você esteja impersonando um usuário sysadmin através de um banco que você seja db_owner, por exemplo, utilizando o EXECUTE AS USER, você não irá ter os “poderes” de sysadmin desse usuário, pois o EXECUTE AS USER apenas herda as permissões a nível de database. Como você já é db_owner, você não vai herdar nenhuma permissão nova que ainda não tenha, apenas pode executar comandos como outro usuário (o que não deixa de ser um risco). Já se você conseguir utilizar o EXECUTE AS LOGIN de um usuário sysadmin, aí você terá sim, todos os “poderes” a nível de instância que ele possuir (ou seja, pode fazer TUDO).

EXECUTE AS em Objetos de Banco de Dados

• CALLER: Executar como CALLER é o padrão; se nenhuma outra opção for especificada, o procedimento será executado no contexto de segurança do chamador.
• OWNER: Executar como OWNER executa o procedimento no contexto do proprietário do objeto. Se o objeto for criado em um esquema de propriedade do dbo ou pelo proprietário do banco de dados, o procedimento será executado com permissões ilimitadas.
• SELF: Executar como SELF executa no contexto de segurança do criador do objeto. Isso é equivalente a executar como usuário especificado, onde o usuário especificado é a pessoa que cria ou altera o objeto.
• LOGIN/USER: Conforme já descrito acima, você também pode utilizar um usuário ou login específico no cabeçalho do objeto, permitindo que todos que tenham permissão de EXECUTE nesse objeto, como por exemplo, uma Stored Procedure, executem esse objeto com o contexto desse usuário/login especificado automaticamente.

Mais a frente tem um tópico específico de EXECUTE AS em Objetos de Banco de Dados, onde irei dar mais detalhes sobre essa utilização.

Após executar a instrução EXECUTE AS para executar comandos como outro usuário, geralmente é desejado voltar o contexto de segurança da sessão para o usuário original. Uma das formas de de fazer isso, é criando uma nova sessão ao se reconectar no banco.

O comando REVERT

A outra forma de se fazer isso, é utilizando o comando REVERT, que irá retornar o contexto de segurança para o usuário anterior:

Result:

Vale lembrar que o comando EXECUTE AS pode ser aninhado, ou seja, pode ser criada uma árvore de contextos de segurança, conforme mostro abaixo:

Result:

E agora, vamos desfazer a pilha de mostrar o aninhamento desse contexto:

Result:

EXECUTE AS … WITH COOKIES

Esse parâmetro especifica que o contexto de execução só pode ser revertido para o contexto anterior se a instrução de chamada REVERT WITH COOKIE contém o valor @varbinary_variable correto. Isso é uma medida de segurança, utilizada especialmente em pool de conexões, para garantir que apenas quem conhece o hash do cookie possa reverter o contexto de segurança.

Exemplo (Removi os comandos GO para evitar a perda de informações das variáveis):

Result:

Agora vou tentar aplicar o comando REVERT sem especificar o token:

Agora vou tentar trocar o token e tentar aplicar o comando REVERT:

EXECUTE AS … WITH NO REVERT

Após apresentar os comandos REVERT e REVERT WITH COOKIES, existe uma outra cláusula que faz com que o impersonate não possa ser revertido para o usuário original nessa sessão. Essa instrução é o WITH NO REVERT. Nesse caso, a única forma de reverter é abrindo uma nova sessão.

Quando a cláusula WITH NO REVERT COOKIE = @varbinary_variable for especificada, o Mecanismo de Banco de Dados do SQL Server passa o valor do cookie para @varbinary_variable. O contexto de execução definido por essa instrução poderá ser revertido somente para o contexto anterior se a instrução de chamada REVERT WITH COOKIE = @varbinary_variable tiver o mesmo valor @varbinary_variable.

Essa opção é útil em um ambiente no qual um pool de conexão é usado. O pool de conexão é a manutenção de um grupo de conexões de banco de dados para reutilização por aplicativos em um servidor de aplicativos. Como o valor passado para @varbinary_variable é conhecido apenas pelo chamador da instrução EXECUTE AS (no caso, o aplicativo), o chamador pode garantir que o contexto de execução estabelecido não possa ser alterado por mais ninguém.

Exemplo:

Result:

Para que seja possível utilizar o comando EXECUTE AS em qualquer login antes do SQL Server 2014, era necessário ter a permissão CONTROL SERVER ou fazer parte das server roles sysadmin ou securityadmin. A partir do SQL Server 2014 isso acabou ficando mais objetivo com a permissão IMPERSONATE ANY LOGIN, que é exclusiva para esse tipo de atividade e não exige que a pessoa esteja na role CONTROL SERVER ou seja um usuário sysadmin.

Entretanto, você que é DBA, deve ter em mente que essa permissão é EXTREMAMENTE perigosa para um usuário comum, uma vez que ele consegue utilizar o EXECUTE AS para executar comandos utilizando o login de um usuário sysadmin, por exemplo, e assim, fazer qualquer coisa na instância.

Este foi um dos motivos da permissão IMPERSONATE ANY LOGIN ter sido criada no SQL Server 2014. Os usuários com permissão CONTROL SERVER já possuíam esse privilégio implicitamente e com isso, eles poderiam utilizar o EXECUTE AS para executar comandos como outro usuário, inclusive, usuários sysadmin. A partir do SQL Server 2014, o DBA já pode aplicar um comando de DENY IMPERSONATE ANY LOGIN para os usuários com permissão de CONTROL SERVER e evitar que isso aconteça.

Vale ressaltar que existe também o comando GRANT IMPERSONATE ON LOGIN/USER::[usuario1] TO [usuario2], que permite liberar acesso de IMPERSONATE a usuários específicos. Além disso, membros da database role db_owner podem utilizar o EXECUTE AS em usuários criados nestes bancos. Lembrando que, mesmo que você esteja impersonando um usuário sysadmin através de um banco que você seja db_owner, por exemplo, utilizando o EXECUTE AS USER, você não irá ter os “poderes” de sysadmin desse usuário, pois o EXECUTE AS User apenas herda as permissões a nível de database. Como você já é db_owner, você não vai herdar nenhuma permissão nova que ainda não tenha, apenas pode executar comandos como outro usuário (o que não deixa de ser um risco).

Permissões Explícitas para utilizar o IMPERSONATE

Como já comentei acima, existem algumas condições para utilizar comando EXECUTE AS no SQL Server.

Condição #1: IMPERSONATE ANY LOGIN

Os usuários que possuem a permissão IMPERSONATE ANY LOGIN explícita podem executar comandos como qualquer login na instância SQL Server. Lembre-se que as permissões são a nível de instância, ou seja, aplicáveis a todos os databases dessa instância e comandos que exigem privilégios como sysadmin e CONTROL SERVER podem ser utilizados através do EXECUTE AS LOGIN.

Para liberar essa permissão, você deve utilizar o seguinte comando:

Condição #2: IMPERSONATE LOGIN

Os usuários que possuem a permissão IMPERSONATE LOGIN podem executar comandos como logins específicos na instância SQL Server. Esse comando dá poderes a um loginA executar comandos como se fosse o loginB e deve ser liberado para cada login desejado. Lembre-se que as permissões são a nível de instância, ou seja, aplicáveis a todos os databases dessa instância e comandos que exigem privilégios como sysadmin e CONTROL SERVER podem ser utilizados através do EXECUTE AS LOGIN.

Para liberar essa permissão, você deve utilizar o seguinte comando:

Condição #3: IMPERSONATE USER

Os usuários que possuem a permissão IMPERSONATE USER podem executar comandos como usuários específicos em um database. Esse comando dá poderes a um usuarioA executar comandos como se fosse o usuarioB no database que ele possui a permissão de IMPERSONATE e deve ser liberado para cada login desejado. Comandos que exigem permissões a nível de instância (Ex: Shutdown, alterar membros de roles do servidor, criar Linked Server, etc), NÃO são executados por IMPERSONATE USER, apenas comandos que exigem permissões a nível de database, como INSERT, UPDATE, DELETE, etc.

Para liberar essa permissão, você deve utilizar o seguinte comando:

Permissões Implícitas para utilizar o IMPERSONATE

Agora vou demonstrar que existem algumas condições implícitas onde você pode utilizar o comando EXECUTE AS no SQL Server sem ter uma permissão explícita para isso.

Condição #1: SYSADMIN

Role de “Administrador” do SQL Server. Usuários que estão nessa role podem fazer qualquer coisa no banco de dados e por isso, já tem a permissão de IMPERSONATE LOGIN, IMPERSONATE USER e IMPERSONATE ANY LOGIN e não podem ter privilégios negados com o DENY.

Para adicionar alguém nessa role, utilize o seguinte comando:

Condição #2: CONTROL SERVER

Permissão de “Administrador” do SQL Server. Usuários que possuem essa permissão podem fazer quase qualquer coisa no banco de dados e por isso, já tem a permissão de IMPERSONATE LOGIN, IMPERSONATE USER e IMPERSONATE ANY LOGIN, mas, diferente da role sysadmin, esses usuários PODEM ter privilégios de IMPERSONATE negados com o DENY.

Para adicionar alguém nessa role, utilize o seguinte comando:

Condição #3: SECURITYADMIN

Usuários que estão na server role securityadmin controlem a segurança e permissões da instância SQL Server. Estando nessa role, esses usuários possuem a permissão IMPERSONATE ANY LOGIN e por isso, eles podem fazer qualquer coisa no banco de dados, já que podem utilizar o comando EXECUTE AS LOGIN e executar comandos como se fosse um usuário sysadmin e por este motivo, a própria documentação da Microsoft trata essa role como equivalente à role sysadmin no quesito preocupações com a segurança. Diferente da role sysadmin, esses usuários PODEM ter privilégios de IMPERSONATE negados com o DENY.

Para adicionar alguém nessa role, utilize o seguinte comando:

Condição #4: db_owner

Usuários que estão na database role db_owner podem executar qualquer comando de DDL, DCL ou DML no database que eles tenham essa role. Por este motivo, esses usuários já possuem a permissão de IMPERSONATE USER implícita em todos os usuários do database. Vale lembrar que esses usuários PODEM ter privilégios de IMPERSONATE negados com o DENY.

Para adicionar alguém nessa role, utilize o seguinte comando:

Condição #5: db_securityadmin

Usuários que estão na database role db_securityadmin podem gerenciar a segurança e permissões no database. Por este motivo, esses usuários possuem a permissão de IMPERSONATE USER implícita em todos os usuários do database, podendo executar comandos como se fossem inclusive, um dos usuários que estão na role db_owner. Vale lembrar que esses usuários PODEM ter privilégios de IMPERSONATE negados com o DENY.

Para adicionar alguém nessa role, utilize o seguinte comando:

Como eu já comentei, para que seja possível utilizar o comando EXECUTE AS em qualquer login, é necessário ter a permissão CONTROL SERVER, fazer parte das server roles sysadmin ou securityadmin ou ter o privilégio explícito IMPERSONATE ANY LOGIN. Caso o usuário tenha a permissão IMPERSONATE, ele pode utilizar o EXECUTE AS apenas nos usuários específicos que ele tem essa permissão.

Falando a nível de database, onde o usuário pode executar o IMPERSONATE em usuários do banco, ele precisará estar na database role db_owner ou possui a permissão IMPERSONATE ON USER para os usuários específicos que ele pode personificar.

Sendo assim, vou demonstrar logo abaixo, como identificar quem são os usuários que possuem essas permissões na usa instância SQL Server.

Usuários com permissões CONTROL SERVER ou roles sysadmin e securityadmin

Para identificar usuários com permissões CONTROL SERVER ou roles sysadmin e securityadmin, que podem executar o EXECUTE AS para qualquer login, execute o script abaixo:

Result:

Usuários com IMPERSONATE ANY LOGIN

Para identificar quem são os usuários que possuem a permissão IMPERSONATE ANY LOGIN explícita, e assim, podendo executar comandos como qualquer usuário, execute o script abaixo:

Result:

Usuários com IMPERSONATE LOGIN

Para identificar quem são os usuários que possuem a permissão IMPERSONATE LOGIN explícita, podendo executar comandos como alguns usuários específicos, execute o script abaixo:

Result:

Usuários nas database roles db_owner ou db_securityadmin

Para identificar quem são os usuários que estão nas databases roles db_owner ou db_securityadmin, podendo executar comandos como qualquer usuário do database, execute o script abaixo:

Result:

Usuários nas roles db_owner e db_securityadmin (verifica em todos os databases)

Assim como no exemplo anterior, para identificar quem são os usuários que estão nas databases roles db_owner ou db_securityadmin, agora analisando em todos os databases da instância, execute o script abaixo:

Result:

Usuários com IMPERSONATE USER em um database

Para identificar quem são os usuários que possuem a permissão IMPERSONATE USER explícita, podendo executar comandos como alguns usuários específicos do database, execute o script abaixo:

Result:

Usuários com IMPERSONATE USER (verifica em todos os databases)

Assim como no exemplo anterior, para identificar quem são os usuários que estão nas databases roles sysadmin ou db_securityadmin, agora olhando em todos os databases da instância, execute o script abaixo:

Result:

Prática muito comum no uso do EXECUTE AS é a sua utilização em objetos de banco, como Stored Procedures, para definir a permissão padrão que esses objetos serão executados. Ou seja, você pode criar uma Stored Procedure que será sempre executada como o usuário “dirceu.resende”, que é membro da server role sysadmin e portanto, tem permissão a fazer qualquer coisa, e qualquer pessoa que tiver acesso de EXECUTE nessa Stored Procedure pode executá-la, sem precisar ter privilégio de IMPERSONATE ANY LOGIN e ou ter acesso às atividades que essa Stored Procedure executa.

Antes dos exemplos, vou relembrar as formas possíveis de EXECUTE AS em objetos:

• CALLER: Executar como CALLER é o padrão; se nenhuma outra opção for especificada, o procedimento será executado no contexto de segurança do chamador.
• OWNER: Executar como OWNER executa o procedimento no contexto do proprietário do objeto. Se o objeto for criado em um esquema de propriedade do dbo ou pelo proprietário do banco de dados, o procedimento será executado com permissões ilimitadas.
• SELF: Executar como SELF executa no contexto de segurança do criador do objeto. Isso é equivalente a executar como usuário especificado, onde o usuário especificado é a pessoa que cria ou altera o objeto.
• LOGIN/USER: Conforme já descrito acima, você também pode utilizar um usuário (permissões a nível de database) ou login (permissões a nível de instância) específico no cabeçalho do objeto, permitindo que todos que tenham permissão de EXECUTE nesse objeto, como por exemplo, uma Stored Procedure, executem esse objeto com o contexto desse usuário/login especificado automaticamente.

Os tipos de objetos que podem ter a cláusula EXECUTE AS na definição são:

• Stored Procedures – CALLER | SELF | OWNER | ‘user_name’
• Funções (exceto inline table-valued functions) – CALLER | SELF | ‘user_name’
• DDL Triggers com Database Scope – CALLER | SELF | ‘user_name’
• DDL Triggers com Server Scope e triggers de logon – CALLER | SELF | ‘login_name’
• Queues – SELF | OWNER | ‘user_name’
• Azure SQL Database – Stored Procedures – CALLER | SELF | OWNER | ‘user_name’
• Azure SQL Database – Functions (exceto inline table-valued functions) – CALLER | SELF | OWNER | ‘user_name’
• Azure SQL Database – DDL Triggers com Database Scope – CALLER | SELF | ‘user_name’

Para demonstrar um uso comum de EXECUTE AS em Stored Procedures, vou criar uma SP na msdb para a execução de jobs passando o nome do job como parâmetro:

Agora vou tentar executar manualmente a sp_start_job:

e depois tentar executar por essa SP que eu criei:

Adicionando uma cláusula de EXECUTE AS num objeto, você não precisa fazer nada para que o usuário tenha acesso à essa SP, além da habitual permissão de EXECUTE nessa Stored Procedure, por exemplo. Vou repetir os exemplos conectado direto com o usuário “teste”, sem usar o EXECUTE AS em momento algum:

E agora, vou executar a Procedure que eu criei e que vai me permitir executar qualquer job da instância, mesmo sem ter permissões para isso no meu usuário:

Obs: Essa SP stpInicia_Job TEM que ser criada na msdb por conta da forma que as permissões funcionam no SQL Server. Se você criar uma Stored Procedure e dentro dela existirem consultas ou comandos em objetos de outros databases, o usuário executor terá que ter permissões explícitas nesses objetos, mesmo utilizando o EXECUTE AS. Caso tudo que esteja dentro da Stored Procedure referencie apenas objetos do próprio database, o executor não precisa ter nenhuma permissão nos objetos envolvidos dentro da SP, apenas permissão de EXECUTE na própria Stored Procedure.

Vale lembrar que quando você cria um objeto com o EXECUTE AS USER = ‘Usuario’, desativar ou negar o privilégio de connect para o login associado à esse usuário NÃO irá alterar o comportamento da utilização desse objeto, uma vez que as ações ao login acabam não influenciando as permissões a nível de database nessa situação (a não ser em casos em que o usuário é sysadmin, por exemplo, e não tem permissões explícitas no database desse objeto).

Ou seja, se uma SP tem a cláusula WITH EXECUTE AS USER = ‘Usuario1’ e você desativa o login associado à esse usuário, isso não irá fazer com que uma SP que tenha execute gere um erro quando alguém tentar executá-la. Mas se você renomear ou excluir esse usuário no banco, aí a SP irá parar de funcionar.

Teste #1 – IMPERSONATE ANY LOGIN

Para testar o impersonate utilizado indevidamente, vou pegar o mesmo usuário do exemplo (teste) e conceder o privilégio de IMPERSONATE ANY LOGIN.

E agora, vou conectar na instância com esse usuário:

E vamos tentar realizar o select na tabela Person, que eu apliquei um DENY SELECT para esse usuário:

Bom, ele não tem permissão mesmo. E se a gente utilizasse outro usuário para acessar os dados? Por exemplo, o usuário “dirceu.resende”, que é sysadmin..

Vamos conferir quais permissões ele passa a ter acesso após realizar o impersonate?

Result:

Humm.. E se a gente tentasse criar alterar uma tabela? Esse usuário “teste” só tem a permissão db_datareader..

É.. Esse usuário realmente consegue fazer tudo que o usuário impersonado poder fazer.. Como estou impersonando um usuário membro da server role sysadmin, um usuário comum agora consegue fazer QUALQUER COISA, até mesmo desligar o servidor! E será que ele quer continuar sendo um usuário “comum” ?

Pronto! Agora ele mesmo se transformou em sysadmin. Se quiser, pode até excluir os outros usuários sysadmin e “tomar” o controle da instância pra ele. Vejam como essa permissão pode ser extremamente perigosa!

E não pára por aí.. Olhem outro exemplo de como essa permissão, quando utilizada indevidamente, pode causar grandes estragos. Imaginem que você, DBA, tem uma rotina para logar as alterações realizadas na sua base de dados, como por exemplo, a que disponibilizo no post How to create an Audit trigger to log object manipulation in SQL Server.

Agora, imagine um usuário mal intencionado utilizando isso para fazer coisas erradas e colocar a culpa para cima de outro colega ? Vamos testar.. Primeiro, vou criar a tabela e alterar uma coluna. Todos os logs vão ser gravados utilizando o usuário que realizou as ações.

E agora, para excluir a tabela, vou utilizar o EXECUTE AS para apagar como se fosse outro usuário.

Pronto. Fiz besteira e ainda coloquei a culpa no colega. Quer resolver isso? Comece a utilizar a função ORIGINAL_LOGIN() nas suas rotinas de auditoria e log.. Para demonstrar isso, vou alterar a trigger que criei para criar essa auditoria:

Agora, vou realizar novamente a criação de uma tabela, alterá-la e impersonar o login “dirceu.resende” para realizar a exclusão da tabela e deixar a responsabilidades nas contas dele:

É, dessa vez não deu certo.. A função ORIGINAL_LOGIN() revelou o real executor dos comandos, mesmo com o EXECUTE AS.

Teste #2 – Teste com IMPERSONATE USER

Um outro teste que podemos tentar, é liberar o privilégio de impersonate em um usuário específico. Por exemplo, quero que o usuário “teste” possa utilizar o comando EXECUTE AS apenas como o usuário “teste2”, ambos com baixo nível de acesso. Para isso, vou criar o usuário “teste2”, com as mesmas permissões do “teste”, mas sem o DENY de select na tabela Person.

Usuário criado, vamos tentar utilizar o EXECUTE AS a partir do usuário “teste”:

Obviamente, deu erro. O usuário “teste” não possui permissão de IMPERSONATE. Vamos conceder a permissão e tentar de novo:

Vamos testar de funcionou:

Opa! Agora estou executando os comandos como se fosse o usuário “teste2”. O usuário “teste” não consegue fazer consultas na tabela Person, pois ele tem um DENY SELECT lá. Vamos tentar ler os dados através do usuário “teste2” com o EXECUTE AS:

Opa! Conseguimos ler os dados da tabela, mesmo o usuário “teste” não tendo permissão para isso. Vamos confirmar se ele não consegue mesmo:

É.. Não dá mesmo, só com o EXECUTE AS.. E será que consigo utilizar o EXECUTE AS em outro usuário que não tenho acesso ?

Maravilha! Não consegui aplicar o IMPERSONATE para outro usuário, ainda mais o “dirceu.resende”, que é sysadmin.. Imaginem o estrago que isso poderia causar..

Um ponto importante que deve ser considerado no impersonate, é quando a instância possui um limitador de recursos para determinados usuários, o que pode ser feito utilizando o Resource Governor no SQL Server.

O que eu quero testar aqui é se o EXECUTE AS “engana” o Resource Governor também, conseguindo executar uma consulta sem ser limitado pelo RG.

Para isso, vou criar o Resource Pool, Workload Group, a função de classificação e habilitar o Resource Governor na instância, limitando os recursos que podem ser utilizados pelo usuário “teste”:

Após a criação e ativação do Resource Governor, vou executar uma consulta pesada para testar a limitação dos recursos:

Enquanto a consulta é executada, vou olhar na Query para retornar as consultas em execução (sp_WhoIsActive sem consumir TempDB) qual o profile que está sendo utilizado para esta consulta:

Pois bem. A consulta utilizando o usuário “teste” estava utilizando o grupo de workload “Grupo” do Resource Governor, que possui limitações de IOPS, CPU e memória, conforme configurei. Vou criar o usuário “dirceu.resende” para o login sysadmin “dirceu.resende” no database AdventureWorks e liberar a permissão de IMPERSONATE nesse usuário para o usuário “teste”:

Agora vamos testar utilizar o EXECUTE AS USER para um usuário que não tenha essa limitação, como o usuário “dirceu.resende”:

Como era de se esperar, o Resource Governor não foi “enganado” pelo EXECUTE AS USER e continuou limitando o usuário “teste” mesmo disfarçado de “dirceu.resende”, uma vez que ele herda apenas os “poderes” a nível de database e não da instância. Agora, o que acontece se eu tentar executar o EXECUTE AS LOGIN ? Vamos testar!

E para a minha surpresa, mesmo recebendo os “poderes” de um sysadmin, o Resource Governor continuou limitando os recursos do usuário “teste”, mesmo utilizando o EXECUTE AS LOGIN do usuário “dirceu.resende” (e sim, eu abri uma nova sessão para testar):

E agora, vou executar a consulta com o próprio usuário “dirceu.resende”, sem utilizar o impersonate, que irá nos mostrar que o Resource Governor agora não está limitando os recursos dessa sessão (pool default):