Introducción
Con el advenimiento de la Era de la Información, los datos están siendo recolectados en una escala masiva. Los sistemas de Tecnologías de la Información han hecho al acceso a estos datos más rápido y fácil. También ha hecho más fácil el mal uso de los datos.
Todos hemos oído de instancias donde empleados de hospital dieron un vistazo al registro médico de una celebridad. En muchos casos, estos empleados de hospital tienen razones legítimas para acceder a la información del paciente, lo que significa que su acceso no puede ser revocado o, en algunos casos, incluso restringido, sin estorbar su habilidad de realizar sus tareas eficientemente.
Esta es sólo una de una plétora de razones porque los gobiernos están implementando requerimientos de auditoría estandarizados como HIPAA, SOX, PCI, GLBA, FERPA y Basel.
Si no podemos prevenir que la gente acceda a los datos, necesitamos hacer seguimiento a cómo están siendo usados. Eso nos permitirá investigar cualquier actividad sospechosa para determinar si ha ocurrido una infracción y la naturaleza de la infracción, lo cual nos permitirá tomar la acción apropiada.
Para este fin, Microsoft ha añadido la característica Auditing de SQL Server 2008 adelante.
Auditar SQL Server previo a 2008
Antes de SQL Server 2008, la auditoría tenía que ser hecha usando una combinación de características como:
- Auditoría de Inicio de Sesión y auditoría C2
- Desencadenadores y notificaciones de eventos
- Seguimiento de SQL podría ser usado en conjunto con SQL Profiler
Utilizar las características mencionadas anteriormente para propósitos de auditoría puede ser bastante incómodo, ya que involucra una cantidad significativa de configuración. Los datos acumulados por estos métodos son registrados en diferentes maneras a una variedad de localizaciones, lo cual lo hace más difícil de asimilar. El impacto potencial en el desempeño puede ser también asociado con algunas de estas acciones que lo hacen menos deseable.
Auditando en de SQL Server 2008 en adelante
SQL Server Audting es una nueva característica que hace uso de eventos extendidos para permitirle auditar todo lo que pasa en su servidor, desde cambios en la configuración del servidor hasta quién modificó un valor en una tabla específica de la base de datos. Esta información es luego escrita en el registro de seguridad de Windows, el registro de la aplicación Windows o un archivo plano.
En SQL Server 2008, Auditing era una característica exclusiva de la edición Enterprise. En SQL Server 2012, la auditoría de servidor ha sido hecha disponible para todas las ediciones. De todas maneras, la auditoría de base de datos permanece para uso por parte de cliente de la edición Enterprise solamente.
Eventos Extendidos
Los eventos extendidos son una arquitectura altamente configurable usada para manejar eventos que están ocurriendo en SQL Server. Los eventos extendidos están integrados en el código de SQL Server, y como tales tienen un impacto mínimo en el desempeño.
Los eventos extendidos hacen uso de paquetes para agrupar objetos. Uno de estos paquetes es SecAudit, el cual es usado por SQL Audit. Los eventos en este paquete son privados y usados internamente por la característica SQL Audit. Desafortunadamente este paquete no es accesible, así que ninguno de sus objetos está disponible externamente.
Componentes de Auditoría
La característica SQL Server Audit engloba tres componentes principales:
- Auditoría de Servidor
- La Especificación de Auditoría de Servidor
- La Especificación de Auditoría de Base de Datos
Auditoría de Servidor
La Auditoría de Servidor es el componente padre de una auditoría SQL Server y puede contener Especificaciones de Auditoría de Servidor y/o Especificaciones de Auditoría de Base de Datos.
La Auditoría de Servidor reside en la base de datos master y es usada para definir dónde se almacenará la información de auditoría, la política de archivos de seguimiento, el retraso de colas y cómo SQL Server debería reaccionar en caso de que una auditoría no sea posible.
En la configuración de la auditoría lo siguiente es configurado:
- El nombre de la Auditoría de Servidor.
- El retraso de colas que es la máxima cantidad en milisegundos que el sistema puede esperar antes de procesar cualquier auditoría. Básicamente una auditoría puede ser procesada síncronamente o asíncronamente. Para procesar síncronamente, establezca el retraso de colas a 0. Para un procesamiento asíncrono, el valor más bajo posible es 1000 milisegundos.
- La acción a tomar en el evento de que el registro de auditoría esté no disponible por alguna razón.
Las opciones son:- Continuar e ignorar el problema del registro.
Apagar el servidor
Puede parecer muy severo apagar el servidor no es posible escribir el registro de auditoría. Pero todo se reduce a cuán importante es que suceda la auditoría. ¿Es más importante tener la ruta de auditoría completa o es más importante que la base de datos permanezca en línea? Apagar el servidor es uno de los requerimientos del cumplimiento común de requerimientos.
Para poder configurar esta opción, el usuario que crea o modifica la Auditoría necesita tener permisos SERVER SHUTDOWN.
Fallar la operación
Esta es una buena alternativa a apagar el servidor enteramente. El servidor permanecerá en línea, pero si toma lugar una acción que requiere auditoría, la acción fallará si el objetivo no está disponible, asegurando ninguna información de auditoría es perdida en transacciones que necesitan ser auditadas.
En SQL Server 2012 la auditoría se ha vuelto más robusta permitiendo ahora a SQL Audit recuperarse si el objetivo está temporalmente no disponible.
El destino de la auditoría
El registro puede hacerse a:
Archivo
El enfoque recomendado es almacenar los registros de auditoria en una localización de red fuera del servidor.
El nombre usado del archivo es automáticamente generado por SQL Server. Esto es hecho para asegurar que los nombres de archivos sean siempre únicos. El nombre del archivo de auditoría es hecho de lo siguiente:
- El nombre de la Auditoría
- El GUID de la Auditoría
- El Número de Partición
- Sello de tiempo
- Extensión del archivo
Registro de seguridad
Uno de los requerimientos de la mayor parte de las regulaciones es que los datos auditados en sí mismos tienen que ser asegurados. Esto se puede lograr en una variedad de formas, pero típicamente el acceso al registro de seguridad es más restringido que el acceso al registro de la aplicación, y como tal ofrece una buena manera de mantener segura la información registrada.
Registro de la aplicación
Tenga en mente que el ajuste por defecto para el registro de la aplicación es sobrescribir eventos cuando alcanza el tamaño máximo. Esto podría resultar en que se pierda información de auditoría.
- La ruta del archivo a especificar si la opción previa seleccionada fue registrar a un archivo.
- El límite del tamaño y número de archivos de auditoría.
El máximo número de archivos de auditoría a ser usados sin retrotraer.
En SQL Server 2008 sólo era posible establecer el número de archivos a tener en adición al archivo actual antes de empezar a retrotraer. Una opción adicional ha sido añadida a SQL Server 2012 para permitir a los DBAs especificar el número de archivos de autoría sin arriesgarse a que los datos se sobrescriban cuando se empieza a retrotraer.
Si reservar o no espacio de disco específicamente para los registros de auditoría.
Una Auditoría de Servidor es automáticamente asignada al GUID de identificación única. Este GUID puede ser explícitamente asignado. Este GUID es estático y no puede ser cambiado después de que la auditoría ha sido creada.
En SQL Server 2012, la auditoría ahora también permite especificar un filtro. Esto es básicamente una cláusula WHERE para la auditoría, la cual es evaluada antes de que un evento de auditoría sea escrito al objetivo de auditoría. Esto es aplicado para todas las especificaciones de auditoría enlazadas a una auditoría. Cualquiera de los campos retornados por la función sys.fn_get_audit_file excepto file_name y audit_file_offset puede ser usado como una expresión de filtro.
Una auditoría puede ser creada ya sea usando SQL Server Management Studio, usando Transact SQL o SQL Server Management Objects (SMO).
En SQL Server Management Studio una auditoría puede ser creada bajo el nodo Audits, el cual reside debajo del nodo Security en Object Explorer.
Una Auditoría también puede ser creada usando el comando de Transact SQL CREATE SERVER AUDIT.
Importante: Todas las auditorías y especificaciones de auditoría son creadas en un estado deshabilitado. Habilitar una auditoría no habilita automáticamente todas las especificaciones de auditoría enlazadas a ella. Cada especificación de auditoría necesita ser habilitada individualmente. Una auditoría o especificación de auditoría no puede ser modificada cuando está habilitada, primero necesita ser deshabilitada, luego modificada y rehabilitada.
Tanto la auditoría como la especificación de auditoría necesitan ser habilitadas para que un evento sea registrado.
Permisos requeridos:
Para usar CREATE, ALTER o DROP con una auditoría, el usuario requiere el permiso ALTER ANY SERVER AUDIT. Esto está también incluido en el permiso CONTROL SERVER.
Para escribir a una localización de archivo, la cuenta del servicio de SQL Server necesitará tener permisos de escritura en la carpeta compartida de red. Para leer el archivo, todos los usuarios que pertenecen al rol Audit Reader y Audit Administrators necesitan tener permisos de lectura en esa carpeta compartida también.
Seguridad adicional es requerida cuando se está escribiendo al Registro de Seguridad de Windows, esto se trata más adelante en este artículo.
Especificaciones de Auditoría
Las especificaciones de auditoría pueden tener 3 categorías de acciones:
- Acciones a niel de servidor
- Acciones a nivel de base de datos o
- Acciones a nivel de auditoría, las cuales auditan acciones en el proceso de auditoría en sí mismo. Algunas acciones de auditoría son automáticamente auditadas, como cambiar el estado de una auditoría a apagado o encendido.
Algunas acciones pueden ser auditadas individualmente, como auditar un evento seleccionado en una tabla. A esto se le llama Acciones de Auditoría.
En la mayor parte de los casos, las acciones de auditoría están agrupadas juntas, resultando en Grupos de Acciones de Auditoría. Esto facilita la configuración de especificaciones de auditoría, dado que las acciones que forman una unidad lógica están incluidas en un solo grupo, ahorrándole tener que especificar cada una individualmente.
La Especificación de Auditoría de Servidor
La Especificación de Auditoría de Servidor, la cual está disponible en todas las ediciones de SQL Server, es usada para definir qué necesita ser auditado al nivel del servidor.
La Especificación de Auditoría de Servidor se encuentra debajo del nodo Security en SQL Server. Sólo puede haber una Especificación de Auditoría de Servidor por auditoría.
Para crear una Especificación de Auditoría de Servidor, tres cosas necesitan ser especificadas:
- El Nombre de la especificación de auditoría. Esto es opcional, un nombre por defecto será asignado si usted no ingresa uno.
- La Auditoría de Servidor que define el objetivo al que los eventos seleccionados deberían ser registrados.
El Tipo de Acción de Auditoría. Esto es los eventos que deberían ser auditados.
Para la Especificación del Servidor, todos los eventos son agrupados en Grupos de Acciones de Auditoría. Los siguientes son ejemplos de grupos de acciones de Auditoría a Nivel de Servidor:
SUCCESSFUL_LOGIN_GROUP
FAILED_LOGIN_GROUP
DBCC_GROUP
La lista completa de Grupos de Acciones de Auditoría a Nivel de Servidor puede ser encontrada aquí: Audit Actions and Audit Action Groups
Cuando una Especificación de Auditoría de Servidor es creada vía SSMS, está deshabilitada por defecto. Cuando se lo crea con T-SQL, hay un parámetro opcional para crearlo en un estado habilitado.
Permisos requeridos:
Para crear una Especificación de Auditoría de Servidor, el usuario necesita tener permiso para conectarse a la base de datos y tener ALTER ANY SERVER AUDIT. El permiso CONTROL SERVER permite a la auditoría ser vista por el usuario.
La Especificación de Auditoría de Base de Datos
La Especificación de Auditoría de Base de Datos audita eventos a nivel de base de datos. Usar auditoría más granular puede minimizar el impacto en el desempeño de su servidor. Esto puede ser hecho usando una Especificación de Auditoría de Base de Datos, la cual desafortunadamente está disponible sólo en la edición Enterprise. Usando la Especificación de Auditoría de Base Datos, la auditoría puede ser hecho a nivel de objeto o usuario.
Desafortunadamente, no puede ser hecha a nivel de columna aún.
La Especificación de Auditoría de Base de Datos es creada debajo del nodo Security en la base de datos relevante.
También puede ser creada con Transact SQL y SMO.
Lo siguiente necesita ser configurado para crear una Especificación de Auditoría de Base de Datos:
- El nombre de la Especificación de Auditoría de Base de Datos. Un nombre por defecto será asignado si ninguno es provisto.
- La Auditoría de Servidor a la que la especificación tiene que ser enlazada.
El Tipo de Acción de Auditoría. Hay Acciones de Auditoría y Grupos de Acciones de Auditoría que pueden ser seleccionados en este campo.
INSERT y UPDATE son algunas de las Acciones de Auditoría que pueden ser seleccionadas en este campo.
Una lista completa de Acciones de Auditoría y Grupos de Acciones de Auditoría aplicable a la Especificación de Auditoría de Base de Datos puede ser encontrada aquí: Audit Actions and Audit Action Groups
- El Nombre de Objeto del objeto a ser auditado cuando una Acción de Auditoría haya sido seleccionada.
- El Esquema del objeto seleccionado.
- El nombre de la Entidad de Seguridad. Para auditar todos los usuarios, use la palabra reservada public en este campo.
- ID de evento definido por el usuario
Este es un id especificado por el usuario, el cual es escrito a la columna user_defined_event_id del registro de auditoría. El tipo de dato es smallint. - Éxito
Indica si el procedimiento tuvo éxito en escribir al registro de auditoría. El tipo de dato es bit. - Información definida por el usuario
Este es un parámetro opcional que permite al usuario especificar información adicional respecto del evento. Esta información es registrada a la columna user_defined_information del registro de auditoría. El tipo de dato es nvarchar(4000). Patrón de archivo
Esto es un nvarchar(260). La ruta o la ruta y el nombre del archivo del archivo a leer debería ser provisto. Para leer todos los archivos en una carpeta, especifique la ruta a la carpeta usando el asterisco (*) como comodín. Si un archivo inválido es especificado, el mensaje de error MSG_INVALID_AUDIT_FILE será mostrado.
Nombre de archivo inicial
Esta es la ruta y el nombre de archivo del archivo en un conjunto de auditoría donde debería empezar la lectura. El tipo de dato es nvarchar(260).Desplazamiento de registro de auditoría
Esto puede ser usado para especificar la localización de inicio y el archivo inicial. El tipo de dato es bigint.- Escriba los registros de auditoría a una carpeta compartida de un servidor de archivos en un servidor diferente al cual sysadmin no tiene ni siquiera permiso. Sólo otorgue permisos al auditor.
- Escribir al registro de Seguridad de Windows es también una buena alternativa. SI desea escribir al registro de Seguridad de Windows, usted necesitará hacer lo siguiente:
Añada la cuenta del Servicio de SQL Server a la política Generate Security Audits en su Edit Group Policy Editor.
Cambie la política de Acceso a Objetos de Auditoría para incluir Success y Failure.
Tenga en mente que cuando se escribe a los registros de Windows, la política de auditoría de Windows podría potencialmente causar que se pierdan datos de auditoría. Los registros de Windows usualmente pueden comenzar a sobrescribir eventos más antiguos, lo cual podría causar que se pierdan algunos datos de la Auditoría de SQL.
En Windows 8 el complemento es llamado gpedit.msc. Para accederlo, usted necesita escribir gpedit.msc en la caja de búsqueda. Recuerde incluir la extensión .msc o puede no encontrarlo.
Desafortunadamente, si usted sólo tiene la edición básica de Windows 8, puede que no pueda acceder a esta aplicación.
- Escriba registros de auditoría a una localización centralizada.
- Para facilitar el procesamiento de datos auditados, cargue los registros en la base de datos.
- Use un archivo como un objetivo para desempeño óptimo.
- Use a auditoría dirigida para minimizar los datos recolectados y mejorar el desempeño.
- Cuando se esté escribiendo a los registros de Windows, asegúrese de que la política de sustitución incremental de los Registros de Windows coincide con la de su estrategia de auditoría.
- Definición y descripción general interbloqueo de SQL Server - December 16, 2019
- 5 consejos prácticos de Transact-SQL que puede usted conozca (o no) - December 24, 2016
- Creando una estrategia de auditoría exitosa para sus bases de datos SQL Server - October 29, 2016
Incluso cuando SQL Server le permitirá especificar una acción de auditoría en objetos dentro del ámbito del servidor, como vistas de sistema, los objetos no serán auditados, pero no se levantará ningún mensaje de error tampoco.
Si desea auditar objetos dentro del alcance del servidor, usted necesita crear una especificación de auditoría de base de datos en la base de datos master.
Permisos requeridos:
Para crear una especificación de auditoría de base de datos, un usuario necesita tener permiso para conectarse a la base de datos y tener los permisos ALTER ANY DATABASE AUDIT SPECIFICATION o ALTER o CONTROL para la base de datos que quisieran añadir a la auditoría.
Eventos de auditoría definidos por el usuario
Una de las nuevas características de 2012 es la habilidad de crear Eventos de Auditoría Definidos por el Usuario. Los eventos de auditoría definidos por el usuario pueden ser usados para integrar aplicaciones de terceros a SQL Server Audit.
Un evento de auditoría definido por el usuario es creado usando el procedimiento sp_audit_write. Este procedimiento acepta 3 parámetros:
Para que un evento definido por el usuario sea auditado, USER_DEFINED_AUDIT_GROUP necesita ser seleccionado para auditoría ya sea en la especificación de auditoría de la base de datos o el servidor.
SI esto no ha sido seleccionado como un evento de auditoría, todos los eventos generados por el procedimiento sp_audit_write serán ignorados.
Leyendo datos del archivo de auditoría
Cuando la información de auditoría es escrita a un archivo destino, esto es hecho en binario. La función de valores de tabla fn_get_audit_file() necesita ser usada para leerlo.
Esta función acepta 3 parámetros:
Ejemplo:
|
1 |
SELECT * FROM fn_get_audit_file('D:\Audits\*',default,default) |
El archivo puede contener cualquiera de los 26 elementos disponibles. Una lista completa de los elementos disponibles puede ser encontrada aquí: SQL Server Audit Records
La información de auditoría escrita al Registro de Seguridad de Windows o el Registro de la Aplicación puede ser leída usando el visor de eventos. Esta información puede ser también leída a través de SQL Server Management Studio expandiendo el nodo Security, luego expandiendo el nodo Audit, haga clic derecho en una Auditoría y seleccione la opción View Audit Logs
Asegurando los registros de auditoría
Los registros de auditoría en sí mismos necesitan ser protegidos de accesos no autorizados y modificaciones.
Hay dos maneras de incrementar la seguridad de los registros de auditoría:
Reiniciando un SQL Server después de un apagado forzado
SI un SQL Server fue apagado por la Auditoría de SQL, no se iniciará normalmente. Necesita ser reiniciado en modo de un solo usuario usando la bandera –m. Alternativamente, puede ser también iniciado en modo de configuración mínima usando la bandera –f.
Esto luego permitirá al DBA a hacer modificaciones a la auditoría si es requerida.
SQL Server escribirá el mensaje de error MSG_AUDIT_SHUTDOWN_BYPASSED al registro de errores si la auditoría fue esquivada de esta manera.
Mejores prácticas
Conclusión
Auditoría de SQL Server es una característica poderosa, pero no debería ser usada sin una planificación cuidadosa. Para usar Auditoría exitosamente usted necesita tener una idea clara de qué desea alcanzar. ¿Qué acciones necesitan ser auditadas? ¿Quién necesita acceso a esta información? ¿Cómo será accedida? Una gran parte de una auditoría exitosa depende de cómo los datos de auditoría son almacenados, procesados y monitoreados.
En adición a la planificación, más trabajo puede ser requerido para crear reportes que pueden ser usados por auditores para dar sentido a esta información.
Referencias:
Ella es una creyente firme en el aprendizaje durante toda la vida y es una expositora regular en grupos de usuarios de SQL Server, compartiendo su pasión y conocimiento con cualquier persona que escuche.
Ver todas las entradas de Minette Steynberg