Trazar acceso a bases de datos

 
Dirigido a: Administradores de Bases de Datos
Autor: Pablo F. Dueñas Campo

Introducción
En ocasiones, como DBA, nos toca verificar qué usuarios realmente están accediendo a una base de datos y qué tipo de operaciones realizan, para determinar los derechos mínimos que se necesitan. Esta tarea quizás debieran hacerla los creadores de la aplicación, pero no siempre tenemos acceso a ellos y nos toca ver las aplicaciones como una caja negra.

SQL Server nos provee un evento que nos dice los inicios de sesión, pero ¿podemos tener esta información para las conexiones a las bases de datos?

Inicio de sesión
Para las pruebas que vamos a realizar, vamos a usar SQLCMD que hace menos cosas en segundo plano. Por ejemplo, es conocido que SSMS abre 3 conexiones por cada ventana de consulta que abrimos. También he creado dos bases de datos porque, como veremos, el problema fundamental son las consultas distribuidas.

Empezaremos con un inicio de sesión que no tiene derechos de conexión en ninguna base de datos. Es decir, el único rol de servidor que tiene es public y no tiene asignación de usuario en ninguna de las bases de datos.

Cuando iniciamos sesión en SQL Server hay una serie de eventos. Veámoslos:

Vemos el evento audit login que nos indica que se produce con la base de datos master más otros eventos de configuración. Si conectamos con la base de datos master o con la msdb veremos que podemos hacer consultas aunque no esté dado de alta nuestro usuario en dichas bases de datos.

Consultas
Pero lo que nos interesa es poder trazar la actividad en nuestra base de datos de prueba. Porque el evento «audit login» lo que nos dice es que se ha iniciado sesión en SQL Server, no que ha habido una conexión a una base de datos. Esto lo podemos comprobar dando, ahora sí, derechos de conexión y rol db_owner al usuario de prueba, y cambiando de base de datos:

Vemos que al cambiar de base de datos sí tenemos unos eventos que nos pueden decir que se ha realizado la conexión. En concreto el evento User Error Message con el texto Se cambió el contexto… nos dice la base de datos donde se van a ejecutar las instrucciones siguientes.

Pero ¿qué pasa si hacemos una consulta a otra base de datos sin cambiar el contexto? Aquí cambian mucho las cosas:

Después de cambiar el contexto, vemos que en este caso solo hay un evento que nos dice que se ha hecho una conexión a la base de datos Adm. Este evento es Audit Schema Object Access. Veamos si es el evento que andamos buscando.

Primero probamos si nos sirve con un procedimiento almacenado:

En esta ocasión vemos que, aunque el lote y la instrucción se inician desde la base de datos Test, el resto de eventos aparecen en Adm, incluido el que estamos probando.

Por último, probamos con una función de tabla:

Pongo solo parte de los eventos, pues en este caso es bastante más compleja la lista de eventos. Pero vemos que parte apuntan a la base de datos Test y parte a la Adm. En especial el evento que estamos estudiando.

Audit Schema Object Access Event
Si acudimos a la ayuda de SQL Server, la definición de este evento es: Se produce cuando se utiliza un permiso de objeto (como SELECT), tanto con éxito como con error. Es decir, este evento nos dice que se ha accedido a un objeto, y además nos dice la consulta que lo origina.

Pero hay otros aspectos que hacen muy interesante su uso:

• ApplicationName: nos dice el nombre de la aplicación, si esta tiene a bien incluirlo en la cadena de conexión, lo que nos puede ayudar mucho en establecer quién/qué usa la base de datos
• HostName: nos dice el nombre del equipo, entre comillas. Tiene que ponerlo la conexión y algunas veces no es correcto el que aparece, especialmente con sesiones de Termina Server
• LoginName y SessionLoginName: el usuario, no podía faltar
• ObjectName: por si nos interesa saber los objetos que se usan
• Permissions: muy interesante, dado que de esta manera sabemos si basta con que tenga permisos de lectura o si necesita además permisos de escritura o de ejecución
• Success: también muy interesante, dado que nos permite averiguar si está accediendo a todo sin problemas o si se le están denegando ciertos accesos al usuario

Objetos obsoletos
Por último y para ser exhaustivos, en Internet se pueden encontrar referencias al uso de este evento para detectar objetos obsoletos. Es decir, dentro de una base de datos puede haber tablas, procedimientos almacenados, etc., que ya no están en uso y que nos interesa limpiar para que no sea tan complicado su mantenimiento o para evitar confusiones. En ese caso, este evento es muy útil dado que nos da en una de sus columnas el nombre del objeto. Podemos usar esta columna para ver qué objetos se usan o bien usarla de filtro para comprobar si cierto grupo de objetos se ha quedado obsoleto.

Resumen
Al buscar cómo dar seguimiento a los accesos a una base de datos, hemos encontrado que el evento que nos proporciona la información buscada y algo más, es uno no muy obvio ni conocido, Audit Schema Object Access, que nos puede ayudar mucho a afinar los derechos que está empleando un usuario de base de datos, una aplicación, etc.