En el día a día de la administración de las bases de datos, los cambios en el schema son demasiados, por lo que se vuelve sumamente necesario contar con un mecanismo de auditoria de dichos cambios, que permita dar respuesta a las preguntas ¿qué? ¿como? ¿cuando? ¿quien? WTF?, y que eventualmente también te permitirá echarle la culpa al verdadero responsable de una catástrofe en las BD.
Bueno en este y otros escenarios más o menos parecidos tienen significado los DDL (Data Definition Language) Triggers, los cuales al igual que los DML Triggers se disparan al generarseciertos eventos en ciertos objetos, es decir, nosotros crearemos un Trigger que realize "algo" en el momento que tenga lugar un suceso determinado en el objeto de la BD que nos interese.
Estos Trigger (a partir de este momento TR) tienen 2 ambitos: Servidor y Base de Datos, y como es de esperarse responden a diferentes eventos.
Tabla de Eventos para la BD. (MSDN)
| CREATE_APPLICATION_ROLE (Applies to CREATE APPLICATION ROLE statement andsp_addapprole. If a new schema is created, this event also triggers a CREATE_SCHEMA event.) | ALTER_APPLICATION_ROLE (Applies to ALTER APPLICATION ROLE statement andsp_approlepassword.) | DROP_APPLICATION_ROLE (Applies to DROP APPLICATION ROLE statement andsp_dropapprole.) |
| CREATE_ASSEMBLY | ALTER_ASSEMBLY | DROP_ASSEMBLY |
| ALTER_AUTHORIZATION_DATABASE (Applies to ALTER AUTHORIZATION statement when ON DATABASE is specified, and sp_changedbowner.) | ||
| CREATE_CERTIFICATE | ALTER_CERTIFICATE | DROP_CERTIFICATE |
| CREATE_CONTRACT | DROP_CONTRACT | |
| GRANT_DATABASE | DENY_DATABASE | REVOKE_DATABASE |
| CREATE_EVENT_NOTIFICATION | DROP_EVENT_NOTIFICATION | |
| CREATE_FUNCTION | ALTER_FUNCTION | DROP_FUNCTION |
| CREATE_INDEX | ALTER_INDEX | DROP_INDEX |
| CREATE_MESSAGE_TYPE | ALTER_MESSAGE_TYPE | DROP_MESSAGE_TYPE |
| CREATE_PARTITION_FUNCTION | ALTER_PARTITION_FUNCTION | DROP_PARTITION_FUNCTION |
| CREATE_PARTITION_SCHEME | ALTER_PARTITION_SCHEME | DROP_PARTITION_SCHEME |
| CREATE_PROCEDURE | ALTER_PROCEDURE | DROP_PROCEDURE |
| CREATE_QUEUE | ALTER_QUEUE | DROP_QUEUE |
| CREATE_REMOTE_SERVICE_BINDING | ALTER_REMOTE_SERVICE_BINDING | DROP_REMOTE_SERVICE_BINDING |
| CREATE_ROLE (Applies to CREATE ROLE statement, sp_addrole, and sp_addgroup.) | ALTER_ROLE | DROP_ROLE (Applies to DROP ROLE statement,sp_droprole, and sp_dropgroup.) |
| CREATE_ROUTE | ALTER_ROUTE | DROP_ROUTE |
| CREATE_SCHEMA (Applies to CREATE SCHEMA statement, sp_addrole, sp_adduser,sp_addgroup, and sp_grantdbaccess.) | ALTER_SCHEMA (Applies to ALTER SCHEMA statement and sp_changeobjectowner.) | DROP_SCHEMA |
| CREATE_SERVICE | ALTER_SERVICE | DROP_SERVICE |
| CREATE_STATISTICS | DROP_STATISTICS | UPDATE_STATISTICS |
| CREATE_SYNONYM | DROP_SYNONYM | |
| CREATE_TABLE | ALTER_TABLE | DROP_TABLE |
| CREATE_TRIGGER | ALTER_TRIGGER | DROP_TRIGGER |
| CREATE_TYPE (Applies to CREATE TYPE statement and sp_addtype.) | DROP_TYPE (Applies to DROP TYPE statement andsp_droptype.) | |
| CREATE_USER (Applies to CREATE USER statement, sp_adduser, andsp_grantdbaccess.) | ALTER_USER | DROP_USER (Applies to DROP USER statement,sp_dropuser, and sp_revokedbaccess.) |
| CREATE_VIEW | ALTER_VIEW | DROP_VIEW |
| CREATE_XML_SCHEMA_COLLECTION | ALTER_XML_SCHEMA_COLLECTION | DROP_XML_SCHEMA_COLLECTION |
Tabla de Eventos a nivel del Servidor. (MSDN)
| ALTER_AUTHORIZATION_SERVER | ||
| CREATE_DATABASE | ALTER_DATABASE | DROP_DATABASE |
| CREATE_ENDPOINT | ALTER_ENDPOINT | DROP_ENDPOINT |
| CREATE_LOGIN (Applies to CREATE LOGIN statement, sp_addlogin,sp_grantlogin, xp_grantlogin, and sp_denylogin when used on a nonexistent login that must be implicitly created.) | ALTER_LOGIN (Applies to ALTER LOGIN statement, sp_defaultdb,sp_defaultlanguage, sp_password, and sp_change_users_loginwhen Auto_Fix is specified.) | DROP_LOGIN (Applies to DROP LOGIN statement, sp_droplogin, sp_revokelogin, and xp_revokelogin.) |
| GRANT_SERVER | DENY_SERVER | REVOKE_SERVER |
EVENTDATA()
Bien, cuando los eventos mencionados se disparan, la información que detalla lo ocurrido es almacenada en un archivo XML y que es accesible mediante la función EVENTDATA(), a partir de ahí podemos ocupar XQUERY, para consultar la información que necesitemos de forma especifica.
A continuación les muestro un ejemplo de la información proporcionada por EVENTDATA(). (Fuente)
<EVENT_INSTANCE>
<EventType>CREATE_TABLE</EventType>
<PostTime>2005-07-30T10:48:52.537</PostTime>
<SPID>55</SPID>
<ServerName>PSE-TEST-JON1</ServerName>
<LoginName>PSE-TEST-JON1\Administrator</LoginName>
<UserName>dbo</UserName>
<DatabaseName>AdventureWorks</DatabaseName>
<SchemaName>Person</SchemaName>
<ObjectName>Address</ObjectName>
<ObjectType>TABLE</ObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON"
ANSI_NULL_DEFAULT="ON"
ANSI_PADDING="ON"
QUOTED_IDENTIFIER="ON"
ENCRYPTED="FALSE" />
<CommandText>CREATE TABLE [Person].[Address](
[AddressID] [int]
IDENTITY (1, 1)
NOT FOR REPLICATION NOT NULL,
[AddressLine1] [nvarchar](60) NOT NULL,
[AddressLine2] [nvarchar](60) NULL,
[City] [nvarchar](30) NOT NULL,
[StateProvinceID] [int] NOT NULL,
[PostalCode] [nvarchar](15) NOT NULL,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT
[DF_Address_rowguid] DEFAULT (NEWID()),
[ModifiedDate] [datetime] NOT NULL CONSTRAINT
[DF_Address_ModifiedDate] DEFAULT (GETDATE())
) ON [PRIMARY];</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>
Como podemos ver, la información presentada es bastante útil e importante, aunque en lo personal me gustaría que cuando realizaras una operación de modificación o de eliminación de un objeto, guardara un copia de la definición anterior. Para aquellos que crear valido recurrir a las Vistas de Sistema y buscar la definición les comento ya lo intente y desafortunadamente el trigger se ejecuta posteriormente al evento de tal forma que la definición de los objetos ya ha cambiado en las vistas de sistema.
Bien a continuación pongo un ejemplo propio del trigger que he implementado y que de momento me funciona correctamente, seguramente podrán mejorarlo y adaptarlo a sus necesidades así que ojala lo podamos comentar.
/**
* Trigger DDL para auditoria de cambios en el schema de las bd
* Version 1, Alamacena el detalle de los cambios en el schema de
* las bd, evita que se borren tablas.
* 12 de Abril de 2010.
* I.S.C. Juan Carlos Armenta Bautista
**/
GO
IF EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'tr_chge_log_Db')
BEGIN
DISABLE TRIGGER [tr_chge_log_Db] ON DATABASE;
SELECT '[tr_chge_log_Db] Deshabilitado.....' AS msg;
END
GO
/****** Object: DdlTrigger [tr_chge_log_Db] Script Date: 04/12/2010 14:45:55 ******/
IF EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'tr_chge_log_Db')
BEGIN
DROP TRIGGER [tr_chge_log_Db] ON DATABASE
SELECT '[tr_chge_log_Db] Borrado.....' AS msg;
END
GO
create TRIGGER tr_chge_log_Db
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
,CREATE_VIEW, ALTER_VIEW, DROP_VIEW
,CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
,CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION
,CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER
,CREATE_INDEX, ALTER_INDEX, DROP_INDEX
AS
BEGIN
SET NOCOUNT ON;
SET ARITHABORT ON;
BEGIN TRY
DECLARE @evento as XML;
SET @evento=EVENTDATA();
INSERT INTO msdb.dbo.change_db_sch_log(sch_obj,nom_bd,tip_evt,nom_obj,tip_obj,tsql_cmd,nom_login)
SELECT @evento.value('(/EVENT_INSTANCE/SchemaName)[1]','varchar(256)'),
@evento.value('(/EVENT_INSTANCE/DatabaseName)[1]','varchar(256)'),
@evento.value('(/EVENT_INSTANCE/EventType)[1]','varchar(50)'),
@evento.value('(/EVENT_INSTANCE/ObjectName)[1]','varchar(256)'),
@evento.value('(/EVENT_INSTANCE/ObjectType)[1]','varchar(25)'),
@evento.value('(/EVENT_INSTANCE/TSQLCommand)[1]','varchar(max)'),
@evento.value('(/EVENT_INSTANCE/LoginName)[1]','varchar(256)')
-- Validacion del tipo de evento, si es un drop_table, se anula la operacion
IF @evento.value('(/EVENT_INSTANCE/EventType)[1]','varchar(50)')='DROP_TABLE'
BEGIN
RAISERROR ('No se permite la eliminacion de tablas en la BD.',16,1);
ROLLBACK;
END
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState);
ROLLBACK;
END CATCH
END
GO
SELECT '[tr_chge_log_Db] Creado.....' AS msg;
…
Notas finales.
Como verán para poder borrar o modificar el trigger primero es necesario deshabilitarlo. Asi pues 2 instrucciones que seguro les serviran mucho son:
ENABLE TRIGGER tg_name ON DATABASE;
DISABLE TRIGGER tg_name ON DATABASE;
que creo no requieren mayor explicación.
Sale pues hasta aquí no más, ojala que les sea de utilidad mi primer post.
