Auditoria en SQL Server

*AUDITORÍA EN SQL SERVER 2008 *

UNASAM-ING.SISTEMAS E INFORMÁTICA

2012-2«Año de la Inversión para el Desarrollo Rural y la Seguridad Alimentaria»

INTEGRANTES:

Patricio Tipian GessabelMamani Sánchez JessitNivin Águedo FrankAraoz Solis Emehely

DOCENTE:

Ing. Silva Zapata Miguel

1

Auditoria de base de datos en SQL Server 2008

INTRODUCCIÓNRevisando 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. Veremos 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.

Además a modo de complemento veremos algunas otras maneras de realizar una auditoria en SQL server, con ayuda de otras herramientas tales como el Idera Compliance Manag, así como la implementación de los triggers y creación de tablas extras para el almacenamiento de la información registrada y modificada de la base de datos, para mayor complemento en el curso de Administración de Base de datos.

Ing. Sistemas e Informática UNASAM Facultad de Ciencias

2


I. SQL SERVER AUDITAuditoría de una instancia de SQL Server o una base de datos de SQL Server implica el seguimiento y registro de eventos que ocurren en el sistema. Basándose en la información acumulada que sería capaz de rastrear los cambios en la base de datos, el acceso a la base de datos, etc. Una auditoría es la combinación de varios elementos en un solo paquete para un grupo específico de acciones de servidor o base de datos de acciones. Los componentes de SQL Server Audit se combinan para producir una salida que se llama una auditoría, así como una definición de informe junto con los gráficos y elementos de datos produce un informe. Auditoría de SQL Server utiliza extendido eventos para ayudar a crear una auditoría.

Si bien estamos trabajando con SQL Server 2008 de auditoría que tenemos que tener presentes cuatro cosas en mente:

1. SQL Server Audit2.Especificación de auditoría de servidor (Eventos para capturar en el nivel de instancia de servidor)3.Base de datos de auditoría pliego de condiciones (Eventos para capturar en una base de datos específica)4.Target (sería el caso de los eventos se registra)

El objeto de SQL Server Audit recoge una sola instancia de servidor o base de datos de las acciones a escala y los grupos de acciones para el seguimiento. La auditoría es a nivel de la instancia de SQL Server. Usted puede tener múltiples auditorías por cada instancia de SQL Server. Cuando se define una auditoría, se especifica la ubicación de la salida de los resultados. Este es el destino de auditoría. La auditoría se crea en un estado de movilidad reducida, y no de forma


3


automática todas las acciones de auditoría. Después de la auditoría está habilitada, el destino de la auditoría recibe los datos de la auditoría.

Especificación de auditoría de servidor

El objeto de auditoría de servidor Especificación pertenece a una auditoría. Puede crear una

especificación de auditoría de servidor por la auditoría, ya que ambos se crean en el alcance de

la instancia de SQL Server. La especificación de auditoría de servidor recoge servidor de muchos

grupos de acción a nivel nacional planteadas por el extendido Eventos característica. Usted

puede incluir a los grupos de acción de auditoría en una especificación de auditoría de servidor.

Los grupos de acción de auditoría son grupos predefinidos de acciones, que son los eventos

atómicos expuestos por el motor de base de datos. Estas acciones son enviados a la auditoría,

que registra en el objetivo.

Servidor de auditoría de los grupos de nivel de acción se describe en el tema de SQL Server

Grupos de auditoría de acciones y acciones.

Especificación de base de datos de auditoría

El objeto de auditoría de base de datos especificación también pertenece a una Auditoría de SQL

Server. Puede crear una especificación de auditoría de base de datos por base de datos de SQL

Server para la auditoría. La especificación de auditoría de base de datos de base de datos recoge

las acciones de auditoría de alto nivel planteada por el extendido Eventos característica. Puede

agregar cualquiera de los grupos de auditoría de la acción o los eventos de auditoría a una

especificación de auditoría de base de datos. Los sucesos de auditoría son las acciones atómicas

que pueden ser auditados por el motor de SQL Server. Los grupos de acción de auditoría son

grupos predefinidos de acciones. Ambos se encuentran en el alcance de la base de datos de SQL

Server. Estas acciones son enviados a la auditoría, que registra en el objetivo. Base de datos de

auditoría de los grupos de nivel de acción y las acciones de auditoría se describe en el tema de

SQL Server Grupos de auditoría de acciones y acciones.

Meta

Los resultados de una auditoría son enviados a un objetivo, que puede ser un archivo, el registro

de sucesos de seguridad de Windows, o el registro de sucesos de aplicación para Windows.

(Escribir en el registro de seguridad no está disponible en Windows XP). Registros deben ser

revisados y archivados periódicamente para asegurarse de que el destino tiene espacio

suficiente para escribir registros adicionales. Escribiendo en el registro de seguridad de Windows

requiere la cuenta de servicio de SQL Server que se añade a la Generar auditorías de seguridad.

Por defecto, el sistema local, Servicio Local y Servicio de red forman parte de esta política. Esta

configuración se puede configurar utilizando el complemento de la política de seguridad-en

(secpol.msc). Además, el acceso a objetos de auditoría de políticas de seguridad debe estar

habilitado para el éxito y el fracaso. Esta configuración se puede configurar utilizando el

complemento de la política de seguridad-en (secpol.msc). En Windows Vista o Windows Server


4


2008, puede configurar la aplicación más granular de políticas generadas desde la línea de

comandos mediante el programa de la política de auditoría (Auditpol.exe). Para obtener más

información acerca de los pasos para habilitar la escritura en el registro de seguridad de

Windows, vea Cómo: Escribir servidor de eventos de auditoría en el registro de seguridad. Para

obtener más información sobre el programa de Auditpol.exe, véase el artículo de Knowledge

Base 921469, Cómo utilizar Directiva de grupo para configurar la auditoría de seguridad

detalladas. Los registros de sucesos de Windows son globales para el sistema operativo

Windows. Para obtener más información acerca de los registros de sucesos de Windows, consulte

el Visor de sucesos general. Si usted necesita permisos más precisa sobre la auditoría, el uso del

archivo de destino binario. Para obtener más información acerca de la auditoría de los registros

por escrito a la meta, vea SQL Server Audit Records.

NOTA: Cualquier usuario autenticado puede leer y escribir en el registro de sucesos de la

aplicación de Windows. El registro de sucesos de la aplicación necesita un nivel de permisos que

el registro de sucesos de seguridad de Windows y es menos seguro que el registro de sucesos de

seguridad de Windows.

PASOS Paso 1:

Haga clic en abrir SQL Server 2008 Management Studio y acceder a ella.

Paso 2:

Explora el nodo de seguridad en el Explorador de objetos y seleccione el nodo de Auditoría.

Paso 3:

Haga clic derecho en el nodo de Auditoría y seleccione la opción “New auditoría” en el menú.


http://eliasnegrete.files.wordpress.com/2010/02/sql2008-5.jpg

5


Paso 4:

Ahora SQL Server 2008 se abrirá un cuadro de diálogo “Crear Auditoría”, con algunos campos. Es

importante entender el significado de cada uno de estos campos. Los detalles de estos campos

se proporcionan a continuación.

Nombre de auditoría

El nombre de la auditoría. Esto se genera automáticamente cuando se crea una nueva auditoría,

pero se puede modificar.

Retraso de la cola (en milisegundos)

Especifica la cantidad de tiempo en milisegundos que puede transcurrir antes de que las

acciones de auditoría están obligados a ser procesados. Un valor de 0 indica la entrega

sincrónica. El valor por defecto el valor mínimo es 1000 (1 segundo). El máximo es

2.147.483.647 (2.147.483,647 segundos, o 24 días, 20 horas, 31 minutos, 23,647 segundos).

Apague el servidor de auditoría de error

Fuerzas de un servidor de cerrar cuando la instancia del servidor escrito a la meta no puede

escribir datos en el objetivo de la auditoría. El inicio de sesión emitiendo este debe tener el

permiso SHUTDOWN. Si el inicio de sesión no tiene este permiso, esta función se producirá un

error y un mensaje de error será levantado.

Como una mejor práctica, esto sólo debería utilizarse en casos en que un error de auditoría

podría poner en peligro la seguridad o la integridad del sistema.

Destino de auditoría

Especifica el objetivo de la auditoría de datos. Las opciones disponibles son un archivo binario, el

registro de aplicación de Windows, o el registro de seguridad de Windows. SQL Server no puede

escribir en el registro de seguridad de Windows sin configurar valores adicionales en Windows. Ing. Sistemas e Informática UNASAM Facultad de Ciencias


6


Para obtener más información, vea Cómo: Escribir servidor de eventos de auditoría en el registro

de seguridad.

Nota: La escritura en el registro de seguridad no está disponible en Windows XP.

Archivos de sustitución máxima

Especifica el número máximo de archivos de auditoría para mantener en el sistema de archivos.

Cuando el ajuste de MAX_ROLLOVER_FILES = ilimitado, no se impone ningún límite en el número

de archivos de sustitución que se creará. El valor predeterminado es ilimitado. El número

máximo de archivos que se pueden especificar es 2.147.483.647.

Tamaño máximo del archivo (MB)

Especifica el tamaño máximo, en megabytes (MB), para un archivo de auditoría. El tamaño

mínimo que se puede especificar es de 1024 KB y el máximo es 2.147.483.647 terabytes (TB).

También puede especificar UNLIMITED, que no pone un límite en el tamaño del archivo.

Especificar un valor inferior a 1024 KB se producirá el error MSG_MAXSIZE_TOO_SMALL. El valor

predeterminado es ilimitado.

Espacio en disco de reserva

Especifica que el espacio es preasignados en el disco igual al tamaño del archivo especificado

máximo. Este ajuste sólo se puede utilizar si MAXSIZE no es igual a unlimited. La configuración

por defecto es OFF.

Ruta de archivo

Especifica la ubicación de la carpeta en la que los datos de auditoría se escribe cuando el destino

de Auditoría es un archivo. Al hacer clic en el botón al lado de este campo se abre el cuadro de

diálogo Buscar carpeta para especificar una ruta de archivo o crear una carpeta donde se escribe

el archivo de auditoría.

Ahora, después de llenar los valores adecuados en el cuadro de diálogo, pulse Aceptar para crear

una auditoría.


7


Paso 5:

Ahora, una vez que se crea la Comisión de Auditoría, que podría ser encontrado en el> nodo de

Seguridad> Auditoría. Ahora, para permitir a la Auditoría, simplemente haga clic derecho sobre

la auditoría de servidor que acabamos de crear y del menú seleccione “Activar Auditoría”.

Usted podría ver un cuadro de diálogo con el mensaje de éxito,

si la operación tiene éxito.




8


Paso 6:

Puede hacer clic derecho sobre la Auditoría acaba de crear y seleccione la opción “Ver los

registros de auditoría”. Esto abre un cuadro de diálogo que contiene los registros de auditoría.

Usted puede también el guión de la política de crear. Haga clic derecho sobre la auditoría,

seleccione “Script de auditoría como”>> “Para Crear”>> (Archivo / Portapapeles / Agente de

Empleo).




9


Como

sabemos que la política de gestión basada en forma ahora parte integrante de la de SQL Server

2008 de modo que si desea crear una política para esta auditoría, entonces es muy fácil de hacer

eso. Tiene que hacer clic derecho sobre la Auditoría acaba de crear, seleccionar la opción

“facetas” del menú.

Presenta un cuadro de diálogo

que le proporciona el estado de la auditoría. En la esquina inferior derecha de este cuadro de

diálogo que se encuentra un botón “Exportar como política de Estado actual”.




10


Al hacer clic en el botón “Exportar como la política actual del Estado”, que nos lleva a otro

cuadro de diálogo que muestra el nombre de directiva y el nombre de la condición que se puede

editar. Usted puede seleccionar la opción adecuada a continuación estos campos para aplicar

esta política en el servidor local o para guardarlo como un archivo de política. Si usted selecciona

la opción “servidor local” opción, se vería que una política y un Estado ha sido creado con el

nombre que ha especificado en el cuadro de diálogo.




11


Paso 7:

Ahora vamos a crear la especificación de auditoría de servidor.

Antes de continuar recomiendo encarecidamente que visite el artículo de TechNet:Servidor de

Auditoría Grupos de Acción y Acciones.

Una vez que han comprendido la auditoría de servidor Grupos de Acción, explorar el nodo de

Seguridad>> Auditoría>> Especificaciones de auditoría de servidor en el Explorador de objetos

en SQL Server 2008. Haga clic derecho sobre las Especificaciones de auditoría de servidor y

seleccione “Nuevo Server Audit Specification”.

Al seleccionar esta opción, un cuadro de diálogo en el que se especifique de auditoría de servidor

Nombre de especificación y “servidor de auditoría” que ha creado en el paso 6. A partir de

entonces tendría que especificar los Grupos de Acción de auditoría en la rejilla de abajo. Una vez

que haya seleccionado todos los Grupos de Acción requiere de Auditoría, que se pulse Aceptar y

verás una especificación de auditoría creado para el servidor. En este paso que he seleccionado

el Grupo de Acción de Auditoría “Restaurar copia de seguridad del grupo” y este evento se

produce cada vez que se expida una copia de seguridad o restaurar los comandos.


http://technet.microsoft.com/en-us/library/cc280663(SQL.100).aspx

http://technet.microsoft.com/en-us/library/cc280663(SQL.100).aspx



12


Una vez que se crea, se vería que la especificación de auditoría está en estado de discapacidad.

Tiene que hacer clic derecho sobre la especificación de auditoría que se acaba de crear y

selecciona “Habilitar Server Audit Specification” en el menú.

Justo después de eso, a ver un cuadro de diálogo diciendo: “La operación fue un éxito”, a menos

que haya hecho algo divertido para hacer que falle.

Usted puede hacer clic derecho sobre la especificación de auditoría de servidor y facetas

seleccione en el menú. Esto muestra un cuadro de diálogo con un botón en la parte inferior

derecha que dice “de exportación en curso como política de Estado”. Haga clic en este botón

para crear una política como lo hemos hecho anteriormente.




13


Paso 8:

Ahora vamos a tratar de copias de seguridad de una base de datos en el servidor. He utilizado la

base de datos AdventureWorksLT para la demostración. Luego veremos los registros para

asegurar que nuestra auditoría está trabajando.

Explorar los nodos en el Explorador de objetos, Bases de datos>> AdventureWorksLT. Haga clic

derecho sobre la base de datos AdventureWorksLT y seleccione Tareas>> Copia



14


Esto nos trae

el siguiente cuadro de diálogo para crear una copia de seguridad de la base de datos. Por favor,

rellene los valores adecuados en los campos de este cuadro de diálogo y pulse el botón Aceptar.

Para obtener más información o ayuda en la consideración de copia de seguridad por favor visite

Libros en Línea.

Verás la copia de seguridad completa con éxito.




15


Ahora haga clic derecho sobre la auditoría de servidor que hemos creado en el Explorador de

objetos y seleccione la opción Ver los registros de auditoría en el menú.

Esto abrirá el cuadro de diálogo que mostrará los detalles del evento de copia de seguridad. En

la foto de abajo, verás la copia de seguridad de evento que ocurrió en la base de datos

AdventureWorksLT. Puede desplazarse a la derecha para encontrar los detalles en el cuadro de

diálogo

Paso 9:

Ahora tenemos que crear una especificación de auditoría de base de datos. Yo usaría la base de

datos AdventureWorksLT para la demostración. Usted puede visitar el artículo de TechNet:





16


Servidor de Grupos de Acción de Auditoría y vaya a “nivel de base de Auditoría Grupos de

Acción” y “el nivel de base de Auditoría Acciones” secciones de este artículo para obtener más

información.

La hipótesis es que desea auditar cada seleccionar, insertar, actualizar, eliminar la operación

realizada en la mesa SalesLT.Product por nadie.

Explora el> Base de datos> AdventureWorksLT>> Seguridad>> Especificaciones de auditoría

de base de datos de nodos en el Explorador de objetos. Ahora haga clic derecho en la sección

“Especificaciones de la base de datos de auditoría” nodo y seleccione “Nueva base de datos de

auditoría de Especificaciones” en el menú.

Esto abrirá un

cuadro de diálogo nuevo con el título – “Crear base de datos de Auditoría de Especificaciones”.



17


Ahora tenemos que dar a la especificación de auditoría de base de datos un nombre y luego

seleccione el nombre del servidor de auditoría de la lista desplegable.

Dentro de la red en la “Auditoría de acción de tipo” escoger “Select” de la lista desplegable, en

la “clase de objeto”, seleccione “objeto”. Haga clic en el botón al lado del cuadro de texto

Nombre del objeto, que se abre un cuadro de diálogo. Seleccione el cuadro SalesLT.Product que

traerá “SalesLT” en “objeto” y “producto” en “Nombre de objeto”. Ahora pulse el botón al lado

del cuadro de texto en la sección “Nombre principal”, seleccione “pública”, navegando por los

objetos en el cuadro de diálogo que aparece una vez que haga clic en el botón.

Repita la operación para el tipo de acción de auditoría INSERT, UPDATE, DELETE entonces,

finalmente pulse Aceptar.



18


Esto creará una especificación de auditoría de base de datos para usted que no está habilitado.

Haga clic en esta especificación de auditoría de base de datos y selecciona “Habilitar la base de

datos de auditoría de Especificaciones” en el menú.



19


Una vez que finaliza la operación .. un cuadro de diálogo aparece para confirmar el éxito de la

operación.

Puede crear una política de

esta especificación de auditoría de base de datos haciendo clic derecho sobre la especificación

de auditoría de base de datos y seleccionando la opción “facetas”, y tras el procedimiento de la

misma manera como hemos hecho anteriormente.

Paso 10: (último paso)




20


Ahora haríamos la consulta “Select * from SalesLT.Product” contra la base de datos

AdventureWorksLT.

Después de que usted puede explorar el Nodo de Seguridad en el Explorador de objetos. <SQL

Instancia <Nombre>> Seguridad>> Auditorías y haga clic derecho sobre el control de servidor

que ya está creada y seleccione “Ver los registros de auditoría” en el menú.



21


Ahora el cuadro de diálogo,

que tiene el registro de auditoría que dice que usted ha seleccionado la tabla de SalesLT.Product

en la base de datos AdventureWorksLT. Puede desplazarse a la derecha en el cuadro de diálogo

para encontrar información detallada.




22


II. AUDITORIA DESDE SQL SERVERAntes de poder crear una especificación de auditoría de base de datos, deberá crear y configurar un objeto SQL Server Audit que se pueda usar en la auditoría.

Para realizar esta tarea debe usar el Editor de consultas de SQL Server Management Studio con objeto de llevar a cabo el siguiente procedimiento. En el ejemplo siguiente se crea una auditoría de base de datos de cualquier operación de inserción en la base de datos AdventureWorks2008R2 de la tabla Person.Person y se envían los resultados al registro de eventos de aplicación de Windows.

Crear una auditoría de base de datosCree un objeto Audit y defina el destino.

1. /* Create the SQL Server Audit object, and send the results to the 2. Windows Application event log. */3. CREATE SERVER AUDIT Test_SQL_Server_Audit4. TO APPLICATION_LOG5. /* The Queue Delay is set to 1000, meaning one second 6. intervals to write to the target. */7. WITH ( QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);8. GO;

Cree la especificación de auditoría de base de datos y asígnela al objeto Audit.9. /* Create the Database Audit Specification object using an Audit

event for the Person.Person Table and the FirstName and LastName columns. */

10. USE AdventureWorks2008R2;11. GO;12. CREATE DATABASE AUDIT

SPECIFICATION Test_Database_Audit_Specification13. FOR SERVER AUDIT Test_SQL_Server_Audit14. ADD (INSERT 15. ON Person.Person16. BY dbo)17. WITH (STATE = ON);18. GO19. Habilite la auditoría.


23


20. /* Enable the audit. */21. ALTER SERVER AUDIT Test_SQL_Server_Audit22. WITH (STATE = ON);23. GO

III. AUDITORIA CON TRIGGERSEJEMPLO: TRIGGER INSERCION, ACTUALIZACION Y ELIMINACION

//////////////////PERSONA INSERTADA//////////////////create table inserPersona(

usu varchar(20) not null,cod varchar(10) not null,nom varchar(45) not null, ape varchar(45) not null,fech date

);

create trigger TgerInsertarpersonaon dbo.personaafter insertas Begin

declare @usu varchar(50);declare @cod varchar(50);declare @nom varchar(50);declare @ape varchar(50);set @usu= CURRENT_USER;select @cod= idPersona from insertedselect @nom= nom from insertedselect @ape= apel from insertedinsert into inserPersona values(@usu,@cod,@nom,@ape,GETDATE());

endgo

--insert into persona values ('20','fra','ni','ave','87542198','M');select * from inserPersona

//////////////////////PERSONA MODIFICADA///////////////////////

create table updatePersona(usu varchar(20) not null,noma varchar(45), nomn varchar(45), fech date

);


24


CREATE TRIGGER TgerUpdatePersona ON dbo.persona AFTER UPDATEAS DECLARE @usu varchar(50), @vOldnom varchar(45), @vNewnom varchar(45)

IF UPDATE(nom) BEGIN SELECT @vOldnom = d.nom, @vNewnom = i.nom FROM inserted i INNER JOIN deleted d ON i.idpersona= d.idpersona

END set @usu= CURRENT_USER; insert into updatePersona values (@usu,@vNewnom,@vOldnom,GETDATE())

GO

/////////////PERSONA ELIMINAR////////////////////////create table deletePersona(

usu varchar(20) not null,cod varchar(10) not null,nom varchar(45) not null, ape varchar(45) not null,

fech date );

create trigger TgerDeletepersonaon dbo.personaafter DELETEas Begin

declare @usu varchar(50);declare @cod varchar(50);declare @nom varchar(50);declare @ape varchar(50);set @usu= CURRENT_USER;select @cod= idPersona from deletedselect @nom= nom from deletedselect @ape= apel from deletedinsert into deletePersona values(@usu,@cod,@nom,@ape,GETDATE());

endgo


25


IV. SQL Compliance Manager Monitor, auditoría y alerta sobre SQL Server actividad de los usuarios y los datos de los cambios realizados.

La auditoría de datos sensibles - obtener visibilidad detallada de quién hizo qué, cuándo, dónde y

cómo

Control de cambios - supervisar y auditar el acceso de datos, inicios de sesión fallidos, los cambios

de esquema, etc

Descubre Amenazas - personalizar alertas para notificar actividades sospechosas por los usuarios o

los hackers

Satisfacer Auditorías - satisfacer los requisitos de cumplimiento con PCI, HIPAA, SOX FERPA y

plantillas

Generar informes - entregar más de 25 informes incorporados para validar SQL Server pistas de

auditoría

Minimizar Gastos generales - reducir el impacto en los servidores a través de un agente de

recolección de datos de luz


http://www.idera.com/SQL-Server/sql-compliance-manager/

Date post:	19-Jan-2016
Category:	Documents
Upload:	jhon-edwin-nivin-aguedo
View:	54 times
Download:	1 times

Auditoria en SQL Server

Documents