Partager via

Auditoría de Seguridad en SQL Server 2008

  • Article

Esta semana mientras trabajaba en Bolivia escuché al personal de seguridad de un cliente discutir sobre las opciones que podían utilizar para auditar las transacciones realizadas a las tablas de una base de datos. Los interrumpí y les pregunté si habían escuchado a cerca de la configuración de auditorías en SQL Server 2008 y me dijeron que no. Así que aproveché el tema para escribir el siguiente artículo.

La primera pregunta que nos surge es: ¿Porque auditamos?

Revisando la literatura de auditoría de seguridad, los autores coinciden en que el tener políticas de seguridad es un factor crítico para mantener los datos seguros. La auditoría también nos ayuda a identificar lo que accedió un intruso en el evento de que un ataque sea exitoso.

En SQL 2005 el enfoque estaba orientado a asegurarse de que el usuario no tuviera privilegio mayores a los que necesitaba y que los cambios fueran realizados por el personal autorizado. La herramienta que se promovía para auditar los cambios a la base de datos era el SQL Profiler ya que podía auditar cambios al esquema, operaciones de insertar, actualizar o eliminar registros; y eventos relacionados con cambios de permisos o la creación de nuevos Logins.

Una de las mejoras que trajo SQL Server 2008 Enterprise es un incremento en la capacidad de auditoría a través del uso del SQL Server Audit. A través de esta funcionalidad se puede rastrear y registrar de forma automática los eventos que ocurren a nivel del servidor o a nivel de la base de datos. Esto es posible a través del uso del objeto Audit. Veamos entonces como crear una auditoría, como crear y habilitar una especificación de auditoría a nivel de Servidor o a nivel de base de datos y como visualizar los registros de auditoría.

Crear una Auditoría

Un objeto de auditoría es una colección de una o más acciones individuales o un grupo de acciones que podrán ser rastreadas. Por ejemplo, se puede configurar un objeto de auditoría para identificar todos los logins fallidos. Los eventos se escriben en la localización que se especifique. Se pueden almacenar en un archivo, la bitácora de eventos de aplicaciones o la bitácora de eventos de seguridad.

El objeto de auditoría se puede crear a través del Management Studio (SSMS) o utilizando T-SQL. Desde SSMS se debe presionar el botón de la derecha del mouse sobre la opción New Audit localizada en la carpeta de auditoría bajo el árbol de Seguridad, como se muestra a continuación:

En la pantalla Create Audit se debe ingresar el nombre del objeto de auditoría, y se debe especificar el destino. Si se indica que el destino será un archivo, hay que especificar la ruta donde será almacenado. Finalmente se debe presionar el botón OK para crear el objeto de auditoría.

Para propósitos de este ejemplo cree un segundo objeto de auditoría e ingrese el nombre “AuditarConsultasTablaEmpleado”. Seleccione como destino un archivo e indique la ruta donde desee almacenarlo. Ambos objetos estarán localizados bajo la carpeta Audits, como se muestra a continuación. Este objeto de auditoría se utilizará para rastrear las transacciones SELECT realizadas contra la tabla HumanResources.Employee de la base de datos AdventureWorks2008R2.

Si desea crear un objeto de auditoría a través de T-SQL lo puede hacer utilizando el comando CREATE SERVER AUDIT. La siguiente consulta crea el objeto “AuditarConsultasTablaEmpleado”. Esta operación fue realizada anteriormente a través de SSMS.

USE master

CREATE SERVER AUDIT [AuditarConsultasTablaEmpleado]

TO FILE  (FILEPATH = N'C:\TEMP');

Crear y habilitar una especificación de auditoría a nivel de Servidor

Una vez se ha creado los objetos de auditoría, el siguiente paso es crear las especificaciones apropiadas de auditoría. Las especificaciones de auditoría le indican al objeto de auditoría lo que debe rastrear. En el caso del objeto de auditoría llamado “AuditarLoginsFallidos”, debemos crear una especificación que busque los logins que no son exitosos. Para lograrlo debemos seleccionar el botón de la derecha del mouse sobre la carpeta “Server Audit Specifications” ubicada bajo el árbol de Seguridad.

Asigne a la especificación de auditoría el nombre: “EspecificacionAuditoriaServidor-LoginsFallidos”. Bajo audit seleccione la opción “AuditarLoginsFallidos”. Esto asignará la especificación de auditoría “EspecificacionAuditoriaServidor-LoginsFallidos” al objeto de auditoría “AuditarLoginsFallidos”. Seleccione el tipo de acción para auditar: “FAILED_LOGIN_GROUP” y presione OK para crear y asignar el objeto de auditoría.

Presione el botón de la derecha del mouse sobre “EspecificacionAuditoriaServidor-LoginsFallidos” para habilitar la especificación a través de la opción: “Enable Server Audit Specification”

Finalmente debe habilitar el objeto de auditoría presionando el botón de la derecha del mouse sobre “AuditarLoginsFallidos” para habilitar la auditoria a través de la opción Enable Audit como se muestra en la siguiente figura.

Si desea crear una especificación de auditoría a nivel de servidor, a través de T-SQL, lo puede hacer utilizando el comando CREATE SERVER AUDIT SPECITICATION. La siguiente consulta crea la especificación de auditoría “EspecificacionAuditoriaServidor-LoginsFallidos”. Esta operación fue realizada anteriormente a través de SSMS.

USE master

CREATE SERVER AUDIT SPECIFICATION [EspecificacionAuditoriaServidor-LoginsFallidos]

FOR SERVER AUDIT [AuditarLoginsFallidos]

ADD (FAILED_LOGIN_GROUP) WITH (STATE = ON)

GO

Crear y habilitar una especificación de auditoría a nivel de base de datos

Para crear y habilitar una especificación de auditoría a nivel de base de datos debe expandir la base de datos, en este ejemplo utilice AdventureWorks2008R2, y seleccione con el botón de la derecha del mouse la opción “Audit Specifications” bajo el árbol de seguridad de la base de datos. Seleccione la opción “New Database Audit Specification” y asigne un nombre (para propósito de este ejemplo asignaremos el nombre: “EspecificacionAuditoriaDB-ConsultasTablaEmpleado”)

Como se muestra en la siguiente figura, seleccione la opción “AuditarConsultasTablaEmpleado” Bajo audit. Esto asignará la especificación de auditoría “EspecificacionAuditoriaDB-ConsultasTablaEmpleado” al objeto de auditoría “AuditarConsultasTablaEmpleado”.

Nota: La clase de objeto se utiliza para indicar lo que se quiere auditar. Las opciones son: Objeto, Base de datos o Esquema. Seleccione objeto para auditar Tablas, Funciones, Procedimientos Almacenados o Vistas. El nombre del Principal son entidades que pueden solicitar recursos de SQL Server.

Para propósitos de este ejemplo, seleccione “SELECT” en el tipo de acción para auditar; en la clase del objeto seleccione “OBJECT”; en el nombre del objeto ingrese [HumanResources].[Employee]; en el nombre del Principal ingrese [public] y presione OK para crear y asignar el objeto de auditoría.

Esto permite rastrear las consultas con SELECT que realizan todos los usuarios a la tabla [HumanResources].[Employee].

Presione el botón de la derecha del mouse sobre “EspecificacionAuditoriaDB-ConsultasTablaEmpleado” para habilitar la especificación a través de la opción: “Enable Database Audit Specification”

Finalmente debe habilitar el objeto de auditoría presionando el botón de la derecha del mouse sobre “AuditarConsultasTablaEmpleado” para habilitar la auditoria a través de la opción Enable Audit como se muestra en la siguiente figura.

Si desea crear una especificación de auditoría a nivel de base de datos, a través de T-SQL, lo puede hacer utilizando el comando CREATE DATABASE AUDIT SPECITICATION. La siguiente consulta crea la especificación de auditoría “EspecificacionAuditoriaDB-ConsultasTablaEmpleado”. Esta operación fue realizada anteriormente a través de SSMS.

USE master

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpec-EmployeesTable]

FOR SERVER AUDIT [Audit-EmployeeQueries]

ADD (SELECT ON OBJECT::[HumanResources].[Employee] BY [public])

WITH (STATE = ON)

GO

Visualizar los registros de auditoría

Los registros de auditoría pueden ser accedidos a través la opción “View Audit Logs” o a través de la bitácora de eventos de aplicaciones o de seguridad, dependiendo de dónde se especificó que se almacenaría la auditoría.

Los resultados pueden ser filtrados o inclusive hasta exportados en los siguientes formatos: log, csv y txt

Conclusión

La auditoría en SQL 2008 Enterprise es muy poderosa y flexible ya que permite crear auditorías a nivel de servidor o a nivel de base de datos. La configuración es sencilla ya que solo se requiere especificar donde se almacenará la auditoría (en un archivo, la bitácora de eventos de aplicaciones o la bitácora de eventos de seguridad), que objeto se desea auditar (Base de datos, Esquema, Tablas, Funciones, Procedimientos Almacenados o Vistas) y para cual Principal (entidades que pueden solicitar recursos de SQL Server).

Comments

  • Anonymous
    January 01, 2003
    Buen Dia Luciano, si podras ver Usuario y Workstation, entre otros. Saludos!

  • Anonymous
    January 01, 2003
    Todas las ediciones de SQL Server admiten auditorías en el nivel de servidor.  Las auditorías de nivel de base de datos se limitan a las ediciones Enterprise, Developer y Evaluation.

  • Anonymous
    January 01, 2003
    Hola Luciano, el Login y Usuario lo puedes ver en el Audit Log (http://msdn.microsoft.com/en-us/library/cc280545.aspx), la Máquina o IP si dependera de que estes auditando, por ejemplo si auditas Logins Fallidos en el Statement podras ver la IP de la maquina que se intent hacer la session fallida.
    Saludos!

  • Anonymous
    January 01, 2003
    Ya que SQL Audit está basado en Extended Events usa muy pocos recursos mientras hace la recolección de datos, por tanto no produce un overhead considerable. Sin embargo la cantidad de overhead se verá ligado a: Que tipo eventos estas auditando, ocurren con una frecuencia muy elevada, es adecuado auditar solo lo necesario. Tambien donde estas almacenando el Log de Eventos, Lo más adecuado sería en un disco independiente, nunca en uno donde existan archivos de bases de datos de SQL.

  • Anonymous
    January 01, 2003
    Hola Santiago, Si puedes hacerlo, solo debes seleccionar en el OBJECT CLASS = DATABASE y luego seleccionas las base de datos deseada. Pero ten encuenta que cada click que hagas en SSMS es un SELECT a las tablas del systema, deberas encontrar un Buena manera de filtrar tus resultados. Saludos!

  • Anonymous
    January 01, 2003
    Hola Chica10, Para hacer seguimientos a cambios DML tu unica opcion es auditor ya sea usando SQL Audit (La major opcione) o un SQL Trace o C2 Compliant Audit. Sin embargo cambios DDL se guardaran en el Default Trace, pero este trace se sobre escribe con el tiempo. Saludos!

  • Anonymous
    January 01, 2003
    Hola Victor, voy a necesitar un poco mas de informacion de como esta configurada tu aduditoria. Saludos!

  • Anonymous
    January 01, 2003
    Buen Dia Mem, asegurate que la Edicion de SQL Server sea Enterprise, Standart Edition no soporta Auditoria en SQL 2008. Saludos!

  • Anonymous
    January 01, 2003
    En el blog se hace referencia a Auditoria a nivel de Servidor (Ejm: Logins y Logouts, cambios de roles o permisologia a nivel de la instancia) y tambien a Auditoria a nivel de la Base de Datos (ejm: Auditar el Acceso a una table o eliminar algun procedimiento). Tambien es posible hacer auditoria usando triggers, pero lo mas adecuado es crear un objeto de Auditoria a Nivel de base de datos, ya que te dara mayor flexibilidad y major rendimiento.

  • Anonymous
    January 01, 2003
    Muchas Gracias! Asi será :)

  • Anonymous
    November 01, 2012
    Hola, buen dia. Solamente me gustaria manifestar que este post esta buenisimo, es my facil de enteder y poner en practica, ademas de ser un tema que casi no se encuentra el la red. Saludos, espero puedas seguir escribiendo post tan buenos como este.

  • Anonymous
    February 26, 2013
    No afecta la performance del servicio ??

  • Anonymous
    July 05, 2013
    estimados, la auditoria de SQL Server 2008 bajo que versión se puede utilizar ???

  • Anonymous
    July 10, 2013
    La que señalas en tu blog es a nivel de BD cierto ??? como seria una a nivel de Servidor o a que te refieres con "a nivel de servidor?? Estuve viendo y se podía auditar tablas pero con Trigger, hay algo mas fácil ??? Saludos.

  • Anonymous
    November 18, 2013
    Hola SQL PFE LATAM buenas tardes, mira seguí tu post y realice una prueba ejecutando un SP que actualiza un campo de stado, pero no veo los valores solo veo la instrucción UPDATE y las variables. Te adjunto lo que te comento: Database Name dbAdministracion Schema Name dbo Object Name Cliente Statement UPDATE dbo.Cliente SET estadoactivo = @estadoactivo WHERE Cliente_id = @Cliente_id

  • Anonymous
    November 19, 2013
    Hola SQL PFE LATAM, y para auditar los accesos a tablas o cambios como se hace ?? mediante trigger ?? tengo la versión STD. Saludos.

  • Anonymous
    November 25, 2013
    Hola Jam, Esto es una configuracion de diseño, existe un enlace de feedback donde puedes votar para cambiar esto en proximas versions de SQL: connect.microsoft.com/.../sql-2008-audit-record-for-stored-procedure-is-not-parameterized Saludos! Edinson Medina

  • Anonymous
    November 25, 2013
    Hola Epic, Si posees la version Standard de SQL 2008, Si, solo podras hacer auditorias a nivel de objetos de bases de datos con un trigger, aca hay un post que te puede ayudar, www.mssqltips.com/.../keeping-your-sql-server-ddl-trigger-audit-table-in-check Saludos!

  • Anonymous
    May 20, 2014
    Buenas tardes, estoy tratando de auditar los eventos DML un usuario de dominio que se conecta a la instancia y no me lo permite, cual será el problema

  • Anonymous
    May 28, 2014
    Estoy tratando de registrar todos los insert , update y delete que se realizan sobre todas las tablas de un db. Esto es posible? o hay que agregar select para tablas, update para tablas, delete para tablas. No me permite agregar una accion select para un db. Saludos

  • Anonymous
    June 05, 2014
    Gracias, lo solucione haciendolo por codigo, del diseñador no me deja.. gracias por la ayuda

  • Anonymous
    August 07, 2014
    The comment has been removed

  • Anonymous
    September 02, 2014
    Si no se encuentra activa la opcion de audit no existe otra forma de saber los insert delete updates de un usuario sobre una tabla específica de la bd. GRACIAS!

  • Anonymous
    October 22, 2014
    Estimado te felicito por tu post...
    mi consulta es: se puede ver que usuario hicieron las modificaciones y desde que maquina hicieron dichas modificaciones..Saludos

  • Anonymous
    October 31, 2014
    Y como se puede ver eso doctor... porque según las indicaciones que has enseñado al momento de reportar no se observa el usuario ni menos desde que maquina se ejecuto la consulta....

  • Anonymous
    March 06, 2015
    Hola, al tratar de implementar la auditoria encontre que dentro de Security solo me muestra Logins, Server Roles, Credentials, pero no me aparece Audit. Sera que algo no se instalo o hay algo que debe hacer para habiliar las opciones de auditoria.
    saludos.

  • Anonymous
    September 07, 2015
    Hola, me gustaría saber si en la versión 2008 se puede verificar si se cambio la contraseña de un inicio de sesión, gracias.

  • Anonymous
    September 11, 2015
    Hola Luis Ortiz, Es probable que no tengas los permisos adecuados.

  • Anonymous
    September 11, 2015
    Hola Ulises Baron, si puedes auditarlo usando el Server-Level Audit specification LOGIN_CHANGE_PASSWORD_GROUP (https://technet.microsoft.com/en-us/library/cc280663(v=sql.100).aspx)

  • Anonymous
    September 16, 2015
    Que tal, quisiera que me ayudaran porfavor sobre la auditoria, veran al verificar lo que ha registrado la auditoria, resulta que la hora que muestra no es la misma a la hora que se efectuo dicha accion, porfavor si fueran tan amables de poder ayudarme es urgente

  • Anonymous
    October 19, 2015
    A mi me pasa, exactamente lo mismo, la hora que me muestra la auditoria, es 2 horas menos que la hora en la que se a realizado realmente.

    Agradecería mucho si me ayudaran a solucionarlo, o por lo menos me explicaran a que se debe.

    Un saludo y gran articulo.

  • Anonymous
    October 19, 2015
    Hola Anthony, Cesar. El timestamp de la auditoria es un 64 bit integer generado del tiempo en la zona horaria UTC, es por esto que si estan en zonas horarias diferentes a UTC deberan restar o sumar la diferencia. Saludos!https://msdn.microsoft.com/en-us/library/cc280649.aspx

  • Anonymous
    November 26, 2015
    que query puedo usar para habilitar una auditoria? y y donde se guarda al momento de habilitarla?
    gracias por su apoyo

    Muy buen post!!!!!

  • Anonymous
    November 30, 2015
    The comment has been removed