SQL Server Audit (motor de base de datos)
Laauditoría de una instancia de Motor de base de datos de SQL Server o de una base de datos
individual implica el seguimiento y registro de los eventos que se producen en Motor de base de
datos. La auditoría deSQL Server permite crear auditorías de servidor, que pueden contener
especificaciones de auditoría de servidor para los eventos de servidor, y especificaciones de
auditoría de base de datos para los eventos de base de datos. Los eventos auditados se pueden
escribir en los registros de eventos o en los archivos de auditoría.
Hay varios niveles de auditoría disponibles para SQL Server, dependiendo de los requisitos
gubernamentales o normativos de cada instalación. SQL Server Audit proporciona las
herramientas y los procesos necesarios para habilitar, almacenar y ver auditorías en varios
objetos de servidor y de base de datos.
Puede registrar grupos de acciones de auditoría en el servidor por instancia, así como
grupos de acciones o acciones de auditoría en la base de datos por base de datos. El evento
de auditoría se producirá cada vez que se encuentre la acción auditable.
Todas las ediciones de SQL Server admiten auditorías en el nivel de servidor. Todas las
ediciones admiten auditorías de nivel de base de datos a partir de SQL Server 2016 (13.x)
SP1. Antes de eso, las auditorías de nivel de base de datos se limitaban a las ediciones
Enterprise, Developer y Evaluation. Para obtener más información, vea Características
compatibles con las ediciones de SQL Server 2016.
Componentes de SQL Server Audit
Una auditoría es la combinación de varios elementos en un único paquete para un grupo
específico de acciones de servidor o de base de datos. Los componentes de SQL Server
Audit se combinan para producir una salida denominada auditoría, de la misma manera que
una definición de informe combinada con gráficos y elementos de datos da como resultado
un informe.
SQL Server Audit usa eventos extendidos para facilitar la creación de auditorías. Para
obtener más información acerca de los eventos extendidos.
SQL Server Audit
El objeto SQL Server Audit recopila una única instancia de acciones y grupos de acciones
de nivel de servidor o de base de datos para su supervisión. La auditoría se realiza en el
nivel de instancia de SQL Server . Es posible tener varias auditorías por cada instancia de
SQL Server .
Cuando se define una auditoría, se especifica la ubicación para los resultados generados.
Éste es el destino de la auditoría. La auditoría se crea en un estado deshabilitado y no
audita automáticamente ninguna acción. Una vez habilitada la auditoría, el destino de la
auditoría recibe los datos de la misma.
Especificación de auditoría de servidor
El objeto Especificación de auditoría de servidor pertenece a una auditoría. Puede crear
una especificación de auditoría de servidor por cada auditoría, ya que ambos se crean en el
ámbito de la instancia de SQL Server .
La especificación de auditoría de servidor recopila muchos grupos de acciones de nivel de
servidor generados por la característica Extended Events. Puede incluir grupos de acciones
de auditoría en una especificación de auditoría de servidor. Los grupos de acciones de
auditoría son grupos predefinidos de acciones, que constituyen eventos atómicos que tienen
lugar en el Motor de base de datos. Estas acciones se envían a la auditoría, que las registra
en el destino.
Destino
Los resultados de una auditoría se envían a un destino, que puede ser un archivo, el registro
de eventos de seguridad de Windows o el registro de eventos de aplicación Windows. Los
registros se deben revisar y archivar periódicamente para garantizar que el destino tiene
espacio suficiente para escribir registros adicionales.
La escritura en el registro de seguridad de Windows requiere que se agregue la cuenta del
servicio SQL Server a la directiva Generar auditorías de seguridad . De forma
predeterminada, el sistema local, el servicio local y el servicio de red forman parte de esta
directiva. Este valor se puede configurar utilizando el complemento de directiva de
seguridad (secpol.msc). Además, la directiva de seguridad Auditar el acceso a objetos
debe estar habilitada tanto para Correcto como para Error. Este valor se puede configurar
utilizando el complemento de directiva de seguridad (secpol.msc). En Windows Vista o
Windows Server 2008, puede establecer la directiva Aplicación generada más específica
desde la línea de comandos usando el programa de directiva de auditoría (AuditPol.exe) .
Para obtener más información sobre los pasos necesarios para habilitar la escritura en el
registro de seguridad de Windows, vea Escribir eventos de auditoría de SQL Server en el
registro de seguridad. Para obtener más información sobre el programa Auditpol.exe, vea el
artículo 921469 de Knowledge Base que describe cómo usar la directiva de grupo para
configurar las opciones detalladas de auditoría de seguridad. Los registros de eventos de
Windows son globales para el sistema operativo Windows. Para obtener más información
sobre los registros de eventos de Windows, vea Información general sobre el Visor de
eventos. Si necesita permisos más concretos en la auditoría, utilice el destino de archivo
binario.
Al guardar información de auditoría en un archivo, para tratar de impedir su alteración,
puede restringir el acceso a la ubicación del archivo de las maneras siguientes:
La cuenta de servicio de SQL Server debe tener los permisos de lectura y escritura.
Normalmente, los administradores de auditoría requieren los permisos de lectura y
escritura. Entonces, se supone que los administradores de auditoría son cuentas de
Windows para la administración de archivos de auditoría, por ejemplo para la copia
en diversos recursos compartidos, la copia de seguridad, etc.
Los lectores de auditoría autorizados para leer archivos de auditoría deben tener
permiso de lectura.
Incluso cuando el Motor de base de datos está escribiendo en un archivo, otros usuarios de
Windows pueden leer el archivo de auditoría si tienen permiso. El Motor de base de datos
no crea ningún bloqueo exclusivo que evite las operaciones de lectura.
Dado que el Motor de base de datos puede tener acceso al archivo, los inicios de sesión de
SQL Server que tengan el permiso CONTROL SERVER pueden utilizar el Motor de base
de datos para tener acceso a los archivos de auditoría. Para registrar a todos los usuarios
que estén leyendo el archivo de auditoría, defina una auditoría en
master.sys.fn_get_audit_file. De esta forma se registran los inicios de sesión con permiso
CONTROL SERVER que hayan tenido acceso al archivo de auditoría a través de SQL
Server.
Si un administrador de auditoría copia el archivo en otra ubicación (para archivarlo, etc.),
los permisos de las ACL de la nueva ubicación se deben reducir solo a los siguientes:
Administrador de auditoría: lectura/escritura
Lector de auditoría: lectura
Es recomendable generar los informes de la auditoría desde otra instancia de SQL Server,
por ejemplo una instancia de SQL Server Expressa la que solo tengan acceso
administradores de auditoría o lectores de auditoría. El uso de otra instancia de Motor de
base de datos para la creación de informes puede contribuir a evitar que usuarios no
autorizados obtengan acceso al registro de la auditoría.
Puede proporcionar más protección contra el acceso no autorizado mediante el cifrado de la
carpeta en que el archivo de la auditoría está almacenado. Para ello, puede utilizar el
Cifrado de unidad Bitlocker de Windows o el Sistema de archivos de cifrado de Windows.
Información general sobre el uso de SQL Server Audit
Puede utilizar SQL Server Management Studio o Transact-SQL para definir una auditoría.
Una vez creada y habilitada la auditoría, el destino comenzará a recibir entradas.
Puede leer los registros de eventos de Windows mediante la utilidad Visor de eventos en
Windows. Para los destinos de archivo, puede usar tanto el Visor del archivo de registros
en SQL Server Management Studio como la función fn_get_audit_file para leer el archivo
de destino.
El proceso general de creación y uso de una auditoría es el siguiente:
1. Cree una auditoría y defina el destino.
2. Cree una especificación de auditoría de servidor o una especificación de auditoría
de base de datos que se asigne a la auditoría. Habilite la especificación de auditoría.
3. Habilite la auditoría.
4. Lea los eventos de auditoría mediante el Visor de eventosde Windows, el Visor del
archivo de registroso la función fn_get_audit_file.
Le escuchamos: Si encuentra algo obsoleto o incorrecto en este artículo, como un paso o un
ejemplo de código, indíquenoslo. Puede hacer clic en el botón Esta página situado en la
sección Comentarios en la parte inferior de esta página. Leemos todos los elementos de
comentarios sobre SQL, normalmente el día siguiente. Gracias.
Consideraciones
Si se produce un error al comenzar la auditoría, el servidor no se iniciará. En este caso, se
puede iniciar el servidor con la opción -f en la línea de comandos.
Si un error de auditoría hace que el servidor se cierre o no se inicie porque se ha
especificado ON_FAILURE=SHUTDOWN para la auditoría, se escribirá en el registro el
evento MSG_AUDIT_FORCED_SHUTDOWN. Dado que el apagado se producirá en la
primera aparición de este valor, el evento se escribirá una vez. Este evento se escribirá
después de la aparición del mensaje del error para la auditoría que ha provocado el cierre.
El administrador puede omitir los cierres provocados por auditorías si inicia SQL Server en
modo de usuario único mediante la marca -m. De esta forma, indicará al sistema que
cualquier auditoría en la que se haya especificado ON_FAILURE=SHUTDOWN debe
ejecutarse en esa sesión como ON_FAILURE=CONTINUE. Cuando se inicia SQL Server
con la marca -m, se escribe el mensaje MSG_AUDIT_SHUTDOWN_BYPASSED en el
registro de errores.
Adjuntar una base de datos con una auditoría definida
Si se adjunta una base de datos que tiene una especificación de auditoría y que especifica
un GUID que no existe en el servidor, se producirá una especificación de auditoría
huérfana . Dado que en la instancia del servidor no existe ninguna auditoría con ese GUID,
no se grabará ningún evento de auditoría. Para corregir esta situación, utilice el comando
ALTER DATABASE AUDIT SPECIFICATION para conectar la especificación de
auditoría huérfana a una auditoría de servidor existente. O bien use el comando CREATE
SERVER AUDIT para crear una nueva auditoría de servidor con el GUID especificado.
Si lo desea, puede adjuntar una base de datos en la que se haya definido una especificación
de auditoría a otra edición de SQL Server que no admita SQL Server Audit, como SQL
Server Express , pero no se registrará ningún evento de auditoría.
Creación de reflejo de la base de datos y SQL Server Audit
Una base de datos en la que se haya definido una especificación de auditoría de base de
datos y que use la creación de reflejo de la base de datos incluirá la especificación de
auditoría de base de datos. Para poder trabajar correctamente en la instancia de SQL
reflejada, se deben configurar los elementos siguientes:
El servidor reflejado debe tener una auditoría con el mismo GUID para permitir que
la especificación de auditoría de base de datos escriba registros de auditoría. Esto se
puede configurar mediante el comando CREATE AUDIT WITH GUID = <GUID
de auditoría de servidor de origen>.
En el caso de los destinos de archivo binario, la cuenta de servicio del servidor
reflejado debe tener los permisos adecuados para la ubicación en la que se escribe la
pista de auditoría.
Si el destino es el registro de eventos de Windows, la directiva de seguridad del
equipo en el que está ubicado el servidor reflejado debe permitir el acceso de la
cuenta de servicio al registro de eventos de seguridad o de la aplicación.
Auditar a los administradores
Los miembros del rol fijo de servidor sysadmin se identifican como el usuario dbo en cada
base de datos. Para auditar las acciones de los administradores, se auditan las acciones del
usuario dbo .
Crear y administrar auditorías con Transact-SQL
Puede usar instrucciones DDL, vistas y funciones de administración dinámica y vistas de
catálogo para implementar todos los aspectos de la Auditoría de SQL Server .
Instrucciones de lenguaje de definición de datos (DDL)
Puede usar las siguientes instrucciones DDL para crear, modificar y quitar especificaciones
de auditoría:
Instrucciones DDL Descripción
ALTER AUTHORIZATION Cambia la propiedad de un elemento protegible.
ALTER DATABASE AUDIT Modifica un objeto de especificación de auditoría de base de datos
SPECIFICATION mediante la característica SQL Server Audit.
Instrucciones DDL Descripción
Modifica un objeto de auditoría de servidor mediante la
ALTER SERVER AUDIT
característica SQL Server Audit.
ALTER SERVER AUDIT Modifica un objeto de especificación de auditoría de servidor
SPECIFICATION mediante la característica SQL Server Audit.
CREATE DATABASE AUDIT Crea un objeto de especificación de auditoría de base de datos
SPECIFICATION mediante la característica SQL Server Audit.
CREATE SERVER AUDIT Crea un objeto de auditoría de servidor mediante SQL Server Audit.
CREATE SERVER AUDIT Crea un objeto de especificación de auditoría de servidor mediante
SPECIFICATION la característica SQL Server Audit.
DROP DATABASE AUDIT Elimina un objeto de especificación de auditoría de base de datos
SPECIFICATION mediante la característica SQL Server Audit.
Quita un objeto de auditoría de servidor usando la característica
DROP SERVER AUDIT
SQL Server Audit.
DROP SERVER AUDIT Elimina un objeto de especificación de auditoría de servidor
SPECIFICATION mediante la característica SQL Server Audit.
Funciones y vistas dinámicas
En la tabla siguiente se enumeran las funciones y vistas dinámicas que puede usar con las
auditorías de SQL Server .
Funciones y vistas dinámicas Descripción
Devuelve una fila por cada acción de auditoría sobre la que se
puede guardar información en el registro de auditoría y por cada
sys.dm_audit_actions
grupo de acciones de auditoría que se puede configurar como
parte de SQL Server Audit.
sys.dm_server_audit_status Proporciona información sobre el estado actual de la auditoría.
Devuelve una tabla que asigna el campo class_type del registro de
sys.dm_audit_class_type_map
auditoría al campo class_desc en sys.dm_audit_actions.
Devuelve información de un archivo de auditoría creado por una
fn_get_audit_file
auditoría de servidor.
Vistas de catálogo
En la tabla siguiente se enumeran las vistas de catálogo que puede usar con las auditorías de
SQL Server .
Vistas de catálogo Descripción
Contiene información sobre las especificaciones de
sys.database_audit_specifications auditoría de base de datos en una auditoría de SQL
Server de una instancia del servidor.
Contiene información sobre las especificaciones de
auditoría de base de datos en una auditoría de SQL
sys.database_audit_specification_details
Server de una instancia de servidor para todas las bases
de datos.
Contiene una fila para cada auditoría de SQL Server de
sys.server_audits
una instancia de servidor.
Contiene información sobre las especificaciones de
sys.server_audit_specifications auditoría de servidor en una auditoría de SQL Server de
una instancia del servidor.
Contiene información sobre los detalles de
sys.server_audit_specifications_details especificación de auditoría del servidor (acciones) en
una auditoría de SQL Server de una instancia de servidor.
Contiene información adicional sobre el tipo de auditoría
sys.server_file_audits de archivos en una auditoría de SQL Server de una
instancia de servidor.
Permisos
Cada una de las características y los comandos para SQL Server Audit tiene sus propios
requisitos de permisos.
Para crear, modificar o quitar una auditoría de servidor o una especificación de auditoría de
servidor, las entidades de seguridad de servidor requieren el permiso ALTER ANY
SERVER AUDIT o CONTROL SERVER. Para crear, modificar o quitar una especificación
de auditoría de base de datos, las entidades de seguridad de base de datos requieren el
permiso ALTER ANY DATABASE AUDIT, o el permiso ALTER o CONTROL en la base
de datos. Además, las entidades de seguridad deben tener el permiso para conectarse a la
base de datos o los permisos ALTER ANY SERVER AUDIT o CONTROL SERVER.
El permiso VIEW ANY DEFINITION proporciona acceso las vistas de auditoría de nivel
de servidor y VIEW DEFINITION proporciona acceso a las vistas de auditoría de nivel de
base de datos. La denegación de estos permisos invalida la posibilidad de ver las vistas de
catálogo, incluso si la entidad de seguridad tiene los permisos ALTER ANY SERVER
AUDIT o ALTER ANY DATABASE AUDIT.
Para obtener más información sobre cómo conceder derechos y permisos, vea GRANT
(Transact-SQL).
https://docs.microsoft.com/es-es/sql/relational-databases/security/auditing/sql-server-audit-
database-engine?view=sql-server-ver15
Técnicas de auditoría de bases de datos
SQL Server
La auditoría de bases de datos SQL Server no es usada solamente para cumplir con
requerimientos de conformidad. Se ha vuelto necesaria para el análisis de acciones de bases
de datos, soluciones de problemas y la investigación de actividades sospechosas y
maliciosas. La auditoría también puede ayudar a evitar acciones inapropiadas de parte de
los usuarios – como si tuviera un sistema CCTV en sus bases de datos.
Hay muchas técnicas de auditoría de SQL Server:
Auditoría manual – puede ser creada para cumplir con sus requerimientos
específicos, pero consume tiempo y es proclive a errores
Utilizar SQL Server Extended Events – fácil de configurar, un amplio rango de
acciones pueden ser auditadas, pero no ofrece información acerca de qué se
eliminó/insertó ni tampoco valores antiguos y nuevos para actualizaciones; una
auditoría detallada puede causar problemas de desempeño
Usar desencadenadores de SQL Server – fácil de configurar, pero puede causar
problemas de desempeño en bases de datos de altas transacciones
Leer los registros de transacciones – no hay captura de datos adicional, ya que SQL
Server ya sigue estos cambios. Más espacio de almacenamiento es necesario,
algunas de las acciones (como EXECUTEs) no son auditadas
Usar SQL Server Profiler y traces de SQL Server – flexible y complejo. Es difícil
leer y filtrar los registros
Cuál es el correcto para usted depende de su ambiente, lo que necesita auditar, dónde quiere
almacenar las acciones capturadas y la forma que quiere que tengan los reportes.
Auditoría manual
Las bases de datos SQL Server pueden ser auditadas usando procedimientos almacenados
especialmente desarrollados y funciones para rastrear cambios en los datos y los objetos.
Provee una solución flexible que requiere una gran cantidad de código y desarrollo, lo cual
incrementa el costo y el tiempo de implementación.
Usar SQL Server Extended Events
SQL Server Audit es una característica de SQL Server, introducida primero en la versión
2008 que usa SQL Server Extended Events para auditar acciones de SQL Server. Permite
auditar diferentes acciones, proveyendo mucha granularidad en el proceso de configuración
y cubriendo un amplio rango de la actividad de SQL Server.
Para crear un nuevo objeto de SQL Server Audit:
1. En SQL Server Management Studio, expanda Security y haga clic derecho en
Audits
2. Seleccione New Audit
3. Especifique un nombre para la auditoría, establezca si desea almacenar los datos de
auditoría en un registro de eventos de la aplicación, un registro de eventos de
seguridad o un archivo, y una localización para el archivo de auditoría
4. Haga clic en OK y su auditoría aparecerá en el nodo Audits en el Object Explorer de
SQL Server Management Studio
5. Por defecto, está deshabilitada y por tanto mostrada con una flecha roja. Para
habilitarla, haga clic derecho y selecciones Enable Audit
6. Elija si necesita Server Audit Specification o Database Audit Specification,
dependiendo si usted quiere auditar la actividad al nivel de una instancia SQL
Server o una base de datos
7. Para crear una Database Audit Specification, expanda el nodo de la base de datos
que desea auditar, vaya a Security, haga clic derecho en Database Audit
Specifications y seleccione New Database Audit
8. En el diálogo Create Database Audit Specification, especifique un nombre de
especificación, asocie la especificación con el objeto de auditoría creado en el paso
#1, especifique la actividad que será auditada en Audit Action Type. Para la
auditoría de bases de datos, especifique una base de datos, objeto, o esquema como
Object Class, el nombre del objeto auditado, y el login auditado
Usted puede ver todas las acciones que pueden ser auditadas usando SQL Server Auditing
en la lista desplegable para Audit Action Type
Los Principales que usted debe seleccionar en este diálogo son realmente cuentas de
usuario que serán monitoreadas.
Al igual que las auditorías, las especificaciones de auditoría de bases de datos están
deshabilitadas por defecto. Para habilitarlas, seleccione esta opción en su menú contextual
Ahora, todas las sentencias DELETE ejecutadas contra Person.BusinessEntityAddress
serán auditadas e insertadas en archivos con nombres que empiezan con Audit-, como
Audit-AW2012Test_9D93CA4A-8B90-40B8-8B0B-
FCBDA77B431D_0_130161593310500000.sqlaudit, y almacenadas en E:\
Dado que puede haber muchas acciones capturadas en una base de datos activa, es
recomendado grabar la información de auditoría en un archivo. El archivo sqlaudit no
puede ser abierto en un editor de texto o hex. Use Reporting Services o la función T-SQL
fn_get_audit_file para analizar los datos.
Por ejemplo:
SELECT event_time,action_id,statement,database_name,server_principal_name
FROM fn_get_audit_file( 'E:\Test\Audit-*.sqlaudit' , DEFAULT ,
DEFAULT);
lo anterior muestra los siguientes resultados:
Incluso cuando usted selecciona todas las columnas, usted no verá qué fue eliminado
realmente, sólo cuándo y quién realizó la eliminación, lo cual es una de las desventajas de
este método.
Otros inconvenientes son:
Como SQL Server Audit usa recursos de SQL Server para una auditoría detallada,
esto puede afectar el rendimiento general de SQL Server
Administrar SQL Server Audit en instancias SQL Server múltiples no puede ser
centralizado por defecto
Analizar y archivar los datos de auditoría (en un archivo, o en registros) involucra
importación, reportes y archivado manuales
Está disponible sólo en versiones de SQL Server 2008 y posteriores
La auditoría a nivel de base de datos está disponible sólo en las ediciones
Enterprise, Developer y Evaluation
Usar desencadenadores de SQL Server
Los desencadenadores de SQL Server son automáticamente activados cuando un cierto
evento sucede. Los desencadenadores son realmente procedimientos almacenados
ejecutados automáticamente cuando una condición se cumple. Los desencadenadores de
Data Manipulation Language (DML) por tanto pueden ser usados para rastrear las
sentencias INSERT, UPDATE y DELETE. Usted puede crear estos desencadenadores uno
por uno para cada tabla y cada sentencia que desea auditar. Usted también necesita crear un
espacio de almacenamiento para la información auditada – por ejemplo, una tabla SQL
donde serán insertados el tiempo de la transacción, el nombre de usuario, el tipo de
transacción, etc. Para rastrear UPDATEs, es muy útil almacenar los valores antiguos y
nuevos.
Por ejemplo, un desencadenador que es activado después de que un registro fuera insertado
en la tabla Person.Person inserta un nombre de tabla, un tiempo y fecha cuando el registro
fue insertado y el nombre de usuario usado para insertar el registro a la tabla
dbo.Repository. Debería verse así:
CREATE TRIGGER PersonPerson_I
ON Person.Person
AFTER INSERT
AS
INSERT INTO dbo.repository (
TABLE_NAME,
TABLE_SCHEMA,
AUDIT_ACTION_ID,
MODIFIED_BY,
MODIFIED_DATE,
[DATABASE]
)
values(
'Person',
'Person',
'Insert',
SUSER_SNAME(),
GETDATE(),
'AdventureWorks2012'
) GO
Antes de que tales desencadenadores sean creados, usted debería diseñar y crear la(s)
tabla(s) donde el DML será almacenado.
Este método es proclive a errores, ya que hay mucho trabajo manual involucrado.
ApexSQL Trigger es una herramienta de auditoría de bases de dato que captura cambios
en los datos que han ocurrido en una base de datos, incluyendo la información acerca de
quién hizo el cambio, qué objetos fueron afectado por él, cuándo fue hecho así como la
información del login de SQL, la aplicación y el anfitrión usados para hacer el cambio.
Almacena toda la información capturada en el repositorio central y la exporta en formatos
amigables para imprimir. Para crear desencadenadores, simplemente seleccione las tablas y
los tipos de operación que desea auditar.
1. Inicie ApexSQL Trigger
2. Conéctese a la base de datos que desea auditar
3. En la cuadrícula principal, seleccione la tabla que desea auditar
4. En el panel Fields, seleccione las columnas a auditar
5. Seleccione las transacciones a auditar – INSERT, DELETE, UPDATE
6. Repita los pasos del 3 al 5 para todas las tablas que desea auditar
7. En el menú, haga clic en Create triggers
8. El script que genera los desencadenadores especificados es mostrado en el diálogo
Script. Verifíquelo y presione F5 para ejecutarlo
Una vez que los desencadenadores son creados, serán ejecutados por cada INSERT,
DELETE y UPDATE ejecutados contra la tabla y los detalles de la operación son
almacenados en las tablas AUDIT_LOG_DATA y AUDIT_LOG_TRANSACTIONS.
Usted puede verlos fácilmente usando los reportes incorporados de ApexSQL Trigger, o
creando consultas SQL propias.
Mientras que los desencadenadores proporcionan una auditoría granular y un
almacenamiento fácil de acceder, su principal desventaja es que los que son activados en
una base de datos atareada pueden causar gastos extras.
Leer los registros de transacciones
Como cada cambio de esquema y datos en una base de datos SQL Server es añadido a un
registro de transacciones en línea como una entrada de registro, leer estas entradas puede
ser usado como una técnica de auditoría. Abrir un archivo de registro de transacciones en
línea de bases de datos, un registro de transacciones suelto o copias de seguridad de
registros de transacciones y leerlos no tan fácil. Una de las opciones es usar funciones no
documentadas como fn_dblog, fn_dump_dblog, y DBCC PAGE.
Aparte de la complejidad y los problemas con la reconstrucción de UPDATE/BLOB, su
mayor desventaja es que muestran valores hex que usted tiene que descifrar.
Para leer registros de transacciones, use un lector de registros de SQL Server como
ApexSQL Log. Esta herramienta audita, revierte o rehace cambios de datos y objetos que
han afectado una base de datos, incluyendo aquellos que han ocurrido antes de la
instalación de ApexSQL Log. También captura información acerca del usuario, la
aplicación y el anfitrión usados para hacer cada cambio.
1. Inicie ApexSQL Log
2. Conéctese a la base de datos que desea auditar
3. En el paso Select SQL logs to analyze, añada las copias de seguridad de registros
de transacciones y los registros de transacciones sueltos que quiere leer. Note que
ellos deben formar una cadena completa para proveer una auditoría exitosa
4. Use las opciones de Filter setup para reducir el conjunto de resultados usando el
tiempo el tipo de operación, el nombre de la tabla, el usuario y otras opciones de
filtrado
5. Haga clic en Open
6. Los resultados son mostrados en la cuadrícula principal y usted puede crear
fácilmente scripts para deshacer y rehacer, o exportarlos a archivos CSV, HTML,
XML o archivos SQL y grabarlos en el disco duro
Las ventajas de este método son que no hay desencadenadores y tampoco procesos
adicionales para capturar la información de auditoría que puede afectar el desempeño de
SQL Server. El historial de transacciones puede ser obtenido para el periodo antes de que la
herramienta fuera instalada, a diferencia de los desencadenadores y Extended Events.
Las desventajas son:
Más espacio es requerido para suficientes fuentes de datos, ya que una base de datos
tiene que estar en un modelo de recuperación completo, y una cadena completa de
registros de transacciones debe existir
No todas las acciones que un usuario quiera auditar son almacenadas en un registro
de transacciones. Por ejemplo, las sentencias SELECT ejecutadas y las consultas no
son almacenadas ahí
Usar SQL Server Profiler y traces de SQL Server
Usar SQl Server Profiler y traces de SQl Server para auditar es una solución muy compleja
con mucho trabajo manual, y por lo tanto proclive a errores.
ApexSQL Audit es una herramienta de auditoría construida sobre traces de SQL Server, lo
que provee información de “quién vio qué”, una auditoría con tolerancia a fallos, reporte
centralizado, una GUI amigable para el usuario para configurar la auditoría en más de 230
operaciones, y un repositorio centralizado para almacenar registros de auditoría y
configuraciones. La herramienta configura los traces de acuerdo a la configuración que un
usuario ha especificado, o usa su configuración por defecto que cubre las solicitudes más
comunes de auditoría.
1. Inicie ApexSQL Audit
2. Haga clic en el botón ‘Add server’ para seleccionar un servidor para la auditoría
3. Haga clic en el botón ‘Add database’ para seleccionar una base de datos para la
auditoría, y seleccione el servidor o base de datos que desea auditar
Otra opción es usar el tipo de filtro Avanzado:
Ahora, cuando sea que cualquier operación que usted seleccionó es realizada en el SQL
Server auditado, un registro será guardado en la base de datos del repositorio central –
ApexSQLCrd.
Para ver los registros de auditoría, usted puede un reporte local integrado, o usar la
funcionalidad de reportes web.
Las ventajas de ApexSQL Audit son la fácil configuración de auditoría, un rango amplio
de tipos de operaciones auditadas, el repositorio central, los reportes web, de tal modo que
incluso usuarios remotos puedan acceder fácilmente a todos los registros de auditoría, y la
administración fácil y centralizada.
https://solutioncenter.apexsql.com/es/tecnicas-de-auditoria-de-bases-de-datos-sql-server/
