Uma visão geral dos gatilhos de logon no SQL Server

Uma visão geral dos gatilhos de logon no SQL Server

cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br


Este artigo fornece uma visão geral dos gatilhos de logon no SQL Server e seu uso para controlar o SQL Server
autenticação.

Introdução

O SQL Server fornece o método de autenticação Windows e SQL para conectar-se usando SSMS ou aplicativos cliente. Segurança é
sempre um foco principal para o DBA, especialmente no ambiente de produção. Podemos controlar o acesso ao banco de dados
fornecendo permissões mínimas e apropriadas para o trabalho do usuário. Podemos usar instruções GRANT ou DENY no SQL Server para
conceda permissões de tarefa apropriadas para o usuário individual.

O SQL Server possui os seguintes gatilhos:

  • Gatilhos da linguagem de definição de dados ou DDL

  • Linguagem de manipulação de dados ou gatilhos DML
  • Tempo de execução de idioma comum ou gatilhos CLR
  • Gatilhos de logon

Neste artigo, exploraremos os gatilhos de logon no SQL Server e seu uso.

Gatilhos de logon no SQL Server

Triggers é um código de banco de dados que é executado no caso de um evento definido com precisão. Podemos usar gatilhos de logon para
controlar a segurança de login do SQL. O SQL Server executa automaticamente os gatilhos de logon quando ocorre um evento de logon. isto
é executado antes que uma sessão do usuário seja estabelecida e a autenticação seja bem-sucedida.

Usamos dois termos para uma conexão bem-sucedida do banco de dados em qualquer banco de dados.

  • Autenticação: Especificamos um nome de usuário e senha para conectar usando a autenticação SQL. É uma validação das credenciais do usuário
  • Autorização: É uma permissão que permite executar tarefas específicas. Você pode estar autorizado a ter acesso total a um banco de dados específico, mas não a fazer alterações no nível da instância

Integração do gatilho de logon

Se algum usuário não conseguir se autenticar no SQL Server (credenciais incorretas), o SQL Server não executará o logon
gatilhos. O SQL Server inicia uma transação implícita antes que o logon seja acionado. É independente de qualquer usuário
transação. No caso do gatilho de logon, a contagem de transações é 1. Caso o SQL retorne uma exceção, seu
o valor é definido como zero. Ele falha na transação implícita e a sessão do usuário não é estabelecida. Conexão do usuário também
falha no caso de o erro ter gravidade mais significativa que 20 dentro do gatilho.

Vamos explorar o uso de casos de gatilhos de logon usando exemplos.

Restrinja o número total de conexões do SQL Server usando gatilhos de logon no SQL Server

Às vezes, exigimos restringir o número total de conexões do SQL Server. Muitas conexões podem causar
problemas de desempenho para que possamos usar gatilhos de logon para resolver esse propósito. Por padrão, o SQL Server permite um número ilimitado de
número de conexões simultâneas com o SQL Server. Você pode verificá-lo nas propriedades do SQL Server.

Número máximo de conexões

Nós podemos usar o DMV sys.dm_exec_connections diferenciar o sistema e o processo do usuário. No
abaixo da consulta, criamos um gatilho de logon para permitir no máximo três conexões. Você pode ver, usamos uma cláusula
PARA LOGON para os gatilhos de logon.

  • Nota: Não execute o código abaixo no ambiente de produção. Você deve limitar a conexão máxima apenas se necessário e após verificar os requisitos do ambiente

Depois que o gatilho for criado, faça três conexões de usuário e verifique-o usando DMV.

Processo do usuário conectado

Agora, se você tentar fazer uma conexão adicional, receberá o Erro: 17892 e ele receberá uma mensagem informando que o usuário
não pode conectar devido à execução do acionador.

Erro devido ao gatilho

Antes de avançarmos, solte esse gatilho de logon usando a seguinte consulta.

Coloque um limite no número de conexões do SQL Server de um usuário

Podemos enfrentar esse problema com frequência, em que o usuário abre muitas conexões e executa a consulta em todas elas. isto
aumenta o número total de conexões e pode causar problemas de desempenho. Não queremos limitar o
número total de conexões. Exigimos que um usuário não tenha permissão para abrir mais de duas conexões
simultaneamente.

DMV do SQL Server sys.dm_exec_connections possui uma coluna para fornecer o nome de logon original.

Saída DMV

Podemos usar ORIGINAL_LOGIN () para verificar o logon no qual o usuário está conectado ao SQL Server.

Login de origem

Na definição de gatilho abaixo, verificamos o número máximo de conexões conectadas para o processo do usuário. Podemos
observe que cada usuário pode ter no máximo duas conexões simultâneas usando esse gatilho de logon.

Recebemos a mesma mensagem de erro quando um usuário tenta fazer uma terceira conexão.

Erro devido ao gatilho

Podemos verificar aqui que cada usuário pode ter duas conexões simultâneas.

Saída de dados

Você também pode encontrar uma entrada nos logs do SQL Server que mostra que você não tem permissão para ter mais de dois
conexões. É a mensagem personalizada que especificamos no gatilho de logon.

Logs do SQL Server

Permitir que os usuários com o aplicativo cliente específico se conectem às conexões do SQL Server usando gatilhos de logon no SQL Server

Suponha que não queremos que os usuários se conectem ao SQL Server a partir de outro SSMS. Podemos usar a função APP_NAME () para
verifique o aplicativo cliente do usuário e defina a lógica no gatilho de logon para permitir a conexão ou não.

Aqui, podemos ver que a conexão não é permitida do SQLCMD, porque só permitimos que o SSMS fizesse uma conexão.

Conexões do SQL Server usando gatilhos de logon no SQL Server

Você pode ver a mensagem personalizada no log de erros. Esta mensagem personalizada ajuda a identificar os problemas de conexão
rapidamente.

Exibir logs de erro

Desabilitar a conexão do usuário em um período específico usando gatilhos de logon no SQL Server

Suponha que não desejemos que nenhum usuário se conecte ao banco de dados SQL durante a noite. Podemos estar fazendo banco de dados
tarefas de manutenção nessas horas. Os gatilhos de logon podem ajudar a cumprir esse requisito.

Suponha que eu tenha um usuário Demo com [dbcreator] permissões no nível do servidor.

Para fins de demonstração, digamos que esse usuário trabalhe após o horário comercial. Queremos que o usuário efetue login entre 6
PM e 6 da manhã. Fora desse horário, o SQL Server deve restringir o acesso a esse usuário específico.

No script de gatilho abaixo, usamos a função ORIGINIAL_LOGIN () para validar o usuário e a função SQL DATEPART para
verifique as horas de login do usuário. Se ambas as condições forem atendidas, o usuário [demo] pode fazer login no SQL Server caso contrário, ele recebe um erro
mensagem.

Não permitir conexão do usuário

No código de acionador acima, não especificamos uma instrução PRINT para registrar uma mensagem personalizada no log de erros. Você pode ver
ele registra uma mensagem genérica no log de erros, portanto, recomendo usar uma mensagem personalizada para fornecer detalhes específicos.

Exibir logs de erro SQL Server

Auditoria de logon usando gatilhos de logon

Às vezes, é necessário auditar os eventos de logon das instâncias do SQL Server. Temos a opção de fazer auditoria de login no SQL
Propriedades do servidor.

Auditoria de login

Depois de habilitar a auditoria de logon, ele registra entradas no log de erros do SQL Server para logon com falha e êxito, com base em
a opção configurada.

Mensagens de logon

Pode preencher os logs de erros rapidamente e você pode perder a verificação de eventos úteis de login. Você também pode usar o logon
dispara no SQL Server para esse propósito de auditoria.

Para esse fim, crie uma tabela SQL para armazenar eventos de auditoria.

Em seguida, crie o gatilho de logon com o seguinte script.

No código acima, usamos EVENTDATA () para capturar os nomes de host. É um documento XML em um gatilho DDL para capturar
detalhes úteis do esquema. Você pode consultar os documentos da Microsoft para obter mais detalhes sobre eventdata ().

Agora, consulte a tabela Audit_Logins e você pode ver os registros de eventos de logon sendo armazenados.

Dados de auditoria

Desabilitar todos os gatilhos de logon no SQL Server

Você pode planejar remover os gatilhos de logon ou desativá-los, se não for necessário. Podemos usar o seguinte script para
desative todos os gatilhos de logon na instância SQL.

Pontos úteis sobre gatilhos de logon no SQL Server

  • Use os gatilhos de logon com a devida inteligência. Você pode bloquear todas as conexões SQL se elas não estiverem configuradas corretamente

  • Sempre realize a demonstração em seu ambiente de teste

  • Não crie vários gatilhos de logon e configure apenas conforme sua exigência

  • Sempre devemos estar prontos com um plano de reversão, caso você bloqueie acidentalmente todas as conexões SQL

Conclusão

Neste artigo, exploramos os gatilhos de logon úteis no SQL Server. Você pode usá-lo para restringir logons do SQL conforme seus requisitos. Também pode ser útil para fins de auditoria. Você deve explorar esses gatilhos e implementá-los com testes adequados, se necessário.

Rajendra Gupta
Últimas mensagens de Rajendra Gupta (ver todos)

Leia Também  Introdução ao SQLAlchemy no Pandas Dataframe