Trabalhando com Trigger de Logon no SQL Server

O trigger de logon foi introduzido no SQL Server 2005 SP2 para atender a certificação de segurança chamada Common Criteria (CC), que garante a entrega de produtos seguros para habilitar a segurança da infraestrutura de informações das organizações.

Esse trigger trata-se de Triggers DDL que são criados no nível da Instância ou database, acionando procedimentos armazenados em resposta a um evento de Logon, que ocorre quando é estabelecida uma sessão de usuário com uma instância do SQL Server. Seu acionamento dar-se-á após o término da autenticação, mas antes da sessão do usuário ser realmente estabelecida.

Vale ressaltar que o trigger de logon não é acionado quando houver falha na autenticação.

Embora possamos criar vários triggers no evento de Logon, poderemos definir, se for o caso, qual será o primeiro (First) a disparar e qual será o último (Last) e, no caso de haver muitos triggers, somente será respeitado a ordem de execução dos indicados como First e Last, enquanto que os outros triggers serão executados em ordem aleatória.

Para que possamos indicar qual será o primeiro e o último trigger, devemos usar a System Stored Procedure sp_settriggerorder @triggername=, @order=, @stmttype=, @namespace=, onde os parâmetros são definidos conforme a Tabela 1.

Tabela 1. Definição dos parâmetros

Antes do SQL Server disparar um trigger de logon, uma transação implícita é criada independente de qualquer transação do usuário. Dessa forma, quando o primeiro trigger de logon for disparado a contagem de transação será 1 e, ao finalizar todos os triggers, a transação será confirmada.

O Rollback Transaction zera a contagem de transações, enquanto o Commit Transaction pode decrementar a contagem das transações para 0. Sendo assim, utilizar o Commit Transaction dentro de trigger de logon não é aconselhável.

Podemos utilizar o trigger de logon para auditar e controlar as sessões em uma instância como, por exemplo, restringir a atividade de logon, restringindo os logons no SQL Server ou limitando o número de sessões para um logon especifico.

Para que possamos capturar as informações necessárias para nossa auditoria dentro de uma trigger de logon, utilizamos a função EVENTDATA(), que retorna informações sobre os eventos da instância ou banco de dados e, dessa forma, poderemos obter os dados da conexão que disparou o trigger. O EventData() retorna dados em XML.

Vale ressaltar que o EVENTDATA() só retornará dados quando for referenciado dentro um trigger de logon ou DDL e, no caso de ser referenciado por outras rotinas, seu retorno será NULL.

O evento de Logon do EVENTDATA(), retorna o seguinte esquema em XML presente na Listagem 1.

Listagem 1. Esquema EVENTDATA()

<EVENT_INSTANCE>
  <EventType>LOGON</EventType>       
  <PostTime>2015-07-12T21:10:35.254</PostTime>          
  <SPID>85</SPID>           
  <ServerName>SERVER\INST_01</ServerName>            
  <LoginName>DOMAIN\User01</LoginName> 
  <LoginType>Windows Login</LoginType>        
  <SID>sid</SID>               
  <ClientHost>0.0.0.0</ClientHost>          
  <IsPooled>0</IsPooled>            
</EVENT_INSTANCE>

Cenário

Em nosso cenário iremos trabalhar especificadamente com trigger de logon, mas existem outras possibilidades, pois trata-se de triggers DDL. Sendo assim, poderemos criar trigger para coibir, por exemplo, DROP TABLE, ALTER TABLE, entre outros. Mas isso poderá ser tratado num outro artigo.

Agora vamos ao cenário: imagine que você é o DBA de uma empresa onde os dados são de suma importância para o negócio, ou seja, os mesmos não podem ser manipulados de forma a não atender as regras de negócios. Você deverá aplicar as boas práticas em todos os ambientes SQL Server e, uma delas é não permitir que usuários/desenvolvedores possam se conectar usando logins de sistema através do SQL Server Management Studio (SSMS) para realizar qualquer operação que seja.

Vocês podem se perguntar: mas é só não passar a senha desses usuários de sistema. Mas lembre-se de que esses usuários não foram criados através de um domínio, mas sim, com autenticação do SQL Server.

Sem uma política de segurança implantada, qualquer pessoa de posse desses usuários poderá manipular os dados sem que ninguém tenha conhecimento, causando assim impactos catastróficos percebidos somente tempos depois.

Vale ressaltar que esses logins de sistemas criados como autenticação do SQL Server possuem permissões de Insert/Delete/Execute/Update/Select; permissões essas que os usuários/desenvolvedores não possuem.

Esse cenário aplica-se principalmente onde não existe um DBA e, certamente se você está se vendo nessa situação, sabe o quanto é custoso realizar um trabalho de segurança quando os usuários/desenvolvedores possuem todos os logins de sistemas e, com isso, a possibilidade de realizar alterações nos dados sem nenhum critério. Sem contar que a manipulação de dados quando realizada pelos usuários/desenvolvedores utilizando-se desses logins dificultará e muito encontrar quem foi o responsável por efetuar as alterações. Agora se estiver com a auditoria habilitada, será mais tranquilo realizar esse levantamento.

Para coibirmos essas tentativas, temos a nossa disposição o trigger de logon; onde um usuário, ao tentar estabelecer uma conexão com a instância usando um login de aplicação, receberá uma mensagem de erro e os dados dessa tentativa ficarão registrados numa tabela onde somente o DBA terá acesso, para posteriormente ser analisado e, em seguida, tomar decisões de como proceder administrativamente, pois trata-se de uma política de segurança rígida e que não permite esse tipo de ação.

Pensando em futuras implementações com outras validações/auditorias no ambiente SQL Server, pense na possibilidade da criação de um database ou, se desejar maior controle e segurança, uma instâcia SQL Server para a área de banco de dados, onde somente o DBA terá acesso.

Nesse nosso cenário, vamos assumir que iremos criar um database para armazenar todas as tentativas de login com usuários de sistemas.

Algumas etapas deverão ser seguidas para a criação do trigger de logon, como veremos a seguir.

Criação do database

Com o script da Listagem 2 criaremos o database TesteDB para que possamos criar nossa tabela onde ficarão armazenados os dados de nossa auditoria de logon.

Listagem 2. Criação do Database

CREATE DATABASE [TesteDB]
   CONTAINMENT = NONE
   ON  PRIMARY 
  ( NAME = N'TesteDB', FILENAME = N'H:\BD2012\TesteDB.mdf' , SIZE = 4160KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024000KB )
   LOG ON 
  ( NAME = N'TesteDB_log', FILENAME = N'H:\Log2012\TesteDB_log.ldf' , SIZE = 2048KB , MAXSIZE = UNLIMITED , FILEGROWTH = 524288KB )
  GO

Repare que o banco é iniciado com o tamanho aproximado de 4 Mb (Size), o tamanho máximo (MaxSize) ilimitado e o crescimento do database (FileGrowth) é feito a cada 1 Gb. Já o Transaction Log terá o TesteDB_Log (Name), localizado, nesse caso, no mesmo diretório do arquivo de dados (o que nas melhores práticas não é indicado, mas isso é feito apenas para fins didáticos). O tamanho inicial do log (Size) é de 2 Mb e o tamanho máximo (MaxSize) ilimitado, com o crescimento do log (Filegrowth) a cada 512 Mb.

Existência de trigger de logons

Temos que saber se já existe algum trigger de logon. Caso exista, teremos que definir qual será o primeiro (First) ou último (Last) a ser executado e para essa definição usaremos a System Stored Procedure sp_settriggerorder. Temos que ter critérios para definir quem será a primeira ou a última, caso contrário, os triggers serão executados aleatoriamente.

Para essa verificação podemos executar o comando a seguir:

 select * from sys.server_triggers

A ideia é não retornar nada, como vemos na Figura 1.

Figura 1. Verificar se existe algum trigger

Criando um usuário de aplicativo

Para que possamos realizar nossos testes adequadamente, um usuário de sistema com autenticação SQL Server se faz necessário. Esse usuário terá permissão de db_datareader e db_datawriter. Para nosso teste, iremos criar o usuário appMob com o código da Listagem 3.

Listagem 3. Criação do Login, Usuário e permissões

USE [master]
  GO
  CREATE LOGIN [appMob] WITH PASSWORD=N'@@_M0b2o!5', DEFAULT_DATABASE=[TesteDB], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
  GO
   
  -- Criacao do usuario no database
  USE [TesteDB]
  GO
  CREATE USER [appMob] FOR LOGIN [appMob]
  GO
   
  -- Permissao de leitura no database
  USE [TesteDB]
  GO
  ALTER ROLE [db_datareader] ADD MEMBER [appMob]
  GO
   
  -- Permissao de escrita no database
  USE [TesteDB]
  GO
  ALTER ROLE [db_datawriter] ADD MEMBER [appMob]
  GO

Após cumprirmos essas etapas, vamos a criação dos objetos para nossa auditoria com o trigger de logon, lembrando que o usuário que estamos usando nesse caso é o “sa”, pois trata-se de um ambiente stand-alone com intuito de testes locais. Mas em seu ambiente deverá utilizar um usuário com privilégios de ddladmin para executar esses procedimentos.

Criando tabela de auditoria

Criaremos a tabela de auditoria conforme o código da Listagem 4. Ela é necessária para que o trigger possa logar todas as tentativas de conexão. Em nosso artigo criamos um database para esse fim, mas você poderá realizar esses procedimentos no database que achar mais conveniente. Porém, não realize esses procedimentos nos system databases.

Listagem 4. Criação da tabela

CREATE TABLE DBA_Aud_Logon(
         dal_ID int not null identity(1,1),
         dal_Server varchar(50),
         dal_Login varchar(100),
         dal_NomeHost varchar(100),
         dal_Aplicacao varchar(200),
         dal_IPClient varchar(30),
         dal_DataEvento datetime
  )

Trigger de Logon

Agora iremos nos concentrar em criar o trigger de logon, não esquecendo que o foco desse artigo será logar as tentativas de conexão usando usuários de sistemas através do SQL Server Management Studio (SSMS).

Nesse momento, teremos que ficar atentos a toda codificação, pois uma validação errada e certamente poderemos impactar todo ambiente de produção. Recomendo que antes de executar o trigger em qualquer ambiente que seja, analise o código quantas vezes forem necessárias, evitando assim surpresas desagradáveis.

Nesse nosso exemplo, se esquecermos de indicar a verificação do APP_Name(), que nesse caso está indicando o SQL Server Management Studio (SSMS), toda tentativa de login do usuário appMob será bloqueada e, com isso, a aplicação retornará um para o usuário. Caso isso venha a ocorrer, desabilitaremos o trigger, como veremos mais a seguir. Confira a programação presente na Listagem 5.

Listagem 5. Criação do trigger

  IF  EXISTS (SELECT * FROM master.sys.server_triggers WHERE parent_class_desc = 'SERVER' AND name = N'TRG_DBA_AUDLOGON')
  DROP TRIGGER [TRG_DBA_AUDLOGON] ON ALL SERVER
  GO
   
  -- Criacao do trigger de logon
  CREATE TRIGGER TRG_DBA_AUDLOGON
  ON ALL SERVER WITH EXECUTE AS 'sa'
  FOR LOGON
  AS
  BEGIN
         SET NOCOUNT ON
      DECLARE 
         @vEvtData xml,
         @vEventTime datetime,
         @vServer varchar(40),
         @vLoginName varchar(50),
         @vIpClient varchar(50),      
         @vHostName varchar(50),
         @vAppName varchar(500)
   
         -- Verifica se o login utilizado eh appMob e se a aplicacao a ser utilizada é o Management Studio
         IF ORIGINAL_LOGIN() = 'appMob' and APP_NAME() LIKE 'Microsoft SQL Server Management Studio%'
         begin
               -- Variavel que recebera o eventdata()
              SET @vEvtData = eventdata()
              -- Variavies que receberao os dados do eventdata()      
              SET @vEventTime = @vEvtData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
              SET @vServer = @vEvtData.value('(/EVENT_INSTANCE/ServerName)[1]', 'varchar(40)')
              SET @vLoginName = @vEvtData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)')
              SET @vIpClient = @vEvtData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)')
              SET @vHostName = HOST_NAME()
              SET @vAppName = APP_NAME()
              
         -- Nao permite continuar com a conexao, retornando uma janela com erro      
               ROLLBACK
   
         -- Se for o evento será logado, pois esta havendo uma tentativa de burlar a segurança dos dados
              INSERT [TesteDB]..DBA_Aud_Logon(dal_DataEvento,dal_Server,dal_Login,dal_NomeHost,dal_Aplicacao,dal_IPClient)
               -- Estamos retornando o xml os valores para que possamos logar na tabela da funcao EventData
              SELECT  @vEventTime,@vServer, @vLoginName, @vHostName, @vAppName, @vIpClient                  
               end   
  END

Após a criação do trigger no SQL Server Management Studio poderemos verificar no Object Explorer, no item Server Objects e subitem Triggers, que o mesmo foi criado, conforme a Figura 2.

Na Figura 3 e Listagem 6 poderemos verificar um código T-SQL retornando a verificação em duas system tables. A primeira parte retorna os dados do trigger como nome, data de criação, entre outras informações, e a segunda parte retorna o evento do trigger, no nosso caso LOGON (Type_Desc).

Figura 2. Item Server Objects -> Triggers

Figura 3. Código T-SQL para verificar o trigger criado

Listagem 6. Comando T-SQL para verificar os triggers existentes

-- Verificar quais triggers existem
  select name, object_id, parent_class_desc, type,  type_desc, create_date, modify_date from sys.server_triggers
   
  -- Verificar quais eventos, no nosso caso trata-se de um trigger de logon (type_desc)
  select * from sys.server_trigger_events

Agora iremos para a parte que nos interessa: realizar testes para verificar a funcionalidade do trigger.

Estabelecendo conexão com o usuário “sa”

Esse teste poderia ser realizado com qualquer outro usuário que não fosse o appMob, mas como esse database é meramente para testes, o usuário “sa” continua habilitado. Lembre-se que esse usuário num ambiente de produção deveria estar desabilitado, atendendo assim a uma das boas práticas de segurança no ambiente SQL Server.

Na Figura 4 podemos verificar os dados para conexão com o usuário “sa” e após o click no botão Connect poderemos ver que o logon foi realizado com sucesso, conforme código T-SQL da Listagem 7 e Figura 5.

Figura 4. Conexão com o usuário sa

Figura 5. Tabela de log sem registros

Listagem 7. Comandos T-SQL para verificar dados após a conexão

-- Autenticando com SA nenhum registro foi logado
   
  -- Verifica o usuario logado
  select SUSER_SNAME() as login
   
  -- Verifica se foi logado algum registro após a conexão
  select * from DBA_Aud_logon

Nosso próximo teste iremos utilizar o usuário appMob e veremos que o resultado será o esperado, ou seja, alguém tentará logar com o referido usuário (Figura 6) e receberá uma mensagem (Figura 7) que impossibilitará o usuário de continuar. Como podemos ver, a mensagem de erro não é nada amigável para o usuário.

Figura 6. Conexão com o usuário appMob

Figura 7. Trigger retornou mensagem de erro

Para que possamos verificar se o trigger realmente disparou teremos que nos logar no SQL Server Management Studio utilizando um outro login que não faça parte dessa regra de segurança e que tenha permissão no database e tabela de log.

Após logar, realizaremos uma consulta da tabela DBA_Aud_Logon para ver quantas tentativas houveram e de quais máquinas partiram usando o comando a seguir:

 select * from DBA_Aud_logon

Infelizmente, se os usuários/desenvolvedores possuem acesso remoto liberado no servidor de banco de dados esse resultado não poderá ajudar, conforme podemos ver na Figura 8, onde o primeiro registro mostra que houve uma tentativa na máquina local, ou seja, diretamente no servidor de banco de dados. Agora, se a tentativa for de uma máquina remota, teremos informações necessárias para levantar quem tentou se conectar (campos dal_NomeHost e dal_IPClient) e, com isso, poderemos tomar todas as medidas necessárias.

Figura 8. Tentativas de autenticar

Com esse trigger resolvemos o problema de alguém tentar se conectar ao database utilizando o usuário de sistemas, o appMob, auditando assim tentativas proibidas.

Agora, se por algum motivo você não se atentou a regra e não consegue se logar no SQL Server Management Studio, poderemos desabilitar o trigger.

Desabilitando o Trigger de Logon

Através do DAC (Dedicated Administrator Connection) poderemos estabelecer a conexão. Nele digite o seguinte comando:

 Sqlcmd –S Localhost –d master –A

Onde:

• –S (Servidor) = nossa instância, nosso caso localhost (Local);
• -d (Database) = em nosso caso, o máster;
• -A (Conexão) = Conexão Administrator dedicado.

Lembrando que se estiver numa instância nomeada, basta substituir o localhost pela mesma.

Após confirmar o comando a janela do SQLCMD irá abrir para que você possa digitar o comando para desabilitar a trigger, como poderemos ver na Figura 9.

Figura 9. Desabilitando o trigger de Logon

Feito esse procedimento, poderemos logar novamente usando o SQL Server Management Studio e confirmar se o trigger foi realmente desabilitado. Para isso utilizaremos o comando T-SQL da Listagem 8.

Podemos também tentar logar com o usuário appMob, pois se não retornar a mensagem de erro o processo de desabilitar o trigger ocorreu com sucesso. Na Figura 10 podemos ver que o comando T-SQL da Listagem 8 retornou um registro, que no nosso caso é o trigger desabilitado.

Figura 10. Retornando registro indicando o trigger desabilitado

Listagem 8. Verificar se o trigger foi desabilitado

select name, parent_class_desc, type,type_desc 
  from sys.server_triggers 
  where is_disabled = 1

Agora, se necessitar excluir o trigger, poderemos utilizar duas possibilidades:

1. Utilizando o comando da Listagem 9:
   Listagem 9. Comando T-SQL para excluir um trigger de logon
   

   
   USE [master]
   GO
    
   DROP TRIGGER [TRG_DBA_AUDLOGON] ON ALL SERVER
   GO

2. Através do Object Explorer, como mostra a Figura 11.
   
   Figura 11. Excluindo trigger de logon

Todos os procedimentos realizados nesse artigo foram num ambiente stand-alone, onde o impacto é zero, mas num ambiente de produção real esses triggers habilitados podem causar riscos. Sendo assim, cada empresa possui suas particularidades, então não economize em testes e sempre utilize um ambiente que não tenha vínculo com os servidores de produção, dessa forma, você se garante e mostrará que sua organização é algo que a empresa poderá contar sempre, afinal de contas, nunca é demais ser precavido.

Até um próximo artigo.