1 respuesta en este tema

[Kipow]

Bueno no se si alguien ya ha utilizado los triggers de bases de datos, quiero proteger mi metadata de accesos no autorizados pero aun no se me ocurre como poder utilizar estos eventos para lograrlo. alguna idea?

[Rolphy Reyes]

Saludos.

En la actualidad, lamentandolo mucho, no existe una manera sencilla de hacer esa operación que pides.

Los triggers de base de datos no te servirán para eso, no existe una manera que dependiendo del usuario se pueda esconder o no la meta data.

Los desarrolladores que han tocado este tema difieren entre no hacerlo y hacerlo por varias razones, aquí una cita de Ibphoenix:

When you write and define a stored procedure or trigger for a Firebird database, the server stores an almost complete copy of the procedure source code along with a "compiled" copy referred to as BLR (Binary Language Representation). It is the BLR that is executed by the server, the source code is not used.

Some developers attempt to protect at least some of their database metadata by deleting the source code from the database before distributing the database (a simple direct update against the relevant metadata table fields). I recommend that you don't do this for two reasons.

BLR is a fairly simplistic encoding of the source code. It would not be difficult to decode the BLR back to a human readable form. Such a decoding would be without comments and formatting, but the SQL that goes into a stored procedure or trigger is rarely so complicated that this would cause much of a problem. Hence the protection offered by the removal of source code is not very significant.

Secondly the source code can be useful for other purposes. It allows fixes to be applied directly to the database without needing to bring in the full source from elsewhere (and then remembering to remove it again when the fix is applied). The source code is also used by various utilities, such as my own DBak application – an alternative backup program to "gbak". I have not bothered to write my own BLR decoder at this stage, so DBak relies on the availability of the source code in order to be able to build a DDL script to reconstruct a database.

Aquí te pongo unas sentencias que puedes utilizar, pero debes de tener en cuenta que solo debes de hacerlo en tu BD de producción no en la de desarrollo, también de que al realizar algún Update se restaura la meta data:

sql

UPDATE RDB$PROCEDURES
SET RDB$PROCEDURE_SOURCE = NULL
WHERE RDB$SYSTEM_FLAG IS NULL OR RDB$SYSTEM_FLAG = 0;
 
UPDATE RDB$TRIGGERS
SET RDB$TRIGGER_SOURCE = NULL
WHERE RDB$SYSTEM_FLAG IS NULL OR RDB$SYSTEM_FLAG = 0;
 
UPDATE RDB$RELATIONS
SET RDB$VIEW_SOURCE = NULL
WHERE (RDB$SYSTEM_FLAG IS NULL OR RDB$SYSTEM_FLAG = 0)
AND RDB$VIEW_BLR IS NOT NULL;

Hasta luego.