Warning: preg_match(): Unknown modifier 'b' in /mnt/datadisk/www/src/Controllers/PostController.php on line 206 Auditoría en SQL Server (Server Audit) — Dirceu ResendeSaltar al contenido
En esta publicación, demostraré cómo crear auditorías de servidor (Server Audit) en SQL Server. Esta característica es muy interesante para auditar acciones DDL y/o DML (Especificación de auditoría de base de datos) realizadas por un usuario o aplicación o incluso la propia instancia (Especificación de auditoría del servidor).
Creación de una auditoría a través de la interfaz (GUI)
Abra SQL Server Management Studio, seleccione la categoría Seguridad > Auditorías > Haga clic derecho > Seleccione la opción “Nueva auditoría…”
SQL Server - Auditoría del servidor 2
Cuando configura la auditoría, los datos registrados se pueden guardar de 3 maneras:
Archivo: Se genera un archivo físico en el disco que contiene los datos recopilados por la auditoría.
Registro de seguridad: los datos recopilados por la auditoría se almacenan en el registro de seguridad del servidor.
Registro de aplicación: los datos recopilados por la auditoría se almacenan en el registro de aplicación del servidor.
En esta publicación, elegiré escribir en un archivo físico en el disco. Al seleccionar esta opción, también puedes especificar el nombre del archivo donde se guardará, el tamaño máximo y elegir si deseas reservar previamente este espacio máximo en el disco (es decir, si eliges el tamaño máximo de 10 GB, SQL Server ya creará este archivo con los 10 GB de espacio asignados)
SQL Server - Crear auditoría
Después de crear la Auditoría, debe habilitarla haciendo clic derecho sobre ella en el Explorador de objetos y luego seleccionando la opción "Habilitar auditoría"
SQL Server: habilitar auditoría
SQL Server: habilitar auditoría 2
Creación de una auditoría a nivel de servidor (Especificación de auditoría del servidor)
Ahora que se ha creado el objeto de auditoría, podemos crear el objeto de definición de auditoría (Especificación de auditoría del servidor).
Seleccione la instancia y seleccione la categoría Seguridad > Especificación de auditoría del servidor > Haga clic derecho > Seleccione la opción “Nueva especificación de auditoría del servidor”
SQL Server - Auditoría del servidor
En esta pantalla, puede definir lo que desea monitorear, como se muestra en la siguiente captura de pantalla:
SQL Server: especificación de auditoría del servidor
Ver todos los tipos de auditorías a nivel de servidor
Este evento se genera cada vez que se crea, modifica o elimina una auditoría. Este evento se genera cada vez que se crea, modifica o elimina cualquier especificación de auditoría. Cualquier cambio en una auditoría se audita en esa auditoría. equivalente a Auditoría de cambio de clase de evento de auditoría.
Este evento se genera cuando se crea, modifica o elimina una base de datos. Este evento se genera cada vez que se crea, modifica o elimina una base de datos. equivalente a Clase de evento de gestión de bases de datos de auditoría.
Este evento se genera cuando se ejecuta una instrucción CREATE, ALTER o DROP en objetos de base de datos, como esquemas. Se genera cada vez que se crea, cambia o elimina un objeto de base de datos.
Observación
Esto podría generar volúmenes muy grandes de registros de auditoría.
Este evento se genera cuando hay un cambio de propietario para objetos dentro del alcance de la base de datos. Se genera para cualquier cambio de propiedad de objeto en cualquier base de datos del servidor. equivalente a Auditar clase de evento de toma de posesión de objeto de base de datos.
DATABASE_OBJECT_PERMISSION_CHANGE_GROUP
Este evento se genera cuando se emite GRANT, REVOKE o DENY para objetos de base de datos, como ensamblados y esquemas. Se genera para cualquier cambio de permiso de objeto en cualquier base de datos del servidor. equivalente a Auditar clase de evento GDR de objeto de base de datos.
GRUPO_OPERACIÓN_BASE_DATOS
Este evento se genera cuando se producen operaciones en una base de datos, como un punto de control o una notificación de consulta de suscripción. Este evento se genera en cualquier operación de base de datos en cualquier base de datos. equivalente a Clase de evento de operación de base de datos de auditoría.
DATABASE_OWNERSHIP_CHANGE_GROUP
Este evento se genera cuando se utiliza la instrucción ALTER AUTHORIZATION para cambiar el propietario de una base de datos y se verifican los permisos necesarios para hacerlo. Se genera para cualquier cambio de propiedad de la base de datos en cualquier base de datos del servidor. equivalente a Clase de evento Auditar cambio de propietario de base de datos.
DATABASE_PERMISSION_CHANGE_GROUP
Este evento se genera cada vez que se emite GRANT, REVOKE o DENY para un permiso de declaración por parte de cualquier entidad en SQL Server (esto se aplica a eventos solo de base de datos, como otorgar permisos en una base de datos, por ejemplo).
Este evento se genera cuando se crean, modifican o eliminan entidades, como usuarios, de una base de datos. equivalente a Clase de evento de gestión principal de base de datos de auditoría. (También es equivalente a la clase de evento Audit Add DB Principal, que ocurre en los procedimientos almacenados sp_grantdbaccess, sp_revokedbaccess, sp_addPrincipal y sp_dropPrincipal en desuso).
Este evento se genera cada vez que se agrega o elimina una función de base de datos mediante los procedimientos almacenados sp_addrole y sp_droprole. Este evento se genera cada vez que se crea, modifica o elimina una entidad de base de datos de cualquier base de datos. equivalente a Auditoría de clase de evento Agregar rol.
BASE_DATA_PRINCIPAL_IMPERSONATION_GROUP
Este evento se genera cuando hay una operación de suplantación en el ámbito de la base de datos, como EXECUTE AS o SETPRINCIPAL. Se genera para representaciones realizadas en cualquier base de datos. equivalente a Clase de evento de suplantación principal de base de datos de auditoría.
DATABASE_ROLE_MEMBER_CHANGE_GROUP
Este evento se genera cada vez que se agrega o elimina un inicio de sesión de una función de base de datos. Esta clase de evento se genera para los procedimientos almacenados sp_addrolemember, sp_changegroup y sp_droprolemember. Este evento se genera ante cualquier cambio en el miembro de la función de la base de datos en cualquier base de datos. equivalente a Auditoría Agregar miembro a la clase de evento de rol de base de datos.
DBCC_GROUP
Este evento se genera cada vez que una entidad emite un comando DBCC. equivalente a Auditar clase de evento DBCC.
FAILED_LOGIN_GROUP
Indica que una entidad intentó iniciar sesión en SQL Server y falló. Los eventos de esta clase se generan mediante conexiones nuevas o mediante conexiones reutilizadas de un grupo de conexiones. equivalente a Clase de evento fallido de inicio de sesión de auditoría.
Este evento se genera cada vez que la contraseña de inicio de sesión se cambia mediante la instrucción ALTER LOGIN o el procedimiento almacenado sp_password. equivalente a Evento Clase Auditoría Iniciar sesión Cambiar contraseña.
LOGUT_GROUP
Indica que una entidad ha cerrado sesión en SQL Server. Los eventos de esta clase se generan mediante conexiones nuevas o mediante conexiones reutilizadas de un grupo de conexiones. equivalente a Clase de evento de cierre de sesión de auditoría.
Este evento se genera cuando se verifican los permisos para cambiar el propietario del objeto de esquema (como una tabla, procedimiento o función). Esto ocurre cuando se utiliza la declaración ALTER AUTHORIZATION para asignar un propietario a un objeto. Este evento se genera para cualquier cambio de propiedad de esquema en cualquier base de datos del servidor. equivalente a Clase de evento de toma de posesión del objeto de esquema de auditoría.
Este evento se genera cuando se utilizan operaciones de auditoría de seguridad, como cambiar configuraciones, recursos, acceso externo o autorización. equivalente a Clase de evento de operación del servidor de auditoría.
Este evento se genera cuando una entidad emite los procedimientos almacenados sp_defaultdb o sp_defaultlanguage o instrucciones ALTER LOGIN. equivalente a Auditar la clase de evento Addlogin.
Este evento se genera cada vez que se agrega o elimina un inicio de sesión de una función de servidor fija. Este evento se genera para los procedimientos almacenados sp_addsrvrolemember y sp_dropsrvrolemember. equivalente a Auditoría Agregar inicio de sesión a la clase de evento de rol del servidor.
Indica que la entidad ha iniciado sesión correctamente en SQL Server. Los eventos de esta clase se generan mediante conexiones nuevas o mediante conexiones reutilizadas de un grupo de conexiones. equivalente a Clase de evento de inicio de sesión de auditoría.
Después de elegir los tipos de auditoría (en este ejemplo, quiero auditar la creación/eliminación de bases de datos), haga clic en Aceptar y habilite la especificación de auditoría:
SQL Server: habilitación de la especificación de auditoría del servidor
Crear una auditoría a nivel de base de datos (Especificación de auditoría de base de datos)
Una característica muy interesante que puede, en varias ocasiones, reemplazar el uso de activadores, es la creación de rutinas de auditoría a nivel de base de datos. En este ejemplo, crearé una auditoría para registrar los cambios realizados en una tabla específica.
Para crear una auditoría a nivel de base de datos, seleccione la instancia y luego la base de datos que será auditada, expanda la categoría Seguridad, seleccione la subcategoría Especificaciones de auditoría de base de datos, haga clic derecho y seleccione la opción “Nueva especificación de auditoría de base de datos”
SQL Server - Auditoría de base de datos
En esta pantalla podremos definir qué queremos auditar en esta base de datos.
SQL Server: nueva auditoría de base de datos
SQL Server: especificación de auditoría de base de datos
En la configuración anterior, la configuré para auditar las siguientes operaciones:
– BORRADOS realizados en la base de datos de Pruebas por el usuario “Usuario_Teste”
– INSERTs realizados en la tabla Clientes realizados por cualquier usuario.
– INSERTOS en cualquier objeto de esquema dbo, realizado por cualquier usuario.
Después de completar la configuración, recuerde activar la auditoría haciendo clic derecho sobre ella y seleccionando la opción “Habilitar especificación de auditoría de base de datos” y ¡su auditoría estará activada!
Ver los datos recopilados
Para ver los datos recopilados por la Auditoría, simplemente haga clic derecho en la Auditoría (no en la Especificación) y seleccione la opción "Ver registros de auditoría"
SQL Server: ver registros de auditoría
SQL Server: especificación de auditoría de base de datos Ver registro
Crear una auditoría a través de la línea de comando
Después de toda esta publicación que explica cómo crear auditorías de servidores y bases de datos usando las pantallas de SQL Server Management Studio, explicaré rápidamente cómo crear estas mismas auditorías a través de la línea de comandos, que es una forma mucho más práctica de replicar en múltiples servidores y mejora el conocimiento del DBA.
Con los comandos de abajo ya creo la auditoría (y la activo), la auditoría a nivel de servidor y base de datos (ambas ya activadas también).
USE [master]
GO
CREATE SERVER AUDIT [Auditoria_Arquivo]
TO FILE
( FILEPATH = N'C:\'
,MAXSIZE = 100 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
)
GO
ALTER SERVER AUDIT Auditoria_Arquivo WITH (STATE = ON)
CREATE SERVER AUDIT SPECIFICATION [Criação de Banco de Dados]
FOR SERVER AUDIT [Auditoria_Arquivo]
ADD (DATABASE_CHANGE_GROUP)
WITH (STATE = ON)
GO
USE [Testes]
GO
CREATE DATABASE AUDIT SPECIFICATION [Audita_DML]
FOR SERVER AUDIT [Auditoria_Arquivo]
ADD (DELETE ON DATABASE::[Testes] BY [Usuario_Teste]),
ADD (INSERT ON SCHEMA::[dbo] BY [public]),
ADD (INSERT ON OBJECT::[dbo].[Clientes] BY [public])
WITH (STATE = ON)
GO
Ver los registros
Usando consultas simples, podemos leer fácilmente los datos monitoreados y trabajar con esta información:
-- Retorna as informações de um arquivo específico
SELECT *
FROM Sys.fn_get_audit_file('C:\Auditoria_Arquivo_75804C51-45C9-423C-B570-B24202AD13D3_0_130899437085570000.sqlaudit',default,default)
-- Retorna as informações de todos os arquivos
SELECT event_time,action_id,server_principal_name,statement,*
FROM Sys.fn_get_audit_file('C:\*.sqlaudit',default,default)
Y los datos mostrados se ven así: SQL Server: ver registros de auditoría Consulta SQL
¡Gracias y hasta la próxima!
Dirceu Resende
Arquitecto de Bases de Datos y BI · Microsoft MVP · MCSE, MCSA, MCT, MTA, MCP.
Comentários (0)
Carregando comentários…