¡Hola, chicos!
En este artículo, demostraré cómo utilizar la auditoría para mapear los permisos reales necesarios de un usuario, identificando todo lo que ese usuario realmente hizo en la base de datos durante el tiempo observado, para generar un script que otorgue solo los permisos que realmente necesita, eliminando así la necesidad de tener usuarios de integración y otros usuarios, además del principal de la aplicación, con permisos sobre todos los objetos en la base de datos, como db_owner, db_datareader y/o db_datawriter, por ejemplo.

Durante la atención al cliente, es muy común ver situaciones en las que los usuarios de integraciones entre sistemas o procesos se crean en la base de datos exclusivamente para este proceso (buena práctica), pero aunque necesitan acceder/cambiar algunas tablas, terminan recibiendo permisos sobre todos los objetos de esa base de datos. Más aún en los usuarios de integración, que no siempre son desarrollados por el equipo interno y en muchos casos, el sistema de estos usuarios de integración ni siquiera está bajo la responsabilidad del equipo de TI local y se puede acceder a él vía internet, por ejemplo, una brecha en este sistema externo podría terminar siendo catastrófica para tu empresa.

Para mitigar este problema, le demostraré cómo registrar todo lo que hacen estos usuarios en la base de datos y permitirle aplicar solo los permisos necesarios. Para esta necesidad, utilizaré la función Server Audit, disponible desde SQL Server 2008 en la versión Enterprise y desde SQL Server 2012 en la versión Standard. También usaré la función Auditoría de base de datos, disponible en la versión estándar solo desde SQL Server 2016 SP1 en adelante.

En resumen, para poder ejecutar los scripts de este artículo, deberá cumplir una de las siguientes condiciones:

  • SQL Server 2012 o superior, Enterprise, Datacenter (2008) o edición Developer
  • SQL Server 2016 SP1 o superior (cualquier edición, hasta Express)

Creando la tabla para almacenar el historial de acceso

Después de la introducción anterior, comencemos a monitorear el acceso a la tabla. En primer lugar crearé la tabla que almacenará el historial de accesos recopilados por la auditoría.

CREATE TABLE [dbo].[Auditoria_Acesso]
(
    [Id_Auditoria] [bigint] NOT NULL IDENTITY(1, 1),
    [Dt_Auditoria] [datetime] NOT NULL,
    [Cd_Acao] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
    [Ds_Maquina] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
    [Ds_Usuario] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
    [Ds_Database] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
    [Ds_Schema] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
    [Ds_Objeto] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
    [Ds_Query] [varchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
    [Fl_Sucesso] [bit] NOT NULL,
    [Ds_IP] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
    [Ds_Programa] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
    [Qt_Duracao] [bigint] NOT NULL,
    [Qt_Linhas_Retornadas] [bigint] NOT NULL,
    [Qt_Linhas_Alteradas] [bigint] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
WITH
(
DATA_COMPRESSION = PAGE
)
GO
ALTER TABLE [dbo].[Auditoria_Acesso] ADD CONSTRAINT [PK__Auditori__E9F1DAD4EE3743FE] PRIMARY KEY CLUSTERED ([Id_Auditoria]) WITH (DATA_COMPRESSION = PAGE) ON [PRIMARY]
GO

Creando la Auditoría del Servidor filtrando usuarios

El segundo paso para poder auditar los accesos realizados por ciertos usuarios, crearemos una auditoría del servidor en la instancia que se utilizará en el siguiente tema para capturar estos eventos. Vale recordar que la función Server Audit está disponible desde SQL Server 2008 en la versión Enterprise y desde SQL Server 2012 en la versión Standard. Además, solo es posible utilizar filtros en Server Audit a partir de SQL Server 2012.

En el siguiente ejemplo, defino que la auditoría creará hasta 16 archivos de 10 MB cada uno. A medida que se vayan completando los ficheros se irán creando nuevos ficheros con los datos recogidos hasta alcanzar el límite establecido (16 ficheros). Cuando se han creado todos los archivos y están completamente poblados, los archivos más antiguos comienzan a sobrescribirse con la nueva información. Por este motivo, es importante tener una rutina para recopilar datos de archivos y almacenarlos en tablas.

Además, a partir de SQL Server 2012, podemos aplicar varios filtros para afinar nuestras búsquedas, como capturar datos de usuarios que terminan en '%User' o que comienzan con LS_% y también ignorar Intellisense desde las herramientas SSMS y RedGate, además de no recolectar datos de usuarios que están en el dominio “MEUDOMINIO” y tampoco recolectar datos del usuario “usrDirceuResende”.

En esta etapa podemos filtrar los nombres de usuario, software, IP o nombres de host que se utilizarán para refinar las búsquedas y devolver solo los datos deseados.

USE [master]
GO

IF ((SELECT COUNT(*) FROM sys.server_audits WHERE [name] = 'Auditoria_Acessos') > 0)
BEGIN
    ALTER SERVER AUDIT [Auditoria_Acessos] WITH (STATE = OFF);
    DROP SERVER AUDIT [Auditoria_Acessos]
END


CREATE SERVER AUDIT [Auditoria_Acessos]
TO FILE
(	
    FILEPATH = N'C:\Audit\',
    MAXSIZE = 10 MB,
    MAX_ROLLOVER_FILES = 16,
    RESERVE_DISK_SPACE = OFF
)
WITH
(	
    QUEUE_DELAY = 1000,
    ON_FAILURE = CONTINUE,
    AUDIT_GUID = '0b5ad307-ee47-43db-a169-9af67cb661f9'
)
WHERE (([server_principal_name] LIKE '%User' OR [server_principal_name] LIKE 'LS_%') AND [application_name]<>'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense' AND NOT [application_name] LIKE 'Red Gate Software%')
GO


ALTER SERVER AUDIT [Auditoria_Acessos] WITH (STATE = ON)
GO

Creación de la auditoría de la base de datos capturando el acceso

En el tercer paso para configurar nuestro monitoreo, ahora crearemos una auditoría de base de datos para cada base de datos cuyos permisos desee monitorear. En el siguiente ejemplo, monitorearé los eventos INSERT/DELETE/UPDATE/SELECT/EXECUTE realizados por cualquier usuario de la base de datos (pública).

Vale recordar que, si bien Database Audit parece capturar eventos de todos los usuarios, en Server Audit ya limitamos los usuarios que se deben devolver y este filtro también se respeta en Database Audit.

El siguiente código revisará todas las bases de datos que no están en la lista de excepciones (master, tempdb, msdb y model) y creará la auditoría de la base de datos.

DECLARE @Query VARCHAR(MAX)
SET @Query = '

IF (''?'' NOT IN (''master'', ''tempdb'', ''model'', ''msdb''))
BEGIN

    USE [?];

    IF ((SELECT COUNT(*) FROM sys.database_audit_specifications WHERE [name] = ''Auditoria_Acessos'') > 0)
    BEGIN

        ALTER DATABASE AUDIT SPECIFICATION [Auditoria_Acessos] WITH (STATE = OFF);
        DROP DATABASE AUDIT SPECIFICATION [Auditoria_Acessos];

    END

    CREATE DATABASE AUDIT SPECIFICATION [Auditoria_Acessos]
    FOR SERVER AUDIT [Auditoria_Acessos]
    ADD (DELETE ON DATABASE::[?] BY [public]),
    ADD (EXECUTE ON DATABASE::[?] BY [public]),
    ADD (INSERT ON DATABASE::[?] BY [public]),
    ADD (SELECT ON DATABASE::[?] BY [public]),
    ADD (UPDATE ON DATABASE::[?] BY [public])
    WITH (STATE = ON);
    
END'


EXEC sys.sp_MSforeachdb @Query

Crear el procedimiento almacenado para almacenar los datos recopilados

En el cuarto paso de la rutina, crearemos el Procedimiento almacenado responsable de leer los datos de los archivos de auditoría y escribirlos en la tabla creada en el paso 1. Recuerde crear un trabajo en el Agente SQL para ejecutar el siguiente procedimiento cada X minutos.

IF (OBJECT_ID('dbo.stpAuditoria_Acessos_Carrega_Dados') IS NULL) EXEC('CREATE PROCEDURE dbo.stpAuditoria_Acessos_Carrega_Dados AS SELECT 1')
GO

ALTER PROCEDURE dbo.stpAuditoria_Acessos_Carrega_Dados
AS
BEGIN

    DECLARE @TimeZone INT = DATEDIFF(HOUR, GETUTCDATE(), GETDATE())
    DECLARE @Dt_Max DATETIME = DATEADD(SECOND, 1, ISNULL((SELECT MAX(Dt_Auditoria) FROM dirceuresende..Auditoria_Acesso), '1900-01-01'))

    INSERT INTO dirceuresende.dbo.Auditoria_Acesso
    (
        Dt_Auditoria,
        Cd_Acao,
        Ds_Maquina,
        Ds_Usuario,
        Ds_Database,
        Ds_Schema,
        Ds_Objeto,
        Ds_Query
        Fl_Sucesso,
        Ds_IP,
        Ds_Programa,
        Qt_Duracao,
        Qt_Linhas_Retornadas,
        Qt_Linhas_Alteradas
    )
    SELECT DISTINCT
        DATEADD(HOUR, @TimeZone, event_time) AS event_time,
        action_id,
        server_instance_name,
        server_principal_name,
        [database_name],
        [schema_name],
        [object_name],
        [statement],
        succeeded,
        client_ip,
        application_name,
        duration_milliseconds,
        response_rows,
        affected_rows
    FROM 
        sys.fn_get_audit_file('C:\Audit\*.sqlaudit', DEFAULT, DEFAULT)
    WHERE 
        DATEADD(HOUR, @TimeZone, event_time) >= @Dt_Max

END

Consultar los datos recopilados.

Y finalmente, en el 5º paso de esta rutina, leeremos los datos recopilados y almacenados en la tabla dirceuresende.dbo.Auditoria_Acesso e identificaremos los accesos que realiza cada usuario en cada base de datos. Esto le ayudará a identificar a qué acceden actualmente estos usuarios en la base de datos. Se acabó la excusa para que todos los usuarios sean db_owner de las bases de datos.

Si también desea generar scripts para otorgar permisos actuales, puede usar este script:

SELECT DISTINCT 
    Ds_Usuario,
    Ds_Database, 
    Cd_Acao, 
    Ds_Objeto,
    'USE [' + Ds_Database + ']; GRANT ' + (CASE Cd_Acao
        WHEN 'UP' THEN 'UPDATE'
        WHEN 'IN' THEN 'INSERT'
        WHEN 'DL' THEN 'DELETE'
        WHEN 'SL' THEN 'SELECT'
        WHEN 'EX' THEN 'EXECUTE'
    END) + ' ON [' + Ds_Schema + '].[' + Ds_Objeto + '] TO [' + Ds_Usuario + '];' AS Comando 
FROM 
    dirceuresende..Auditoria_Acesso 
WHERE 
    Cd_Acao <> 'UNDO'
ORDER BY
    Ds_Usuario,
    Ds_Database,
    Ds_Objeto

Resultado:

¡Listo! Rutina de auditoría de acceso implementada. El siguiente paso ahora es utilizar los datos recopilados para otorgar los permisos que los usuarios realmente usaron durante el tiempo que estuvo habilitada la auditoría y eliminar los permisos elevados de estos usuarios, como sysadmin y db_owner.

Observación: Un punto muy importante que se debe analizar es la cantidad de registros que contendrá esta tabla. En entornos que tienen muchos usuarios simultáneos, esta recopilación de datos de acceso puede terminar generando un volumen muy grande de datos y consumiendo mucho espacio en disco. Por eso, siempre es importante monitorear el volumen de datos y crear una rutina para limpiar la tabla, manteniendo solo X días de historial.

Además, ni siquiera hace falta decir que recomiendo que PRUEBES mucho antes de aplicarlo a producción, ¿verdad? Dependiendo del número de transacciones por segundo, esto puede incluso generar un impacto en el rendimiento al activar esta característica (aunque ya lo he probado en entornos muy críticos y no he notado ningún cambio).

Bueno chicos, espero que esto les haya ayudado a encontrar una manera de validar todos los objetos a los que acceden ciertos usuarios, ayudándolos a mapear los permisos necesarios.
¡Un fuerte abrazo y hasta luego!