none
Revisar uso de una base de datos RRS feed

  • Pregunta

  • Estimados buenos dias o tardes segun corresponda, existe alguna forma de poder saber el uso histórico de una base de datos productiva, tuve hace tiempo una mala experiencia con un logon trigger . 

    Mi interés es saber si un usuario consulto algo de una base de datos o realizo alguna modificación insert o delete de datos. 

    De antemano gracias por las respuestas. 

    Saludos cordiales

     
    jueves, 12 de diciembre de 2019 13:32

Respuestas

  • Hola SebaSQL:

    USE [master]
    GO
      
    CREATE SERVER AUDIT [AuditoriaBBDDBlog]
    TO FILE 
    ( FILEPATH = N'C:\Auditorias'
     ,MAXSIZE = 0 MB
     ,MAX_ROLLOVER_FILES = 2147483647
     ,RESERVE_DISK_SPACE = OFF
    )
    WITH
    ( QUEUE_DELAY = 1000
     ,ON_FAILURE = CONTINUE
    );
    GO
    ALTER SERVER AUDIT [AuditoriaBBDDBlog]
    WITH (STATE = ON);
    GO
    USE [blog]
    GO
     
    CREATE DATABASE AUDIT SPECIFICATION 
    [DatabaseAuditSpecification-20190201-190230]
    FOR SERVER AUDIT [AuditoriaBBDDBlog]
    ADD (DELETE ON OBJECT::[dbo].[elementos] BY [public]),
    ADD (INSERT  ON OBJECT::[dbo].[elementos] BY [public]),
    ADD (UPDATE ON OBJECT::[dbo].[elementos] BY [public]);
    GO
    USE [blog]
    GO
    ALTER DATABASE AUDIT SPECIFICATION 
    [DatabaseAuditSpecification-20190201-190230]
    WITH (STATE = ON);
    GO
    USE [blog]
    GO
     
    INSERT INTO elementos
    (id, 
     descripcion, 
     modelo, 
     marca
    )
    VALUES
    (301, 
     'elemento para auditar', 
     'modelo', 
     'marca'
    );
    /* insertamos una fila */
      
    GO
      
    UPDATE elementos
      SET
          modelo = 'model'
    WHERE id = 300;
    /* modificamos una fila */
      
    GO
      
    DELETE FROM elementos
    WHERE id = 300;
    /* borramos una fila */
      
    GO


    Ahora me conecto con otro usuario.

    USE [master]
    GO
    CREATE LOGIN [TestAuditorias] WITH PASSWORD=N'TestAuditorias', DEFAULT_DATABASE=[blog], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    GO
    USE [blog]
    GO
    CREATE USER [TestAuditorias] FOR LOGIN [TestAuditorias]
    GO
    USE [blog]
    GO
    ALTER USER [TestAuditorias] WITH DEFAULT_SCHEMA=[dbo]
    GO
    USE [blog]
    GO
    ALTER ROLE [db_owner] ADD MEMBER [TestAuditorias]
    GO
    

    Me conecto con el nuevo user

    INSERT INTO elementos
    (id, 
     descripcion, 
     modelo, 
     marca
    )
    VALUES
    (302, 
     'elemento para auditar', 
     'modelo', 
     'marca'
    );
    /* insertamos una fila */
      
    GO

    Vuelvo a la ventana de mi user sysadmin y ejecuto la query

    SELECT 
           event_time, 
           action_id, 
           statement, 
           database_name, 
           server_principal_name
    FROM fn_get_audit_file('c:\Auditorias\AuditoriaBBDDBlog_*.sqlaudit', DEFAULT, DEFAULT);

    Las dos lineas de más en la insert IN y UNDO, básicamente, son una infracción de primary key, porque copie y pegue la misma sentencia de insercción.

    Espero te ayude.

    • Marcado como respuesta SebaSQL jueves, 12 de diciembre de 2019 18:44
    jueves, 12 de diciembre de 2019 17:04
  • Si realizádolo por todas las entradas que te devuelve esa consulta.

    No se si existe un modo más compacto, pero puedes probar en un ambiente de desarrollo, tal que.

    CREATE DATABASE AUDIT SPECIFICATION 
    [DatabaseAuditSpecification-20190201-190230]
    FOR SERVER AUDIT [AuditoriaBBDDBlog]

    Luego pones el management con la salida de una query a texto.

    select 
    'ADD (DELETE ON OBJECT::['+T.TABLE_SCHEMA+'].'+T.TABLE_NAME+'] BY [public]),'+CHAR(13)+
    'ADD (INSERT ON OBJECT::['+T.TABLE_SCHEMA+'].'+T.TABLE_NAME+'] BY [public]),'+CHAR(13)+
    'ADD (UPDATE ON OBJECT::['+T.TABLE_SCHEMA+'].'+T.TABLE_NAME+'] BY [public]),'+CHAR(13)+''
    
    from INFORMATION_SCHEMA.TABLES  T
    where table_type like 'BASE TABLE'

    Ejecutas la consulta, coges el texto y lo pegas, debajo. quitas la última coma y le añades el punto y coma.

    Y con esto ya tienes todas las tablas de una base.

    Pruébalo en ambiente de desarrollo.

    • Marcado como respuesta SebaSQL martes, 17 de diciembre de 2019 12:37
    jueves, 12 de diciembre de 2019 21:33

Todas las respuestas

  • A posteriori, no, no es posible. Para poder hacer ese control, tienes que configurar algun mecanismo de auditoria de antemano, antes de que los usuarios empiecen a acceder.
    jueves, 12 de diciembre de 2019 13:42
  • Muchas gracias por la respuesta Alberto, ¿y con el SQL Server Profiler es posible obtener si una base de datos fue accedida y por que usuarios a futuro y que ejecuto? .

    De ser si , ¿esto causa algún tipo de inconveniente al servidor productivo? ya sea blqueos , etc?

    De antemano muchas gracias siempre por tus respuestas

    Saludos cordiales. 

    jueves, 12 de diciembre de 2019 14:04
  • Deleted
    jueves, 12 de diciembre de 2019 14:16
  • La versión es SQL Server 2014  y la edición es Enterprise. 
    jueves, 12 de diciembre de 2019 14:20
  • Hola SebaSQL:

    Puedes utilizar:

    Auditoria de acciones.

    https://javifer2.wordpress.com/2019/10/19/auditoria-de-acciones-en-archivos/

    También te puede interesar y/o combinar con:

    Auditoria Base Datos mediante triggers.

    https://javifer2.wordpress.com/2019/10/07/auditoria-base-datos-completa-con-triggers/

    jueves, 12 de diciembre de 2019 15:12
  • Muchas gracias Javi , con respecto al primer articulo que publicas es posible saber que usuario ¿realizo la accion ? sobre X base de datos?

    De antemano gracias.

    jueves, 12 de diciembre de 2019 16:09
  • Como se conectan los usuarios a tú base de datos. A través de un inicio de sesión común o a través de una autenticación individual por usuario (Windows o Sql Server).


    jueves, 12 de diciembre de 2019 16:21
  • Los usuarios se conectan de forma mixta tanto por cuentas de Windows como por cuentas de SQL Server. 


    jueves, 12 de diciembre de 2019 16:37
  • Hola SebaSQL:

    USE [master]
    GO
      
    CREATE SERVER AUDIT [AuditoriaBBDDBlog]
    TO FILE 
    ( FILEPATH = N'C:\Auditorias'
     ,MAXSIZE = 0 MB
     ,MAX_ROLLOVER_FILES = 2147483647
     ,RESERVE_DISK_SPACE = OFF
    )
    WITH
    ( QUEUE_DELAY = 1000
     ,ON_FAILURE = CONTINUE
    );
    GO
    ALTER SERVER AUDIT [AuditoriaBBDDBlog]
    WITH (STATE = ON);
    GO
    USE [blog]
    GO
     
    CREATE DATABASE AUDIT SPECIFICATION 
    [DatabaseAuditSpecification-20190201-190230]
    FOR SERVER AUDIT [AuditoriaBBDDBlog]
    ADD (DELETE ON OBJECT::[dbo].[elementos] BY [public]),
    ADD (INSERT  ON OBJECT::[dbo].[elementos] BY [public]),
    ADD (UPDATE ON OBJECT::[dbo].[elementos] BY [public]);
    GO
    USE [blog]
    GO
    ALTER DATABASE AUDIT SPECIFICATION 
    [DatabaseAuditSpecification-20190201-190230]
    WITH (STATE = ON);
    GO
    USE [blog]
    GO
     
    INSERT INTO elementos
    (id, 
     descripcion, 
     modelo, 
     marca
    )
    VALUES
    (301, 
     'elemento para auditar', 
     'modelo', 
     'marca'
    );
    /* insertamos una fila */
      
    GO
      
    UPDATE elementos
      SET
          modelo = 'model'
    WHERE id = 300;
    /* modificamos una fila */
      
    GO
      
    DELETE FROM elementos
    WHERE id = 300;
    /* borramos una fila */
      
    GO


    Ahora me conecto con otro usuario.

    USE [master]
    GO
    CREATE LOGIN [TestAuditorias] WITH PASSWORD=N'TestAuditorias', DEFAULT_DATABASE=[blog], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    GO
    USE [blog]
    GO
    CREATE USER [TestAuditorias] FOR LOGIN [TestAuditorias]
    GO
    USE [blog]
    GO
    ALTER USER [TestAuditorias] WITH DEFAULT_SCHEMA=[dbo]
    GO
    USE [blog]
    GO
    ALTER ROLE [db_owner] ADD MEMBER [TestAuditorias]
    GO
    

    Me conecto con el nuevo user

    INSERT INTO elementos
    (id, 
     descripcion, 
     modelo, 
     marca
    )
    VALUES
    (302, 
     'elemento para auditar', 
     'modelo', 
     'marca'
    );
    /* insertamos una fila */
      
    GO

    Vuelvo a la ventana de mi user sysadmin y ejecuto la query

    SELECT 
           event_time, 
           action_id, 
           statement, 
           database_name, 
           server_principal_name
    FROM fn_get_audit_file('c:\Auditorias\AuditoriaBBDDBlog_*.sqlaudit', DEFAULT, DEFAULT);

    Las dos lineas de más en la insert IN y UNDO, básicamente, son una infracción de primary key, porque copie y pegue la misma sentencia de insercción.

    Espero te ayude.

    • Marcado como respuesta SebaSQL jueves, 12 de diciembre de 2019 18:44
    jueves, 12 de diciembre de 2019 17:04
  • ¿y con el SQL Server Profiler es posible obtener si una base de datos fue accedida y por que usuarios a futuro y que ejecuto?

    Solo si pones el Profiler a funcionar antes de que los usuarios accedan. No sirve para saber a posteriori lo que hicieron.

    Y sí, el Profiler produce algo de carga sobre el servidor, aunque no bloqueos. Si lo vas a dejar rodando, es preferible que uses el SQL TRACE, que usa el mecanismo interno del Profiler pero se queda rodando internamente en el servidor sin tener un programa externo conectado. Desde el propio profiler una vez que has configurado una captura existe una opción en el menú que te genera un script SQL que sirve para configurar la traza equivalente.

    Si es un SQL Server moderno, en lugar del Sql Trace puedes usar los Extended Events. Se configuran desde SSMS.

    Sin embargo, ten presente que estos mecanismos lo que hacen es capturar las sentencias SQL al completo. Para saber si un usuario ha accedido a una determinada tabla, tendrías que analizar las sentencias una por una a ver qué usuario las ha ejecutado y qué tablas intervienen en cada sentencia.

    En cambio, si configuras un Audit (como ya te han sugerido en una respuesta anterior) eso te captura directamente los sucesos que quieras como por ejemplo "Inserción en tabla X realizado por usuario Y". Ojo, esto necesita un SQL Server relativamente moderno; en las versiones antiguas, se necesitaba una edición Enterprise para utilizar la auditoría (y en las más antiguas todavía, no existía esta opción).

    jueves, 12 de diciembre de 2019 18:20
  • Hola Javi muchas gracias por tu respuesta estaba mirando el articulo, 

    En esta parte me aparece un error me imagino que el USE [Blog] es la base de datos a ser auditada?

    En esta este tramo de transact me arroja error

    CREATE DATABASE AUDIT SPECIFICATION 
    [DatabaseAuditSpecification-20190201-190230]
    FOR SERVER AUDIT [AuditoriaBBDDBlog]
    ADD (DELETE ON OBJECT::[dbo].[elementos] BY [public]),
    ADD (INSERT  ON OBJECT::[dbo].[elementos] BY [public]),
    ADD (UPDATE ON OBJECT::[dbo].[elementos] BY [public]);
    GO
    Msg 15151, Level 16, State 1, Line 74
    Cannot find the object 'elementos', because it does not exist or you do not have permission.
    

    Estoy ejecutando como "sa" en un servidor local 

    ¿el objeto elementos corresponde a una tabla verdad?

    jueves, 12 de diciembre de 2019 19:02
  • Hola SebaSQL:

    La base de datos, en ese caso es la mía, que se llama BLOG, y no puede encontrar elementos, porque no tienes ninguna tabla, que se llame elementos.

    Depende de lo que quieras auditar, puedes obtener los nombres de los objetos realizando un select * from informationschema.tables y completar el script con lo que retornes de esa consulta.

    jueves, 12 de diciembre de 2019 20:24
  • A perfecto comprendo Javi , y para el caso de una base de datos completa es posible?. 

    Gracias de antemano por la ayuda. 

    jueves, 12 de diciembre de 2019 21:05
  • Si realizádolo por todas las entradas que te devuelve esa consulta.

    No se si existe un modo más compacto, pero puedes probar en un ambiente de desarrollo, tal que.

    CREATE DATABASE AUDIT SPECIFICATION 
    [DatabaseAuditSpecification-20190201-190230]
    FOR SERVER AUDIT [AuditoriaBBDDBlog]

    Luego pones el management con la salida de una query a texto.

    select 
    'ADD (DELETE ON OBJECT::['+T.TABLE_SCHEMA+'].'+T.TABLE_NAME+'] BY [public]),'+CHAR(13)+
    'ADD (INSERT ON OBJECT::['+T.TABLE_SCHEMA+'].'+T.TABLE_NAME+'] BY [public]),'+CHAR(13)+
    'ADD (UPDATE ON OBJECT::['+T.TABLE_SCHEMA+'].'+T.TABLE_NAME+'] BY [public]),'+CHAR(13)+''
    
    from INFORMATION_SCHEMA.TABLES  T
    where table_type like 'BASE TABLE'

    Ejecutas la consulta, coges el texto y lo pegas, debajo. quitas la última coma y le añades el punto y coma.

    Y con esto ya tienes todas las tablas de una base.

    Pruébalo en ambiente de desarrollo.

    • Marcado como respuesta SebaSQL martes, 17 de diciembre de 2019 12:37
    jueves, 12 de diciembre de 2019 21:33
  • Muchas gracias Javi funciono perfectamente. 
    • Editado SebaSQL martes, 17 de diciembre de 2019 12:37
    martes, 17 de diciembre de 2019 12:37
  • De nada, es un placer, apoyar un poquito.
    martes, 17 de diciembre de 2019 12:40
  • Ahora me salta otra duda todo funciona perfecto pero la hora, me indica otra hora creo que es un timestamp, como puedo utilizar esta hora para que sea reflejada con la hora del servidor localHora del evento con desfase de la ora del servidor locala del servidor local

    Como se puede apreciar la hora me indica las 14:13 y la hora local eran las 11:13 al momento previo de tomar la captura de pantalla (Desfase de 3 horas), ¿esto se puede corregir?

    martes, 17 de diciembre de 2019 14:23
  • Hola SebaSQL:

    Esa hora es un fecha hora es un datetime2.

    Puedes trabajarlo como si fuera cualquier consulta SQL.

    Desplego en el object explorer, la base de datos master, programación, funciones y funciones de tabla.

    Luego en la consulta, fíjate en el IntelliSense que te devuelve todas las columnas que tiene esa función de table inline, ya que a la misma, le he puesto un alias, y despues de select pongo el alias más un punto, y espero.

    Entonces puedo hacer un Select Convert(.....

    https://docs.microsoft.com/es-es/sql/t-sql/queries/at-time-zone-transact-sql?view=sql-server-ver15

    o convertirlo a datetime, o trabajarlo según las especificaciones, de tu servidor.

    Espero te ayude

    martes, 17 de diciembre de 2019 18:15