none
Criação de Trigger de Logon RRS feed

  • Pergunta

  • Olá Pessoal Bom Dia.

    Estou criando uma trigger de Logon para evitar que os Desenvolvedores conectem ao Banco de Dados com os Usuários que estão no Client da Aplicação das Filiais. Problema que não é possível criptografar a senha no Client.

    A questão é a seguinte, criei uma tabela de controle, onde irei inserir o nome do Usuário e nome da Aplicação que é permitida, quando o Usuário se conectar e a aplicação não for a que esta na Tabela, a conexão não será permitida.

    create table validate_login ( 
        login_name      varchar(30), 
        program_name    varchar(30) 
    ); 

    Trigger Simples que funciona:

    create trigger tr_priv_login 
        on all server with execute as 'sa'
        for logon 
        as
        begin
        if original_login() in (select login_name from validate_login) 
                and app_name() not in (select program_name from validate_login where login_name=original_login()) 
            begin
                raiserror(60000,16,1) 
                rollback; 
            end; 
        end;

    Até aqui sem problemas funciona, ocorre que tenho várias regras no banco e outras triggers que ao serem disparadas enviam e-mail através da stored procedure msdb.dbo.sp_send_dbmail, estou tentando fazer com que esta trigger enviei e-mail quando a condição do if for atendida, tentei dessa forma:

    create trigger tr_priv_login
    on all server with execute as 'sa'
    for logon
    as
    declare @msg nvarchar(max)
    declare @bodymsg nvarchar(max)

    if original_login() in (select login_name from validate_login)
    and app_name() not in (select program_name from validate_login where login_name=original_login())

    begin
    set @msg = cast((select original_login() as 'td','',host_name() as 'td','', app_name() as 'td','',convert(varchar(36),getdate(),131) as 'td'
    for xml path('tr'), elements) as nvarchar(max))

    set @bodymsg = '<html><body><H3> Auditoria Login </H3>
    <table border = 1>
    <tr bgcolor = "#C6CFFF">
    <th> Usuário </th> <th> Terminal </th> <th> Programa </th> <th> Data </th></tr>'

    set @bodymsg = @bodymsg + @msg + '</table></body></html>'

    execute msdb.dbo.sp_send_dbmail
    @profile_name='DBA Administration',
    @recipients = 'wendercruz@flavios.com.br',
    @body = @bodymsg,
    @body_format = 'HTML',
    @subject = 'Auditoria Login';

    raiserror (60000,16,1)
    rollback;

    end;

    O código acima continua funcionando, esta bloqueando da forma desejada, porém não envia e-mail, fiz duas condições de IF a primeira enviando o e-mail e uma segunda condição IF realizando o rollback da sessão, mesmo assim não envia o -email.

    Aguem teria alguma ideia?

    Abraços.

    sexta-feira, 16 de maio de 2014 12:42

Respostas

  • Junior,

    Fiz a verificação que solicitou e realmente o system_user é do sa, neste caso ele não esta dando o kill porque não pode fazer kill na sua própria sessão. Mas como vou conseguir pegar o sid do usuário que fez login se a execução da trigger é feita pelo sa.

    Wender,

    Este seu processo de alteração de contexto está ficando complexo e qualquer pequena alteração em sua instância SQL poderá parar esta trigger e consequentemente poderá afetar todo os acessos.

    Talvez seja interessante você isolar o envio do e-mail em uma outra tarefa (um Job ou um Policy Management). Segue uma sugestão:

    Na trigger você pode inserir estas tentativas de acesso para uma tabela de Histórico e nesta tabela você pode manter alguns dados sobre esta tentativa de acesso(nome do usuário, data/hora, ...). Inclua uma coluna "Email Enviado" para servir como flag para identificar se já houve envio do e-mail.

    Como disse, crie em um job ou policy uma tarefa para você pode gerar um verificador se existem registros onde a flag "Email Enviado" for igual à FALSE e então você executa seu script para enviar o e-mail. 

    Não esqueça de marcar como resposta todos os posts que ajudaram na solução !

    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"
    quinta-feira, 22 de maio de 2014 17:52
    Moderador

Todas as Respostas

  • Wender,

    Você configurou e verificou se o seu Database Mail está funcionando corretamente ?

    Veja alguns links abaixo:

    http://technet.microsoft.com/pt-br/library/ms175887(v=sql.105).aspx

    http://technet.microsoft.com/pt-br/library/ms186358.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"
    sexta-feira, 16 de maio de 2014 14:21
    Moderador
  • Bom Dia Durval,

    Sim o mesmo esta configurado corretamente, para outras triggers utilizo a mesma logica de variáveis e código e envia e-mail corretamente. Profile esta correto e configurações do DataBase Mail também estão corretas, o mesmo envia e-mail de teste.

    Não sei se o fato de não enviar o e-mail após a validação da condição, esta ligada ao fato do Usuário não conseguir conectar ao Banco, já que se a condição for VERDADEIRA, o mesmo não consegue efetuar login.

    Se comentar o trecho do código 

    raiserror (60000,16,1)
    rollback

    O mesmo envia o e-mail e consequentemente o usuário efetua login.

    sexta-feira, 16 de maio de 2014 14:27
  • Bom Dia Durval,

    Sim o mesmo esta configurado corretamente, para outras triggers utilizo a mesma logica de variáveis e código e envia e-mail corretamente. Profile esta correto e configurações do DataBase Mail também estão corretas, o mesmo envia e-mail de teste.

    Não sei se o fato de não enviar o e-mail após a validação da condição, esta ligada ao fato do Usuário não conseguir conectar ao Banco, já que se a condição for VERDADEIRA, o mesmo não consegue efetuar login.

    Se comentar o trecho do código 

    raiserror (60000,16,1)
    rollback

    O mesmo envia o e-mail e consequentemente o usuário efetua login.

    Wender,

    Se tudo está funcionando quando você remove o RAISERROR então temos o problema isolado.

    Faça um teste, insira um WAIT antes do erro. Faça a adaptação em seu script:

    execute msdb.dbo.sp_send_dbmail
    @profile_name='DBA Administration',
    @recipients = 'wendercruz@flavios.com.br',
    @body = @bodymsg,
    @body_format = 'HTML',
    @subject = 'Auditoria Login';


    WAITFOR DELAY '00:00:20'
    RAISERROR(60000,16,1)

    ROLLBACK TRAN


    Coloque este bloco dentro de um TRY... CATCH 


    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"

    sexta-feira, 16 de maio de 2014 14:49
    Moderador
  • Durval fiz o teste com TRY..CATCH, mas não funcionou. Código ficou da seguinte forma:

    begin try
    execute msdb.dbo.sp_send_dbmail
    @profile_name='DBA Administration',
    @recipients = 'wendercruz@flavios.com.br',
    @body = @bodymsg,
    @body_format = 'HTML',
    @subject = 'Auditoria Login';

    end try

    begin catch
    WAITFOR DELAY '00:00:05'
    raiserror (60000,16,1)
    end catch

    rollback
    end;

    Se colocar rollback dentro do begin catch, o usuário consegue logar no banco e e-mail é enviado, se deixar desta forma, usuário não loga mas também não envia e-mail.

    sexta-feira, 16 de maio de 2014 17:34
  • Wender,

    Desculpe a demora em responder, mas você conseguiu encontrar uma solução ?

    Se sim, insira um post com a solução para ajudar outros que tenham um problema semelhante, caso contrário vamos continuar à ajudar você neste problema.

    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"
    terça-feira, 20 de maio de 2014 18:23
    Moderador
  • Olá Durval,

    Estou testando uma outra forma de fazer esse processo, não esta funcionando porém estou verificando o motivo, fiz o seguinte, na primeira condição do IF estou enviando o e-mail e realizando um commit tran, com isso o Usuário poderá fazer login, após isto estou fazendo outro IF e dar um Kill na sessão do usuário, porém não esta efetivando o kill, segue abaixo o trecho do código, se puder ajudar agradeço.

    execute msdb.dbo.sp_send_dbmail
    @profile_name='DBA Administration',
    @recipients = 'wendercruz@flavios.com.br',
    @body = @bodymsg,
    @body_format = 'HTML',
    @subject = 'Auditoria Login';

    commit tran
    if original_login() in (select login_name from validate_login)
    and app_name() not in (select program_name from validate_login where login_name=original_login())
    begin
    declare @temptable table (id int)
    declare @killsession varchar(500)

    insert into @temptable
    values(@@spid)

    select @killsession = 'kill '+cast(id as varchar(30)) from @temptable
    EXECUTE (@killsession)
    --raiserror (60000,16,1)

    end
    end;

    terça-feira, 20 de maio de 2014 20:35
  • Wender,

    Então de qualquer forma o Login esta acontecendo!!!

    Mas se o usuário e a aplicação não tiverem permissão você esta fazendo o Kill?

    Então acredito que é justamente este o ponto do problema, você esta tentando fazer Kill da própria conexão que esta ativa, ao meu ver neste caso, você terá que abrir uma nova conexão, trocar o contexto da conexão e fazer o Kill da outra.



    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    quarta-feira, 21 de maio de 2014 13:38
  • Olá Junior,

    A questão seria, os usuários que estiverem na tabela validate_login irão entrar na condição, para enviar o e-mail ele precisa conectar, só que preciso fazer um kill após isso, na trigger with execute as 'sa' não estaria mudando o contexto para conexão do sa?

    Tentei até criar um procedure para fazer o kill ao invés de inserir dentro da trigger, segue abaixo:

    create procedure sp_kill_logon (@pid int)
    	as
    	declare @statment		varchar(500)
    	
    	set @statment = 'kill ' +cast(@pid as varchar(30))
    
    	execute (@statment)

    Mesmo assim não ocorre o kill, a questão seria a seguinte, quando conecto no banco com um usuário que esta na tabela validate_login, quem executa a trigger é o usuário sa ou o usuário que conectou? Porque se for o usuário que conectou então não é possível fazer um kill na sua própria sessão.

    quarta-feira, 21 de maio de 2014 14:54
  • Wender,

    Ok, entendi.

    Então como Execute as 'Sa' você esta passando a conta de usuário System Administrator para fazer o processamento da Stored Procedure, isso é uma troca de contexto, mas bem perigosa, pois esta passando a conta mais importante do seu servidor.

    Após isso o usuário faz a conexão, e você vai fazer o Kill, no momento que esta processando o Kill, sabe me dizer qual é a conta de login que esta em uso?

    O que eu entendo é que a Stored Procedure esta sendo executada e processada para o usuário SA, somente isso.

    "a questão seria a seguinte, quando conecto no banco com um usuário que esta na tabela validate_login, quem executa a trigger é o usuário sa ou o usuário que conectou? Porque se for o usuário que conectou então não é possível fazer um kill na sua própria sessão."

    É justamente isso que eu estou querendo entender!!!! Através da função System_User, você vai conseguir obter este retorno.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]


    quarta-feira, 21 de maio de 2014 17:11
  • Junior,

    Fiz a verificação que solicitou e realmente o system_user é do sa, neste caso ele não esta dando o kill porque não pode fazer kill na sua própria sessão. Mas como vou conseguir pegar o sid do usuário que fez login se a execução da trigger é feita pelo sa.

    quarta-feira, 21 de maio de 2014 17:50
  • Junior,

    Fiz a verificação que solicitou e realmente o system_user é do sa, neste caso ele não esta dando o kill porque não pode fazer kill na sua própria sessão. Mas como vou conseguir pegar o sid do usuário que fez login se a execução da trigger é feita pelo sa.

    Wender,

    Este seu processo de alteração de contexto está ficando complexo e qualquer pequena alteração em sua instância SQL poderá parar esta trigger e consequentemente poderá afetar todo os acessos.

    Talvez seja interessante você isolar o envio do e-mail em uma outra tarefa (um Job ou um Policy Management). Segue uma sugestão:

    Na trigger você pode inserir estas tentativas de acesso para uma tabela de Histórico e nesta tabela você pode manter alguns dados sobre esta tentativa de acesso(nome do usuário, data/hora, ...). Inclua uma coluna "Email Enviado" para servir como flag para identificar se já houve envio do e-mail.

    Como disse, crie em um job ou policy uma tarefa para você pode gerar um verificador se existem registros onde a flag "Email Enviado" for igual à FALSE e então você executa seu script para enviar o e-mail. 

    Não esqueça de marcar como resposta todos os posts que ajudaram na solução !

    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"
    quinta-feira, 22 de maio de 2014 17:52
    Moderador
  • Durval Bom Dia,

    Entendi, é uma boa alternativa. Vou implementar no DB de Homologação e posto se ficou de acordo.

    sexta-feira, 23 de maio de 2014 13:02
  • Wender,

    Concordo com a sugestão e observação do Durval.

    Eu sou contra utilizer o comando Execute As ou fazer a troca de contexto para o usuário SA!!!!! 


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    sexta-feira, 23 de maio de 2014 15:30
  • Olá, bom apenas reportando referente a criação da trigger.

    Realmente não consegui implementar como trigger, mas consegui obter o resultado final de outra forma, segue abaixo:

    -- Procedure para fazer kill sessão 
    create procedure sp_kill_logon (@pid int) 
        as
        declare @statement       varchar(500) 
          
        set @statement = 'kill ' +cast(@pid as varchar(30)) 
      
        execute (@statement) 

    Agora Select para enviar e-mail com os dados e fazer o kill da sessão:

    -- Select para monitorar conexões não autorizadas, envia e-mail e faz kill na sessão 
    -- Tabela de Controle, validate_login 
    declare @pid        int
    declare @msg        nvarchar(max) 
    declare @bodymsg    nvarchar(max) 
      
    if (select count(*) from master.sys.sysprocesses t1 
        where t1.loginame in (select login_name from validate_login) 
            and t1.program_name not in (select program_name from validate_login 
                                            where login_name=t1.loginame)) >= 1 
          
            begin
              
            set @msg = cast((select t1.loginame as 'td','',t1.hostname as 'td','', t1.program_name as 'td','',convert(varchar,getdate(),105)+' '+substring(convert(varchar,getdate(),114),1,8) as 'td'
                                from master.sys.sysprocesses t1 
                                    where t1.loginame in (select login_name from validate_login) 
                                        and t1.program_name not in (select program_name from validate_login 
                                                                        where login_name=t1.loginame) 
                                for xml path('tr'), elements) as nvarchar(max)) 
      
            set @bodymsg = '<html><body><H3> Auditoria Login </H3> 
                            <table border = 1> 
                            <tr bgcolor = "#C6CFFF"> 
                            <th> Usuário </th> <th> Terminal </th> <th> Programa </th> <th> Data </th></tr>'
      
            set @bodymsg = @bodymsg + @msg + '</table></body></html>'
              
            execute msdb.dbo.sp_send_dbmail 
                @profile_name='DBA Administration', 
                @recipients = 'wendercruz@flavios.com.br', 
                @body = @bodymsg, 
                @body_format = 'HTML', 
                @subject = 'Auditoria Login'; 
    end; 
    select @pid=spid from master.sys.sysprocesses t1 
        where t1.loginame in (select login_name from validate_login) 
            and t1.program_name not in (select program_name from validate_login 
                                            where login_name=t1.loginame) 
    execute sp_kill_logon @pid 

    Apenas criei um job, e inseri o t-sql acima com execução recorrente a cada 01 minuto, ficou show de bola.

    Agradeço a ajuda,

    Abraços.



    • Editado Wender DBA segunda-feira, 26 de maio de 2014 19:06
    segunda-feira, 26 de maio de 2014 18:49