Contenido del curso
Implementación de Triggers Administrativos en Bases de Datos
¿Cómo pueden utilizarse los triggers administrativos en bases de datos y servidores?
Los triggers administrativos son herramientas poderosas que ofrecen control y seguridad en la gestión de bases de datos y servidores. Estas funcionalidades permiten a los administradores establecer reglas estrictas sobre las modificaciones de la estructura de las bases de datos o sobre la creación de nuevas bases en el servidor. Son particularmente útiles para asegurar la integridad de la información y prevenir cambios no deseados. Además, proporcionan la capacidad de ofrecer notificaciones precisas y alertar a los administradores cuando se intentan llevar a cabo ciertas acciones.
¿Qué son los triggers a nivel de base de datos?
Los triggers a nivel de base de datos son utilizados para monitorear y restringir cambios en las tablas. La implementación de un trigger a este nivel permite validar y asegurar que no se alteren las tablas mediante operaciones como la eliminación o la modificación. Un ejemplo de esto sería un trigger que impida la alteración de la estructura de una tabla, como agregar o eliminar columnas.
En lenguaje SQL, la creación de un trigger a nivel de base de datos se podría ver de la siguiente manera:
CREATE TRIGGER ValidaTablas ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS BEGIN PRINT 'No es permitido modificar la estructura de la tabla. Comuníquese con el DBA.' ROLLBACK END
Este código asegura que cualquier intento de alterar una tabla en la base de datos devolverá un mensaje de error y cancelará la operación, protegiendo la estructura original de las tablas.
¿Cómo funcionan los triggers a nivel de servidor?
En el contexto del servidor, los triggers administrativos permiten controlar operaciones más amplias, como la creación o eliminación de bases de datos. Estos triggers son críticos en entornos donde la gestión de múltiples bases de datos es usual, proporcionando un control robusto sobre la infraestructura.
Un ejemplo de trigger para prevenir la creación de nuevas bases de datos a nivel de servidor se podría definir así:
CREATE TRIGGER ValidarBaseDeDatos ON ALL SERVER FOR CREATE_DATABASE AS BEGIN PRINT 'No se puede crear la base de datos.' ROLLBACK END
Con este trigger, cualquier intento de crear una nueva base de datos será bloqueado, asegurando que sólo los administradores autorizados puedan realizar estos cambios.
¿Cuáles son los beneficios de usar triggers administrativos?
Los triggers administrativos ofrecen una capa adicional de seguridad y control en la administración de bases de datos y servidores. Entre sus beneficios se encuentran:
¿Qué deben tener en cuenta los administradores que implementan triggers?
El uso de triggers administrativos requiere una planificación cuidadosa para no bloquear operaciones legítimas ni afectar el rendimiento de las bases de datos. Los administradores deben:
Con estos consejos, los administradores pueden maximizar la efectividad de los triggers administrativos y mejorar la seguridad y estabilidad de sus sistemas.
Enrique Alexis Lopez Araujo
Estudiante
Daniel G Perico Sánchez
Estudiante
Luis Sandoval
Estudiante
Jeiner Muñoz Olivera
Estudiante
Eder Yerson Garcés Perez
Estudiante
Danny Cortez
Estudiante
DAVID EDUARDO BAEZ SANCHEZ
Estudiante
Iván González Mendoza
Estudiante
JONATHAN VERA SEGURA
Estudiante
Felipe Gil Valencia
Estudiante
Dionny Jose Contreras Chacon
Estudiante
Roy Rojas
Profesor
Abraham Gerardo Valdez Hernandez
Estudiante
Arnold Josias Bertrand Mejia
Estudiante
Adrian Castillo Ramírez
EstudianteAdrian Castillo Ramírez
EstudianteAdrian Castillo Ramírez
Estudiante
Khelvyn Carrasquel
EstudianteRoy Rojas
Profesor
Juan Garrido
EstudianteRoy Rojas
Profesor
Josadec Pedraza
Estudiante
Luis Quiroz Prada
EstudianteLuis Quiroz Prada
Estudiante
Juan David Forero
Estudiante
KEVIN Cordon
Estudiante
Rodrigo Rojas Candia
Estudiante
ANDRES ALFONSO MIRA MEJIA
Estudiante🎉🔥Yo creo este ejemplo de un trigger donde cada vez que se insertara un usuario me iba a llegar un correo en tabla de cada usuario registrado, para esto el requerimiento es haber configurado si o si sp_send_dbmail y agregarle los parametros en donde se ejecuta el profile y el correo 💚ya agregue una columna llamada load_date para verificar el ultimo(s) usuarios nuevos dentro de la tabla UsuarioTarget
CREATE TRIGGER [dbo].[send_email_insert_usuario_new] ON [dbo].[UsuarioTarget] FOR INSERT AS BEGIN SET NOCOUNT ON; SET ANSI_WARNINGS OFF DECLARE @database NVARCHAR(MAX) = N'' DECLARE @tableHTML nvarchar(max) DECLARE @Table NVARCHAR(MAX) = N'' DECLARE @subject_content NVARCHAR(MAX) = N'' SELECT @Table = @Table +'<tr style="background-color:'+CASE WHEN (ROW_NUMBER() OVER (ORDER BY id))%2 =1 THEN '#A3E0FF' ELSE '#8ED1FB' END +';">' + '<td>' + CAST(CONVERT(varchar, CAST(RTRIM((id)) AS varchar), 1) AS varchar) + '</td>' + '<td>' + CAST(CONVERT(varchar, CAST(RTRIM((name)) AS varchar), 1) AS varchar) + '</td>' + '<td>' + CAST(CONVERT(varchar, CAST((load_date) AS date), 1) AS varchar) + '</td>' + '</tr>' FROM [dbo].UsuarioTarget WHERE (load_date > GETDATE() - 1) select @database = (select DB_NAME() AS [Current Database]) SET @subject_content = 'User Record Inserted in ' + @database SET @tableHTML = N'<H3>Catalogue Users</H3>' + N'<table border="1" align="center" cellpadding="2" cellspacing="0" style="font-family: "Trebuchet MS", Arial, Helvetica, sans-serif;text-align:center; border-collapse: collapse; width: 100%;" >' + N'<tr style ="font-size: 14px;font-weight: normal;background: #b9c9fe;"> <th>User ID</th> <th>Name User</th> <th>Date</th>' + @Table + N'</table>' + N'<br>' + N'<br>' print @database IF EXISTS (SELECT MAX(load_date) FROM UsuarioTarget ) BEGIN EXEC msdb.dbo.sp_send_dbmail @recipients = 'test@test.com', @profile_name = 'Email', @subject = @subject_content, @body = @tableHTML, @body_format = 'HTML' ; END END go
Muy buen aporte, ¡muchas gracias!
Que poderoso pueden ser los triggers de lado de la administración de servers para Base de Datos.
Los triggers nos facilitan la vida. Los usaré de manera más seguida. :D
Te recomiendo que no abuses de ellos
Los triggers son para usos especificos, abusar de ellos puede provocar un mal rendimiento en las bases de datos.
En nuestra empresa se crean triggers de servidor, para mejorar la seguridad de acceso. Se valida, servidor, programa donde se ejecuta. Este es un ejemplo, ell de nuestra empresa es mucho más complejo y genera auditoria adicional.
CREATE TRIGGER strRejectSSMSConnectionForSQLLogin1 ON ALL SERVER FOR LOGON AS BEGIN IF ORIGINAL_LOGIN() = N'SQLLogin1' AND PROGRAM_NAME() LIKE N'Microsoft SQL Server Management Studio%' BEGIN RAISERROR('Direct connection by SSMS refused.', 16, 1) ROLLBACK END END
Normalmente había considerado los triggers solo para cuestiones relacionadas con tablas de información, no tanto para cuestiones administrativas. Me resulta muy interesante las aplicaciones adicionales que pueden tener.
No puedo borrar el trigger administrativo ? donde esta alojado no lo encuentro.
Creo que mientras no se tenga un usuario configurado se puede usar DROP TRIGGER con el nivel que se definió en este.
Para el caso de los triggers de la clase serian.
Para el primero:
DROP TRIGGER validaTablas ON DATABASE;
Para el segundo:
DROP TRIGGER validaBaseDatos ON ALL SERVER;
Sin implemento el Trigger que no permite modificación de la estructura de la BD por parte de otros usuarios, como podría yo como DBA realizar el cambio? borrando el Trigger? y como podria hacerlo para que no modifiquen Store procedures?
En el trigger puedes agregar reglas, puedes obtener el usuario que está ejecutando la consulta y si sos vos por ejemplo, no hacer rollback, o bien puedes deshabilitar el trigger momentaneamente.
Sígueme en twitter @royrojasdev donde regularmente publico sobre SQL
Creo que este ejemplo de uso de triggers a nivel BD no es muy práctico. Ya que estas haciendo lo que se puede hacer mediante los permisos. Un mejor ejemplo seria que mande notificaciones cada que creen,modifiquen o eliminen algun objeto los que tienen el permiso.
Si pero no exactamente, existe el problema que los programadores utilizan los usuarios de las aplicaciones para conectarse a las bases de datos y realizar operaciones como DELETE o DROP, que pasa? que no se puede restringir a nivel de red siempre porque hay empresas que les dan la facilidad a los DEV para acceder a las bases de datos de producción. Por lo que este, que no afecta el performance puesto que estas operaciones no se suelen hacer a nivel de aplicación, suelen ser la mejor opcion.
Sí, puedes agregar un trigger que utilice la cláusula OUTPUT para capturar cambios en la tabla. En el contexto de tu pregunta, puedes usar la sintaxis que mencionas para recuperar tanto los valores antiguos (DELETED) como los nuevos (INSERTED) de la tabla. Este enfoque te permite monitorear qué cambios se realizaron en las columnas específicas.
El trigger se podría definir así:
CREATE TRIGGER nombre_del_trigger ON nombre_de_la_tabla AFTER INSERT, UPDATE, DELETE AS BEGIN SELECT DELETED.codigo AS targetcodigo, DELETED.Nombre AS targetnombre, DELETED.Puntos AS target.puntos, INSERTED.codigo AS targetcodigo, INSERTED.Nombre AS targetnombre, INSERTED.Puntos AS target.puntos FROM DELETED FULL OUTER JOIN INSERTED ON DELETED.id = INSERTED.id; -- Asegúrate de usar la clave primaria para unir END;
Esto te permitirá ver los cambios en la tabla al momento de la inserción, actualización o eliminación.
Se puede implementar un trigger en SQL Server para limitar la actualización de más de 100 valores en una tabla. Esto se lograría utilizando un trigger BEFORE UPDATE que cuente las filas afectadas y genere un error si supera el límite deseado. Por ejemplo:
CREATE TRIGGER LimitarActualizaciones ON TuTabla INSTEAD OF UPDATE AS BEGIN IF (SELECT COUNT(*) FROM inserted) > 100 BEGIN RAISERROR('No se pueden actualizar más de 100 registros a la vez.', 16, 1); ROLLBACK TRANSACTION; END ELSE BEGIN -- Código para realizar la actualización UPDATE TuTabla SET Columna = i.Columna FROM inserted i WHERE TuTabla.Id = i.Id; END END
Este enfoque asegura que no se realicen actualizaciones indeseadas en la base de datos.
En los triggers de SQL Server, es posible agregar condiciones para validar si un usuario específico puede realizar modificaciones. Para hacerlo, puedes usar la función ORIGINAL_LOGIN() en tu trigger. Por ejemplo, puedes modificar el código del trigger para que solo permita cambios si el usuario es uno específico:
CREATE TRIGGER ValidaTablas ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS BEGIN IF ORIGINAL_LOGIN() <> 'usuario_permitido' BEGIN PRINT 'No es permitido modificar la estructura de la tabla. Comuníquese con el DBA.'; ROLLBACK; END END;
Esto permitirá que solo el usuario especificado pueda realizar modificaciones mientras que los demás serán bloqueados.
como se pudiera generar un trigger para conocer si eliminaron y agregaron un nuevo usuario
CREATE TRIGGER CatchUser ON DATABASE FOR CREATE_USER, DROP_USER AS BEGIN SET NOCOUNT ON; ........
Como puedo crear un trigger que bloquee todos los update o alter table a la DB, pero sin que ese trigger afecte al usuario administrador, porque en el ejemplo el trigger creado afecta a todos los usuario y cuando se necesita hacer un update se tiene que eliminar el trigger.
CREATE TRIGGER safety
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
Aqui agregas la logica donde valida
si detectas algo incorrecto o un usuario invalido haces
ROLLBACK;
Buenas tardes amigos,
A lo largo del curso veo que usar el OR ALTER en mi version de MS SQL me marca este error
Msg 156, Level 15, State 1, Line 5 Incorrect syntax near the keyword 'OR'. Msg 111, Level 15, State 1, Line 5 'ALTER TRIGGER' must be the first statement in a query batch. Completion time: 2023-02-20T15:27:49.1564781-08:00
Saben por que?
Para controlar que una tabla sea modificada o recibir notificaciones cuando esto suceda, puedes implementar triggers administrativos en SQL Server. Un trigger es un tipo de procedimiento almacenado que se ejecuta automáticamente en respuesta a ciertos eventos en la base de datos, como modificaciones en las tablas.
Por ejemplo, puedes crear un trigger que se active al intentar realizar un DROP TABLE o ALTER TABLE. Dentro del trigger, puedes incluir un mensaje de error y un ROLLBACK para evitar que la modificación se lleve a cabo. Así, si alguien intenta alterar la estructura de la tabla, recibirán una notificación indicando que no está permitido modificarla.
Implementar estos triggers asegura la integridad de la base de datos y permite que los administradores mantengan control sobre las modificaciones estructurales.
Estos triggers permiten una gestión y control más eficiente de la base de datos.
Las ultimas clases si están muy interesantes, y mas con el uso de los triggers para tener mas control como administrador.
en mi trabajo hacen esas restricciones a nivel de permisos del usuario, solo hay un usuario dba que puede crear, y los demas solo pueden consultar e insertar y cada usuario tiene permiso a ciertas tablas por el rol... base de datos ORACLE
MI ejemplo del Alter Update y el Insert:
ALTER TRIGGER T_Insert ON UsuarioTarget AFTER INSERT AS BEGIN IF (ROWCOUNT_BIG() = 0) RETURN; DECLARE @codigo int SELECT @codigo = codigo from inserted IF @codigo = 7 BEGIN Print 'No se realizo el Insert' ROLLBACK; RETURN; END
--SELECT Codigo,Nombre,Puntos --FROM INSERTED Print 'Se realizo el insert' END GO
ALTER TRIGGER T_Update ON UsuarioTarget AFTER UPDATE AS BEGIN IF (ROWCOUNT_BIG() = 0) RETURN; DECLARE @codigo int SELECT @codigo = codigo from inserted IF @codigo = 7 BEGIN Print 'No se realizo el Update' ROLLBACK; RETURN; END --SELECT Codigo,Nombre,Puntos --FROM INSERTED Print 'Se realizo el Update' END GO
No tenía presente el uso de los trigger, por ahora entiendo que se puede controlar errores, usar para temas administrativos; Se debe tener cuidado con el uso de ellos.
