Hola, chicos,
¡Buen día!
En la publicación de hoy, demostraré cómo crear un seguimiento en SQL Server usando SQL Profiler para auditar el acceso a objetos, ya sea por parte de un usuario, objeto o base de datos específica. Esto es especialmente útil para identificar qué usuarios tienen acceso a un determinado objeto o también para saber qué usuarios no tienen acceso a un objeto y aun así intentan acceder a él, ya sea una tabla, vista, procedimiento almacenado, etc. En varias empresas, esto se utiliza para auditar el acceso a tablas críticas con información sensible, como datos de empleados y salarios, etc.
SQL Server Profiler, una utilidad muy conocida para la mayoría de los administradores de bases de datos de SQL Server, pero no tanto para los desarrolladores, tiene muchos otros usos además de realizar auditorías, especialmente con el crecimiento de los sistemas que utilizan ORM, como Hibernate y Entity Framework, para encapsular consultas de bases de datos. Estas herramientas se encargan de generar consultas SQL y consultas para el desarrollador, quien solo se preocupa de codificar en su lenguaje de programación (Java, C#, etc.) y no necesita escribir una línea de código SQL.
Estas consultas generalmente no tienen mucho rendimiento y el desarrollador a menudo no sabe cómo el ORM ensambló la consulta. Cuando comienzan a ocurrir problemas de rendimiento en el sistema, SQL Server Profiler es una poderosa herramienta que permite identificar qué comando exacto está ejecutando el sistema y permite al DBA realizar análisis de rendimiento apropiados, analizar índices, etc.
Cómo abrir SQL Server Profiler desde Management Studio
Inicie SQL Server Management Studio y abra SQL Server Profiler:
Cómo configurar eventos y filtros de seguimiento en SQL Server Profiler
Una vez abierto, configura la descripción de tu seguimiento, donde se guardarán los resultados (siempre prefiero en una tabla), y otras configuraciones que consideres relevantes:
Ahora es el momento de seleccionar el evento que monitorearemos, que es el “Evento de acceso al objeto del esquema de auditoría” y seleccionar las columnas que queremos que se registren en la tabla de seguimiento:
Para ajustar los filtros de captura de seguimiento, haga clic en el botón “Filtros de columna” y se abrirá una ventana donde será posible realizar los más diversos tipos de condiciones para que esta sesión sea auditada. Enumeraré los más comunes:
- Nombre de la aplicación: Filtro que ya viene activado por defecto y con la cláusula NOT LIKE “SQL Server Profiler –
” para que no se audite la propia sesión de Trace, permite realizar filtros utilizando el software utilizado para conectarse a la base de datos, como “Microsoft SQL Server Management Studio – Query” o “.Net SqlClient Data Provider”. - Nombre de la base de datos: Le permite filtrar bases de datos que pueden o no ser parte del seguimiento.
- DBUnombre de usuario: Le permite filtrar qué usuarios de SQL Server pueden o no ser parte del seguimiento.
- Nombre de host: Filtro que le permite seleccionar qué nombres de host (nombres de máquinas) serán o no auditados
- Nombre de inicio de sesión: Define si se desea aplicar un filtro al inicio de sesión (puede ser AD o SQL Server) para que sea parte de Trace o no.
- Nombre del objeto: Le permite filtrar objetos (tablas, vistas, procedimientos almacenados, etc.) que pueden o no ser parte del seguimiento.
- SPID: Filtro que le permite seleccionar qué sesiones serán auditadas o no por Trace
- Éxito: Parámetro esencial para este post, que define si la sesión pudo acceder al objeto (1) o si no pudo por un error de permiso (0). Para lograr el objetivo de este post, debes utilizar la condición Igual a 0
Vale la pena recordar que al editar filtros, si desea agregar más de un valor a las cláusulas de filtro, simplemente presione la tecla “Enter” cuando el cursor esté posicionado en el cuadro de texto, para que aparezcan más cuadros de texto debajo:
En este ejemplo, apliqué el filtro “Excluir filas que no contienen valores” a las columnas DatabaseName y DBUsername para que los intentos de acceso indirecto, como SQL Prompt e Intellisense, eviten que se escriba información no tan interesante en el log y lo deje muy “contaminado”.
Si ha iniciado el seguimiento y lo está editando, simplemente deténgalo usando el botón Detener (o en el menú Archivo > Detener seguimiento) y seleccione la opción de menú “Archivo” > “Propiedades…”.
Ahora haga clic en el botón "Ejecutar" y el seguimiento comenzará en la instancia:
Visualización de resultados de seguimiento en SQL Server Profiler
Ahora usaré un usuario sin permiso e intentaré realizar una consulta en una tabla que generará un error de falta de permiso:
Si volvemos a la ventana del SQL Server Profiler, veremos que este intento de acceso quedó registrado:
O podemos realizar consultas sobre la tabla en la que configuramos donde Trace debe guardar los resultados:
Exportación de definiciones de seguimiento a un archivo SQL
SQL Server Profiler también nos da la opción de exportar las definiciones y filtros realizados como un archivo .sql, para que puedas volver a activar este rastreo de una forma más práctica y rápida, e incluso puedas automatizarlo en un Job, por ejemplo:
Y este será el resultado de exportar Trace como un script SQL:
Vale la pena recordar que cuando exporta el seguimiento a .sql, NO se admite la exportación de datos a la tabla de la base de datos.
Consultar el resultado de seguimiento habilitado para script SQL
Como se explicó anteriormente, usando el script podemos activar fácilmente el seguimiento, pero no es posible exportar los datos a una tabla, solo a archivos de seguimiento (.trc). Afortunadamente, esto no es un problema, ya que podemos consultar fácilmente el archivo de seguimiento usando la función fn_trace_gettable (Obtén más información accediendo a las publicaciones Uso del seguimiento estándar de SQL Server para auditar eventos (fn_trace_gettable) y Monitoreo de operaciones DDL y DCL usando fn_trace_gettable de SQL Server).
Primero, abrimos el archivo SQL generado en SQL Server Management Studio y cambiamos el nombre del archivo de seguimiento "InsertFileNameHere" a una ruta accesible para su servidor, como lo hice en el siguiente ejemplo:
Entonces, intentemos consultar la tabla y ver nuevamente el mensaje de acceso denegado. Ahora que el mensaje se ha registrado en nuestro archivo de seguimiento, realizamos la consulta:
Identificar y detener un evento de rastreo
Si elige iniciar eventos de seguimiento utilizando el script SQL, será muy importante poder identificar si el seguimiento está activo y saber cómo detenerlo. Para hacer esto, simplemente ejecute la siguiente consulta
select * from sys.traces
Una vez que hemos recuperado el ID de nuestro rastreo, detengámoslo y comprobamos si realmente ya no está activo:
¡Y eso es todo, amigos!
La idea era crear un post específico sobre auditoría de acceso a objetos, que acabó convirtiéndose en un minitutorial sobre SQL Server Profiler.
Espero que hayas disfrutado del post y hasta la próxima.
Comentários (0)
Carregando comentários…