Auditando sentencias SELECT en SQL Server

Aunque las sentencias SELECT no son destructivas por naturaleza ni tampoco pueden cambiar datos o esquemas, hay muchos cases que requieren su auditoría en SQL Server. Las sentencias SELECT ejecutadas pueden indicar varios problemas actuales o potenciales, y esta es la razón por la cual es importante saber quién vio qué y cuándo.

En general, dos casos de uso mayores requieren el rastro de sentencias SELECT:

• Para saber quién y cuándo está accediendo a sus datos para resolver problemas de seguridad, aplicación o desempeño
• Para proveer un requerimiento necesario (por ejemplo, para cumplir con HIPAA y otras regulaciones)

Hay muchas soluciones nativas de SQL Server para auditar sentencias SELECT.

Procedimientos almacenados y funciones

Rastrear quién vio qué puede ser hecho a través de procedimientos almacenados y funciones especialmente desarrolladas. Para usar es método de auditoría, el acceso a la base de datos debe ser limitado a través del uso de procedimientos almacenados (permitir sentencias EXEC solamente, mientras se prohíben todas las consultas y operaciones DML). El resultado de una consulta es retornado por un procedimiento almacenado, mientras al mismo tiempo el procedimiento almacenado registra el acceso en una tabla repositorio de auditoría apropiada junto a información adicional del interés (por ejemplo, el tiempo de ejecución).

De todas maneras, este método de auditoría requiere programación T-SQL y mantenimiento adicional de los procedimientos almacenados y las funciones (por ejemplo, en caso de un cambio en el esquema de la base de datos). Por otra parte, las consultas ad hoc ejecutadas por partes confiables (por ejemplo, miembros del rol sysadmin) pueden fácilmente sobrescribir la regla de que las sentencias SELECT deben interpretarse por un procedimiento almacenado, y entonces no pueden ser rastreadas. Esto establece el uso de procedimientos almacenados y funciones como un método de auditoría inaceptable para el cumplimiento de cualquier regulación.

Tecnología de rastreo de SQL Server

SQL Server provee el monitoreo de eventos seleccionados vía la tecnología de rastreo. Está disponible a través de la Interfaz de Programación de Aplicaciones (Application Programmer Interface, API), SQL Server Profiler como una GUI para ver el rastreo en tiempo real, o accediendo a los archivos de rastro grabados.

Para auditar la ejecución de sentencias SELECT en una base de datos específica:

1. Inicie SQL Server Profiler y provea las credenciales para la instancia SQL Server que contiene a la base de datos
2. Ingrese el nombre de la traza (por ejemplo, TraceSELECTs) que está a punto de crear
3. Seleccione la plantilla Blank en el menú desplegable Use the template; nosotros usaremos la plantilla vacía dado que queremos capturar solamente las sentencias SELECT y no llenar el repositorio de auditoría con otra información

   

4. Seleccione la opción Save to table, y provea la información para la tabla repositorio de auditoría destino. La tabla puede estar localizada en otra instancia de SQL Server que la auditada; en este ejemplo usaremos la misma.

   

Usaremos la base de datos AUDITDB (una base de datos vacía) y especificaremos una tabla repositorio apropiada para ser creada (TraceSELECTsTable)

5. Ahora, usando la pestaña Events Selection, encuentre y seleccione el elemento SQL:StmtStarting en la columna Events

   

6. Haga clic en Column Filters para abrir el diálogo Edit Filter

7. Seleccione la propiedad TextData filter, e ingrese “SELECT%” como el valor para el filtro Like

   

8. Como queremos monitorear sólo la base de datos especificada (la base de datos ACMEDB en nuestro ejemplo), especificaremos un filtro adicional. Seleccione la propiedad del filtro DatabaseName, e ingrese “ACMEDB” como el valor para el filtro Like

   

9. Haga clic en OK para confirmar los cambios en los filtros
10. Seleccione la opción Run para iniciar la auditoría vía la traza definida TraceSELECs

Cualquier sentencia SELECT ejecutada en la base de datos ACME será rastreada y capturada en la tabla TraceSELECTsTable. Cuando se requiera, la siguiente información es retornada:

La información auditada contiene la información relevante requerida para este tipo de auditoría (por ejemplo, el nombre del login y la base de datos, el tiempo de ejecución y la sentencia SELECT exacta que se ejecutó). De todas maneras, hay muchas desventajas en auditar usando la tecnología de rastreo nativa de SQL Server.

Este método de auditoría no es una solución por defecto. Requiere una configuración manual precisa usando filtros, no provee una solución contra la manipulación de la información capturada por partes confiables, y no hay habilidad para archivar. Adicionalmente, para exportar los datos y crear reportes de auditoría apropiados, este método de auditoría requiere conocimiento de T-SQL.

Característica Audit de SQL Server

La característica Audit de SQL Server fue introducida en SQL Server 2008. La característica utiliza la tecnología Extended Events, y audita los eventos tanto del servidor como de la base de datos. De todas maneras, la auditoría a nivel de base de datos es soportada por las ediciones Enterprise y Developer solamente.

Usar SQL Server Audit como el método para rastrear la ejecución de sentencias SELECT produce menos costo adicional que la tecnología de rastreo, pero dependiendo de cuán ocupada esté la base de datos, un impacto en el desempeño del servidor puede ocurrir.

Cualquiera de las opciones de T-SQL o SQL Server Management Studio puede ser usada para configurar la característica SQL Server Audit. En este artículo nos enfocaremos en SQL Server Management Studio; usted puede fácilmente crear y ver los scripts T-SQL correspondientes usando la opción CREATE To en SQL Server Management Studio.

Para auditar la ejecución de sentencias SELECT en una base de datos específica:

1. Expanda la carpeta de Security

2. Seleccione New Audit y establezcla el Audit name (por ejemplo, AuditSELECTsServerSpecification) y el File path (por ejemplo, C:\AUDITs) en el diálogo Create Audit

   

   Un archivo .sqlaudit correspondiente será generado en la carpeta especificada (C:\AUDITs) que será usada como un repositorio de auditoría.

3. Confirme la creación del objeto de auditoría de SQL Server haciendo clic en OK
4. Haga clic derecho en la auditoría creada y seleccione la opción Enable Audit

Los siguientes pasos describen cómo crear una especificación de auditoría de base de datos, lo cual requiere un objeto de auditoría de SQL Server creado previamente (AuditSELECTsServerSpecification). Es la especificación de auditoría de la base de datos que refinará el rastreo de sentencias SELECT

5. Expanda la base de datos que desea auditar en Object Explorer
6. Expanda la carpeta Security
7. Haga clic derecho en la carpeta Database Audit Specification y seleccione New Database Audit Specification
8. Teclee en el nombre de la nueva especificación de auditoría de la base de datos (por ejemplo, AuditSELECTsDatabaseSpecification) y seleccione el objeto de auditoría de SQL Server previamente creado usando el menú desplegable Audit. Para refinar la auditoría, establezca lo siguiente:
   1. Audit Action Type: SELECT
   2. Object Class: DATABASE
   3. Object Name: ACMEDB

   4. Principal: public – la auditoría es registrada sólo si el nombre principal ejecuta una sentencia SELECT. Como queremos auditar todas las sentencias SELECT, a pesar de quién la ejecutó, el nombre principal debería ser “public”

      

9. Confirme la creación de la especificación de la auditoría de la base de datos haciendo clic en OK
10. Finalmente, haga clic derecho en la auditoría creada y seleccione la opción Enable Database Audit Specification

Después de que el objeto de auditoría y la especificación de auditoría de base de datos están establecidos y habilitados, cada SELECT ejecutado en cualquier tabla será auditado y grabado en el archivo .sqlaudit.

Para ver las sentencias SELECT auditadas:

1. Haga clic derecho en la auditoría AuditSELECTsServerSpecification y seleccione la opción View Audit Logs del menú contextual para lanzar el diálogo visor del registro de auditoría

2. El diálogo mostrará la información acerca de las sentencias SELECT presentes en la base de datos particular

   

Por supuesto, este no es un método conveniente ni una buena forma de proveer datos auditados. La otra forma de exportar y proveer datos capturados es vía la función fn_get_file_audit. La función fn_get_audit_file lee los archivos *.sqlaudit creados por la característica SQL Server Audit.

El siguiente script consulta la información capturada relacionada a sentencias SELECT ejecutadas en la base de datos ACMEDB:

SELECT
       event_time ,
       session_server_principal_name AS UserName ,
       server_instance_name ,
       database_name ,
       object_name ,
       statement
  FROM sys.fn_get_audit_file('C:\AUDITs\*.sqlaudit', DEFAULT, DEFAULT)
WHERE
      action_id = 'SL'
  AND
       database_name = 'ACMEDB';

La solución por defecto

ApexSQL Audit es una herramienta de cumplimiento de normas u auditoría SQL Server que rastrea y reporta eventos en SQL Server auditando lo accesos y cambios en la instancia SQL Server y sus objetos. Provee un rango de reportes integrados, junto con el diseñador de reportes personalizados para crear reportes de auditoría de SQL Server personalizados usando una técnica de arrastrar y soltar.

Para auditar la ejecución de sentencias SELECT en una base de datos específica en múltiples instancias SQL Server:

1. Inicie el GUI de ApexSQL Audit

2. Seleccione la instancia SQL Server (LENOVO), seleccione la base de datos (ACMEDB) que desea auditar para las sentencias SELECT y seleccione la opción Query
   Adicionalmente, seleccione las tablas que pueden ser parte de una sentencia SELECT auditada – si usted no desea auditar sentencias SELECT en tablas específicas, usted puede des-seleccionarlas para preservar el espacio HDD ocupado por la base de datos de repositorio central de ApexSQL. Note que todos los elementos en el panel Objects están seleccionados por defecto

3. Seleccione otra instancia de SQL Server y repita los pasos 1 y 2 con bases de datos y tablas apropiadas.

   

4. Confirme su selección seleccionando la opción Apply en el mensaje amarillo

   

Los reportes integrados de ApexSQL Audit que pueden proveer reportes con sentencias SELECT capturadas son Complete audit trail, Daily activity, Access history per user, Access history per object, Access history per application, y Access history per host. La principal diferencia entre estas plantillas de reportes son los campos de parámetros definidos usados para filtrar la información capturada.

Para ver las sentencias SELECT capturadas, haga clic en el botón View reports en la barra de herramientas principal de ApexSQL Audit, y use cualquiera de los reportes listados previamente.

En el proceso de auditar sentencias SELECT ApexSQL Audit provee:

• Configuración y establecimiento de auditoría sólo apuntando y haciendo clic
• Auditoría y reportes sin el uso o conocimiento de T-SQL
• Soporte para todas las ediciones de SQL Server, excepto la edición SQL Server Express
• Monitoreo y grabación automáticos de acciones de usuarios que incluyen la ejecución de sentencias SELECT, sin importar el rol SQL al que pertenece el usuario
• Identificación de riesgos de seguridad e impacto en el desempeño a través de reportar todas las sentencias SELECT en su forma T-SQL original
• Un repositorio de auditoría centralizado con la característica de poder archivar
• Reportes evidentes, precisos y exhaustivos de manipulación para revisiones