none
Auditar T-SQL RRS feed

  • Pregunta

  • Estimados.

    En un SS2014, quisiera saber que opciones serian las adecuadas para auditar instrucciones T-SQL (INSERT,UPDATE y DELETE) en las tablas para un usuario en particular.

    No quiero usar profiler, pues la idea es que siempre este activo la auditoria.

    No puedo usar trigger a nivel de tablas pues en esta ocasión el usurio tiene acceso de INSERT, UPDATE y DELETE a todas las tablas.

    Idealmente quisiera tener la informacion guardada en una tabla de la auditoria correspondiente.

    Saludos Cordiales.


    DBA SQL Server Santiago/Chile


    • Editado CMAPM miércoles, 21 de junio de 2017 21:13
    miércoles, 21 de junio de 2017 21:12

Respuestas

  • Saludos Christian,


    Siempre es un poco raro ver como quieres recraear la rueda, en todo caso si es cierto viendo bien las isntrucciones DML no son para un trigger de base de datos, las otras opciones o requeren enterprise u opciones de pago.

    Aqui hablan de los audit y los extended events como menciona Geovanny.

    https://dba.stackexchange.com/questions/42235/auditing-specific-database-events

    Tambien mencionan una herramienta de Apex.

    • Marcado como respuesta CMAPM jueves, 22 de junio de 2017 21:10
    jueves, 22 de junio de 2017 16:56
  • [SQL Trace a .trc] [...] Y luego será posible generar algun tipo de reporte sin detener la ejecución del fichero ?

    Para leer esos .trc existe una función, que no recuerdo de memoria pero solo es cuestión de buscarla en el manual. Es una función tipo tabla, le pasas la ruta del .trc y te devuelve una tabla con registros. Le puedes aplicar "order by", "where", o cualquier otra instrucción SQL, con lo que te quedan los datos listos para emitir el reporte.
    • Marcado como respuesta CMAPM jueves, 22 de junio de 2017 21:11
    jueves, 22 de junio de 2017 18:39

Todas las respuestas

  • Estoy auditando con especificaciones de auditoria de bd, funciona excelente, un solo pero.

    Cuando le indico que auditare un INSERT por ejemplo, el managemenete me da la opcion de "agregar" varios objetos, selecciono digamos 5 tablas, pero solo queda definida una de ellas, y claro solo me audita la que quedo definida.

    Entonces tengo que agregar una fila por cada tabla, y multiplicar por 3 si voy a auditar INSERT,UPDATE y DELETE.

    Saludos.


    DBA SQL Server Santiago/Chile

    jueves, 22 de junio de 2017 0:17
  • Y porque no haces un trigger a nivel base de datos, la otra opción es que uses uno de los modelos de auditoría no veo muchas opciones fuera de esto.
    jueves, 22 de junio de 2017 4:50
  • Una interesante opcion es el uso de Extended Events, con un consumo menor de recursos y que te permitira filtrar los eventos para un usuario especifico, aqui te dejo un articulo en ingles que explica en detalle este tema.

    https://www.sqlshack.com/creating-successful-auditing-strategy-sql-server-databases/

    Cualquier duda nos comentas y veremos como apoyarte.


    "Oh, the wind, the wind is blowing,through the graves the wind is blowing,Freedom soon will come; then well come from the shadows".The Partisan(Leonard Cohen) Email: me[at]geohernandez.net Blog:www.geohernandez.net

    • Marcado como respuesta CMAPM jueves, 22 de junio de 2017 15:12
    • Desmarcado como respuesta CMAPM jueves, 22 de junio de 2017 15:12
    jueves, 22 de junio de 2017 10:41
  • No quiero usar profiler, pues la idea es que siempre este activo la auditoria.

    Hay una alternativa para esto, que se llama el SQL Trace. Primero arrancas el Profiler y configuras la traza como la quieras. Despues te vas a las opciones de exportacion, y tiene una opcion que te exporta la configuracion a un fichero .sql que contiene varias llamadas a procedimientos almacenados. Ese fichero lo ejecutas contra el SQL Server, y el resultado es que deja internamente rodando la traza sin que este conectada al Profiler. La traza se graba en un archivo .trc (en una ruta que se escribe dentro de ese script que hemos mencionado) y cuando quieras examinar la traza basta con que abras el .trc con el Profiler (que mientras tanto no tiene por que estar en marcha).
    jueves, 22 de junio de 2017 12:09
  • Hola Enrique.

    Entiendo que un trigger a nivel de base de datos no me permite rescatar sentencias SQL como INSERT,UPDATE y DELETE, 

    Ejemplo del tyrigger que tengo a nivel de BD

    ALTER TRIGGER t_Test
    ON DATABASE 
    FOR ALTER_PROCEDURE,CREATE_PROCEDURE,DROP_PROCEDURE,ALTER_TABLE,CREATE_TABLE,DROP_TABLE,ALTER_INDEX,CREATE_INDEX,DROP_INDEX

    El tema es que si agrego INSERT me marca error.


    DBA SQL Server Santiago/Chile

    jueves, 22 de junio de 2017 14:55
  • No quiero usar profiler, pues la idea es que siempre este activo la auditoria.

    Hay una alternativa para esto, que se llama el SQL Trace. Primero arrancas el Profiler y configuras la traza como la quieras. Despues te vas a las opciones de exportacion, y tiene una opcion que te exporta la configuracion a un fichero .sql que contiene varias llamadas a procedimientos almacenados. Ese fichero lo ejecutas contra el SQL Server, y el resultado es que deja internamente rodando la traza sin que este conectada al Profiler. La traza se graba en un archivo .trc (en una ruta que se escribe dentro de ese script que hemos mencionado) y cuando quieras examinar la traza basta con que abras el .trc con el Profiler (que mientras tanto no tiene por que estar en marcha).

    Y luego será posible generar algun tipo de reporte sin detener la ejecución del fichero ?

    La idea es entregar mediante un job de SQL un correo en html con la información, eso lo se hacer pero cuando consulto datos desde una tabla.


    DBA SQL Server Santiago/Chile

    jueves, 22 de junio de 2017 14:58
  • Estoy revisando, pero no veo como filtrar que solo filtre los INSERT UPDATE y DELETE

    DBA SQL Server Santiago/Chile

    jueves, 22 de junio de 2017 15:12
  • Saludos Christian,


    Siempre es un poco raro ver como quieres recraear la rueda, en todo caso si es cierto viendo bien las isntrucciones DML no son para un trigger de base de datos, las otras opciones o requeren enterprise u opciones de pago.

    Aqui hablan de los audit y los extended events como menciona Geovanny.

    https://dba.stackexchange.com/questions/42235/auditing-specific-database-events

    Tambien mencionan una herramienta de Apex.

    • Marcado como respuesta CMAPM jueves, 22 de junio de 2017 21:10
    jueves, 22 de junio de 2017 16:56
  • [SQL Trace a .trc] [...] Y luego será posible generar algun tipo de reporte sin detener la ejecución del fichero ?

    Para leer esos .trc existe una función, que no recuerdo de memoria pero solo es cuestión de buscarla en el manual. Es una función tipo tabla, le pasas la ruta del .trc y te devuelve una tabla con registros. Le puedes aplicar "order by", "where", o cualquier otra instrucción SQL, con lo que te quedan los datos listos para emitir el reporte.
    • Marcado como respuesta CMAPM jueves, 22 de junio de 2017 21:11
    jueves, 22 de junio de 2017 18:39
  • Saludos Enrique

    Es que me gusta hacer pruebas distintas para tener una visión un poco mas amplia de algunos casos.

    Saludos nuevamente y gracias como siempre.


    DBA SQL Server Santiago/Chile

    jueves, 22 de junio de 2017 21:11
  • Muchas Gracias Alberto, chequearé.

    DBA SQL Server Santiago/Chile

    jueves, 22 de junio de 2017 21:11