Hola, chicos,
¿Estás bien?

Después de MUCHO tiempo sin publicar ningún artículo técnico, estoy empezando a tener algo de tiempo nuevamente para traerles más guiones y artículos interesantes que me gustaría compartir con ustedes.

En esta publicación, demostraré cómo crear un activador para auditar eventos de concesión y eliminación de permisos (GRANT y REVOKE) en objetos, bases de datos, cambios de roles de usuario (roles de base de datos y roles de servidor), además de permisos directos en bases de datos. Esta necesidad surgió por una demanda que me llegó, ya que algunos usuarios estaban perdiendo el acceso a una determinada instancia y no había control para identificar quién estaba eliminando y quién otorgaba ese acceso.

Script de creación de tabla de historial de permisos

Con el siguiente script, puede crear la tabla que almacenará el historial completo de permisos otorgados y eliminados en la instancia.

USE [Auditoria]
GO

IF (OBJECT_ID('dbo.Trace_Alteracao_Privilegios') IS NULL)
BEGIN
    
    CREATE TABLE dbo.Trace_Alteracao_Privilegios (
        Id_Evento BIGINT IDENTITY(1, 1) PRIMARY KEY,
        Ds_Evento VARCHAR(255),
        Dt_Evento DATETIME,
        Ds_Database VARCHAR(255),
        Ds_Schema VARCHAR(255),
        Ds_Objeto VARCHAR(255),
        Ds_Tipo_Objeto VARCHAR(255),
        Ds_Usuario VARCHAR(255),
        Ds_Comando VARCHAR(MAX),
        Evento XML
    );

    GRANT INSERT ON dbo.Trace_Alteracao_Privilegios TO [public];

END

Script de creación de desencadenadores

Con el siguiente script, creará el activador que recopila y audita los permisos.

USE [master]
GO

IF ((SELECT COUNT(*) FROM sys.server_triggers WHERE name = 'trgAudit_Privileges') > 0) DROP TRIGGER [trgAudit_Privileges] ON ALL SERVER
GO

CREATE TRIGGER trgAudit_Privileges
ON ALL SERVER
FOR DDL_SERVER_SECURITY_EVENTS, DDL_DATABASE_SECURITY_EVENTS
AS
BEGIN
    
    
    DECLARE 
        @Ds_Evento NVARCHAR(255),
        @Ds_Schema NVARCHAR(255),
        @Ds_Database VARCHAR(255),
        @Ds_Objeto VARCHAR(255),
        @Ds_TipoObjeto VARCHAR(255),
        @Evento XML,
        @Ds_Comando VARCHAR(MAX);


    SELECT
        @Ds_Evento = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(max)'),
        @Ds_Schema = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(max)'),
        @Ds_Objeto = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(max)'),
        @Ds_TipoObjeto = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(max)'),
        @Ds_Database = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(max)'),
        @Ds_Comando = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)'),
        @Evento = EVENTDATA()


    INSERT INTO Auditoria.dbo.Trace_Alteracao_Privilegios
    SELECT
        @Ds_Evento,
        GETDATE() AS Dt_Evento,
        @Ds_Database,
        @Ds_Schema,
        @Ds_Objeto,
        @Ds_TipoObjeto,
        SUSER_SNAME(),
        @Ds_Comando,
        @Evento


END

Resultados

Ahora demostraré algunos ejemplos de lo que este disparador puede auditar.

¡Eso es todo, amigos!
Un abrazo para ti y nos vemos en el próximo post.