locked
Auditoria en SQL 2000 y 2005 RRS feed

  • Pregunta

  •  

    Hola.

     

    Me estan pidiendo para las bases de datos de SQL 2000, que lleve un control de auditoria a nivel de campos por tabla, quieren saber que usuario modifica los datos y que datos y campos. Como puedo hacerlo?

     

    También necesito obtener los mismos datos para SQL 2005.

     

     

    lunes, 1 de diciembre de 2008 14:27

Respuestas

  • Hola.

    Si esta solucitud no llega al principio del desarrollo, hacerla sobre la marcha se hace muy complicado y sobre todo muy lesivo para el rendimiento. Bueno, muy lesivo para el rendimiento lo va a ser en cualquier caso.

    Al contrario de lo que indica Oscar, y con el debido respeto, en SQL Server 2000 y 2005 no existe (que yo sepa) una forma simple de realizar una auditoría de este tipo, desde luego no con profiler sin más (ójala y fuera tan fácil). Lo que puedes guardar de este modo son todas las consultas que se lanzan al servidor, pero aquí sólo queremos almacenar las modificaciones, que suelen ser menos del 5% del total de sentencias. Eso salvo que estemos hablando de habilitar auditoría C2, algo que son ya palabras mayores y que veo totalmente fuera de lugar en este caso que nos ocupa. Como mucho, se podría colocar dicha traza con un sinfín de filtros de texto, algo que para el rendimiento es también horrible a poco carga que se soporte.

    Lo más normal es que no sea necesario auditar todas las tablas del sistema, si no sólamente aquellas más importantes.Si necesitas almacenar qué usuario realiza cada modificación, y saber qué se cambia, necesitas conservar versiones de los datos. Así, con cada inserción, modificación y borrado, debes guardar en otra tabla la situación anterior del registro, puede que la versión actual, y quién hace el cambio. En este sentido, yo suelo optar por crear una tabla de auditoría por cada tabla real que hay que auditar.

    Luego queda cómo implementar eso. Lo ideal sería que tuvieras centralizada la capa de acceso a los datos en procedimientos almacenados. Así, tendrías que modificar cada procedimiento almacenado de inserción, modificación y borrado de cada tabla a auditar para que realice además la inserción en la tabla de auditoría. Si no estás en este caso, te quedan los triggers, mal que me pese porque los aborrezco. Debes hacer un trigger de inserción, modificación y borrado que vuelque a la tabla de auditoría. Serían triggers for (los clásicos) o en todo caso after (en caso únicamente de 2005). En ese trigger, sabes que tienes las "tablas" inserter y deleted con la situación anterior y posterior del registro, con eso y el usuario (que puedes obtener si no lo arrastras de otra forma), ya tienes todo lo necesario para insertar en la tabla de auditoría.

    Ten muy vigilado este tipo de sistemas porque es muy fácil que se vayan de madre, con un crecimiento desmedido.

    En resumen, es algo complicado, pero factible. Si tienes dudas, nos dices.  
    sábado, 28 de marzo de 2009 8:15
    Moderador

Todas las respuestas

  •  

    Hola Normannp,

                    En un servidor sql2000 crea una BD por ejemplo Auditoria, abre el SQL Profiler y selecciona los campos q requieres que se audite, luego de tener todas las opciones vas a la pestaña “General” ahí marta la casilla “Save to Table” seleccionas el servidor (servidor\instancia) donde está tu BD Auditoria, en la siguiente ventana seleccionas la BD Auditoria y en el campo “Table” pones un nombre para la tabla que se crea automáticamente y ok.

    Con esto guardas en una tabla todo lo que deseas, y ya luego puedes consultar a tu tabla cuando lo requieras.

    Para SQL2005 solo sigue la misma idea.

     Puedes tener tu BD en un motor SQL2005 con dos tablas o las que veas necesarias pero mínimo dos una para 2000 y otra para 2005.





    greos
    • Propuesto como respuesta Jesús Bosch viernes, 27 de marzo de 2009 17:32
    viernes, 27 de marzo de 2009 16:39
  • Hola.

    Si esta solucitud no llega al principio del desarrollo, hacerla sobre la marcha se hace muy complicado y sobre todo muy lesivo para el rendimiento. Bueno, muy lesivo para el rendimiento lo va a ser en cualquier caso.

    Al contrario de lo que indica Oscar, y con el debido respeto, en SQL Server 2000 y 2005 no existe (que yo sepa) una forma simple de realizar una auditoría de este tipo, desde luego no con profiler sin más (ójala y fuera tan fácil). Lo que puedes guardar de este modo son todas las consultas que se lanzan al servidor, pero aquí sólo queremos almacenar las modificaciones, que suelen ser menos del 5% del total de sentencias. Eso salvo que estemos hablando de habilitar auditoría C2, algo que son ya palabras mayores y que veo totalmente fuera de lugar en este caso que nos ocupa. Como mucho, se podría colocar dicha traza con un sinfín de filtros de texto, algo que para el rendimiento es también horrible a poco carga que se soporte.

    Lo más normal es que no sea necesario auditar todas las tablas del sistema, si no sólamente aquellas más importantes.Si necesitas almacenar qué usuario realiza cada modificación, y saber qué se cambia, necesitas conservar versiones de los datos. Así, con cada inserción, modificación y borrado, debes guardar en otra tabla la situación anterior del registro, puede que la versión actual, y quién hace el cambio. En este sentido, yo suelo optar por crear una tabla de auditoría por cada tabla real que hay que auditar.

    Luego queda cómo implementar eso. Lo ideal sería que tuvieras centralizada la capa de acceso a los datos en procedimientos almacenados. Así, tendrías que modificar cada procedimiento almacenado de inserción, modificación y borrado de cada tabla a auditar para que realice además la inserción en la tabla de auditoría. Si no estás en este caso, te quedan los triggers, mal que me pese porque los aborrezco. Debes hacer un trigger de inserción, modificación y borrado que vuelque a la tabla de auditoría. Serían triggers for (los clásicos) o en todo caso after (en caso únicamente de 2005). En ese trigger, sabes que tienes las "tablas" inserter y deleted con la situación anterior y posterior del registro, con eso y el usuario (que puedes obtener si no lo arrastras de otra forma), ya tienes todo lo necesario para insertar en la tabla de auditoría.

    Ten muy vigilado este tipo de sistemas porque es muy fácil que se vayan de madre, con un crecimiento desmedido.

    En resumen, es algo complicado, pero factible. Si tienes dudas, nos dices.  
    sábado, 28 de marzo de 2009 8:15
    Moderador