Trigger d’accesso

  • Articolo

Si applica a:SQL ServerIstanza gestita di SQL di Azure

I trigger d’accesso attivano le stored procedure in risposta a un evento LOGON. generato quando viene stabilita una sessione utente a un'istanza di SQL Server. I trigger LOGON vengono attivati dopo il completamento della fase di autenticazione della procedura di accesso, ma prima che la sessione utente venga stabilita, Pertanto, tutti i messaggi generati all'interno del trigger che verrebbero normalmente visualizzati dall'utente, come i messaggi di errore e messaggi dell'istruzione PRINT, vengono invece indirizzati al log dell’errore dell’SQL Server. I trigger LOGON non vengono attivati in caso di esito negativo dell'autenticazione.

È possibile utilizzare i trigger LOGON per controllare e gestire le sessioni server, ad esempio tenendo traccia delle attività di accesso, limitando gli accessi a SQL Server o limitando il numero di sessioni per uno specifico account di accesso. Nel codice seguente, ad esempio, il trigger d’accesso nega i tentativi d’accesso a SQL Server eseguiti dall'account login_test se ci sono già tre sessioni utente create da quell’account.

USE master;
GO

CREATE LOGIN login_test
WITH PASSWORD = N'3KHJ6dhx(0xVYsdf' MUST_CHANGE,
CHECK_EXPIRATION = ON;
GO

GRANT VIEW SERVER STATE TO login_test;
GO

CREATE TRIGGER connection_limit_trigger ON ALL SERVER
WITH EXECUTE AS N'login_test'
FOR LOGON AS BEGIN
    IF ORIGINAL_LOGIN() = N'login_test'
    AND (
        SELECT COUNT(*)
        FROM sys.dm_exec_sessions
        WHERE is_user_process = 1
            AND original_login_name = N'login_test') > 3
    ROLLBACK;
END;

L'evento LOGON corrisponde all'evento Analisi SQL AUDIT_LOGIN, che può essere usato nelle notifiche degli eventi. La principale differenza tra i trigger e le notifiche di eventi è il fatto che i trigger vengono attivati in modo sincrono rispetto agli eventi, mentre le notifiche di eventi sono asincrone. Ciò significa, ad esempio, che se si desidera arrestare l'attivazione di una sessione è necessario utilizzare un trigger LOGON. Non è possibile utilizzare a tale scopo una notifica degli eventi per un evento AUDIT_LOGIN.

Specificare il primo e l’ultimo trigger

È possibile definire più trigger per l'evento LOGON. Uno qualsiasi di questi trigger può essere designato come primo o ultimo trigger da attivare per un evento usando la stored procedure di sistema sp_settriggerorder . SQL Server non garantisce l'ordine di esecuzione dei trigger rimanenti.

Gestire le transazioni

Prima che SQL Server attivi un trigger LOGON, SQL Server crea una transazione implicita indipendente da qualsiasi altra transazione utente. Per questo motivo, quando viene attivato il primo trigger LOGON il numero di transazioni è 1 e al termine dell'esecuzione di tutti i trigger LOGON viene eseguito il commit della transazione. Come per tutti gli altri tipi di trigger, SQL Server restituisce un errore se l'esecuzione di un trigger LOGON viene completata con un numero di transazioni pari a 0. L'istruzione ROLLBACK TRANSACTION reimposta il numero di transazioni su 0, anche se viene eseguita all'interno di una transazione annidata. COMMIT TRANSACTION potrebbe ridurre il numero di transazioni a 0. Pertanto, si consiglia di non eseguire istruzioni COMMIT TRANSACTION all'interno di trigger d’accesso.

Tenere presenti le considerazioni di seguito per l'utilizzo di un'istruzione ROLLBACK TRANSACTION all'interno di trigger d’accesso:

  • Viene eseguito il rollback di qualsiasi modifica dei dati eseguita fino al punto di ROLLBACK TRANSACTION. Tali modifiche includono modifiche apportate dal trigger corrente e da trigger precedenti eseguite sullo stesso evento. Gli eventuali trigger rimanenti per l'evento specifico non vengono eseguiti.

  • Il trigger corrente continua l'esecuzione delle istruzioni rimanenti successive all'istruzione ROLLBACK. Se una di queste istruzioni modifica i dati, non viene eseguito il rollback delle modifiche eseguite.

Non viene stabilita una sessione utente se si verifica una delle seguenti condizioni durante l'esecuzione di un trigger di un evento LOGON:

  • Viene eseguito il rollback della transazione implicita originale o la transazione ha esito negativo.
  • Viene generato un errore con gravità maggiore di 20 all'interno del corpo del trigger.

Disabilitazione di un trigger d’accesso

Un trigger di accesso può impedire le connessioni al motore di database per tutti gli utenti, inclusi i membri del ruolo predefinito del server sysadmin. Quando un trigger di accesso impedisce le connessioni, i membri del ruolo predefinito del server sysadmin possono connettersi usando la connessione amministrativa dedicata o avviando il motore di database in modalità di configurazione minima (- f). Per altre informazioni, vedere Opzioni di avvio del servizio del motore di database.

Contenuto correlato