Evitando que un usuario se conecte más de una vez a la Base de Datos

14 comentarios

A veces quieres que un usuario no pueda conectarse desde más de una computadora al mismo tiempo, o desde la misma computadora más de una vez. Eso es especialmente importante en las aplicaciones de seguridad o donde se maneja dinero. Entonces, ¿cómo evitamos que lo haga?

En nuestra ayuda vienen los triggers de las bases de datos. Estos se disparan cuando alguien se conecta o se desconecta de la Base de Datos. Lo que podemos hacer es crear una tabla, la llamamos por ejemplo CONECTADOS y cada vez que alguien se conecta insertamos una fila a esta tabla, y cada vez que se desconecta borramos la fila que le corresponde.

CONECTADOS1

Captura 1. Si haces clic en la imagen la verás más grande

La columna CON_NOMBRE se encuentra en una restricción Unique Key, como podemos ver en su DDL:

ALTER TABLE CONECTADOS ADD CONSTRAINT UQ_CONECTADOS UNIQUE (CON_NOMBRE);

¿Por qué?

Porque de esa manera no tendremos que verificar nosotros que un usuario no se conecte más de una vez, el Firebird hará esa tarea por nosotros.

Si no tuviéramos esa restricción Unique Key entonces tendríamos que buscar el nombre del usuario en la tabla CONECTADOS y si existe allí entonces lanzar una excepción para evitar que se conecte. Pero al tener la restricción no es necesario que hagamos eso, ya que el mismo Firebird lanzará automáticamente la excepción. O sea, nos ahorramos trabajo.

En un trigger de la Base de Datos que se dispara cuando alguien se conecta haríamos el intento de insertar una fila en la tabla CONECTADOS, algo como:

CREATE TRIGGER INSERTAR_CONECTADO
   ACTIVE ON CONNECT
   POSITION 2
AS
BEGIN

   INSERT INTO CONECTADOS
              (CON_IDENTI, CON_NOMBRE , CON_TIMEST)
       VALUES (0 , CURRENT_USER, CURRENT_TIMESTAMP);

END;

Si el nombre del usuario no existe en la tabla CONECTADOS entonces se conectará sin problemas, pero si existe entonces el Firebird lanzará una excepción “violation of PRIMAY or UNIQUE KEY constraint …”

Y como lanzó una excepción el usuario no podrá conectarse.

No debemos olvidarnos de crear otro trigger de la Base de Datos, el que se encargará de borrar la fila que le corresponde al usuario de la tabla CONECTADOS, sería algo como:

CREATE TRIGGER BORRAR_CONECTADO
   ACTIVE ON DISCONNECT
   POSITION 3
AS
BEGIN

   DELETE FROM
      CONECTADOS
   WHERE
      CON_NOMBRE = CURRENT_USER;

END;

Entonces, cuando el usuario se desconecta la fila con su nombre será borrada de la tabla CONECTADOS.

¿Y qué ocurre si se apagó anormalmente la computadora donde se encuentra el Servidor del Firebird?

Si las cosas se hacen bien y como corresponde entonces la computadora donde se encuentra el Servidor del Firebird jamás debería apagarse intempestivamente, debería tener sí o sí una UPS en buen estado y nunca se apagaría esa computadora si hay alguien conectado a la Base de Datos.

Pero sabemos que no siempre las cosas se hacen correctamente. En ese caso los usuarios que hubieran estado conectados a la Base de Datos no podrán volver a conectarse y se requerirá que otro usuario, alguien que no estaba conectado, borre los nombres de dichos usuarios de la tabla CONECTADOS.

Luego de eso ya podrán conectarse nuevamente.

Ventaja adicional:

Este método además de evitar que un usuario se conecte más de una vez también tiene la ventaja de que siempre podremos saber quienes son TODOS los usuarios conectados.

CONECTADOS2

Captura 2. Si haces clic en la imagen la verás más grande

Como podemos ver en la Captura 2. un simple SELECT nos dirá los nombres de cada usuario conectado y también la fecha y hora de su conexión.

Conclusión:

Si queremos evitar que un usuario se conecte más de una vez a la Base de Datos y al mismo tiempo entonces podemos crear una tabla y dos triggers: uno que se disparará cuando intenta conectarse y el otro que se disparará cuando se desconecta.

De esta manera solamente podrá conectarse una vez. Tendrá que desconectarse para luego volver a conectarse.

Adicionalmente este método tiene la ventaja de que en cualquier momento podremos saber quienes son los usuarios conectados y cuando se conectaron.

Artículos relacionados:

Como evitar que se conecten a una Base de Datos

Impidiendo la conexión a una Base de Datos

El índice del blog Firebird21

El foro del blog Firebird21

Impidiendo la conexión a una Base de Datos

4 comentarios

En ocasiones queremos evitar que un usuario, un proceso o una computadora se conecten a nuestra Base de Datos. O varios usuarios, varios procesos o varias computadoras.

Ya habíamos visto algunos artículos sobre este tema:

Como evitar que se conecten a una Base de Datos

Evitando que un usuario se conecte más de una vez a la Base de Datos

Evitando que un usuario se conecte más de una vez (método mejorado)

En el presente artículo nos explayaremos más sobre como evitar las conexiones indeseadas.

A partir del Firebird 2.1 podemos usar los llamados triggers de las bases de datos (database triggers, en inglés) y nos servirán perfectamente para lo que pretendemos lograr.

El primer paso es crear una excepción, la cual mostrará un mensaje al usuario. En realidad esto no es obligatorio, podríamos dejar que el Firebird muestre su mensaje por defecto pero ese mensaje estará en inglés. Si queremos personalizar el mensaje entonces deberemos crear una excepción, como la siguiente:

CREATE EXCEPTION
   E_ACCESO_NO_PERMITIDO 'No tienes permiso para conectarte a esta Base de Datos' ;

A continuación crearemos los triggers de bases de datos que necesitaremos:

Un trigger para evitar que los usuarios se conecten a la Base de Datos:

Listado 1.

CREATE TRIGGER NEW_DBTRIGGER_C
   ACTIVE ON CONNECT
   POSITION 2
AS
BEGIN

   IF (CURRENT_USER IN ('JUAN', 'MARIA', 'ESTELA','MONICA')) THEN
      EXCEPTION E_ACCESO_NO_PERMITIDO;

END;

Ningún usuario cuyo nombre esté dentro del IN (en este caso: JUAN, MARIA, ESTELA y MONICA) podrá conectarse a la Base de Datos.

Un trigger para evitar que los programas se conecten a la Base de Datos:

Listado 2.

CREATE TRIGGER NEW_DBTRIGGER_C1
   ACTIVE ON CONNECT
   POSITION 3
AS
BEGIN

   IF (EXISTS(SELECT
                 *
              FROM
                 MON$ATTACHMENTS M
              WHERE
                 M.MON$ATTACHMENT_ID = CURRENT_CONNECTION AND
                (NOT M.MON$REMOTE_PROCESS IN ('MI_PROG1.EXE', 'MI_PROG2.EXE')))) THEN
      EXCEPTION E_ACCESO_NO_PERMITIDO;

END;

En este caso solamente permitiremos el acceso a los programas cuyos nombres se encuentren en el IN, o sea: MI_PROG1.EXE y MI_PROG2.EXE. Evidentemente que tú escribirías allí los nombres de tus propios programas.

¿Cuál es el defecto que tiene esta protección? Que si el intruso sabe que MI_PROG1.EXE puede conectarse entonces podría renombrar a su propio programa como MI_PROG1.EXE y así conseguirá la conexión. Por eso siempre es importante que los mensajes de las excepciones le den al intruso muy poca información. Sería muy mala idea que el mensaje sea algo como: “Solamente se pueden conectar MI_PROG1.EXE y MI_PROG2.EXE” porque en ese caso lo primero que se le ocurrirá al intruso será renombrar a su programa y conseguirá conectarse. Además, hay que tomar en cuenta otro aspecto: no se debe impedir el acceso a los programas legítimos que podamos necesitar, como: GBAK.EXE

Un trigger para evitar que desde una computadora se conecten a la Base de Datos:

Listado 3.

CREATE TRIGGER NEW_DBTRIGGER_C2
   ACTIVE ON CONNECT
   POSITION 4
AS
BEGIN

   IF (EXISTS(SELECT
                 1
              FROM
                 MON$ATTACHMENTS M
              WHERE
                 M.MON$ATTACHMENT_ID = CURRENT_CONNECTION AND
                 M.MON$REMOTE_ADDRESS IN ('192.168.0.100', '192.168.0.104'))) THEN
      EXCEPTION E_ACCESO_NO_PERMITIDO;

END;

En este caso, las computadoras cuyos IP son 192.168.0.100 ó 192.168.0.104 jamás podrán conectarse a nuestra Base de Datos, siempre se les rechazarán todos los intentos de conexión.

Un trigger para evitar que desde una subred se conecten a la Base de Datos:

Listado 4.

A veces queremos que se rechace la conexión desde toda una subred y escribir todos los IP después del IN como se mostró en el ejemplo anterior sería muy largo y muy tedioso. Para esos casos tenemos una mucha mejor alternativa:


CREATE TRIGGER NEW_DBTRIGGER_C3
   ACTIVE ON CONNECT
   POSITION 4
AS
BEGIN

   IF (EXISTS(SELECT
                 1
              FROM
                 MON$ATTACHMENTS M
              WHERE
                 M.MON$ATTACHMENT_ID = CURRENT_CONNECTION AND
                 M.MON$REMOTE_ADDRESS STARTING WITH '192.168.14')) THEN
      EXCEPTION E_ACCESO_NO_PERMITIDO;

END;

Aquí estamos rechazando las conexiones de todas las computadoras de la subred 192.168.14, o en otras palabras cualquier computadora cuyo IP empiece con esos números será rechazada. Esto puede ser muy útil cuando por ejemplo tenemos una Base de Datos para los sueldos y jornales de los empleados y no queremos que desde otras computadoras de la Empresa puedan curiosear en ella. Entonces, de un plumazo las rechazamos a todas las que no se encuentran en nuestra misma subred.

Un trigger para evitar que se conecten fuera del horario autorizado:

Si el horario laboral de la empresa va de 8:00 a 18:00 entonces sería muy sospechoso que alguien tratara de conectarse a las 23:45 ¿verdad? ¿Por qué alguien querría conectarse a esa hora? Para estos casos podríamos tener un trigger que restrigiera el acceso fuera del horario establecido.

Listado 5.

CREATE TRIGGER NEW_DBTRIGGER_C4
   ACTIVE ON CONNECT
   POSITION 5
AS
BEGIN

 IF (CURRENT_USER <> 'SYSDBA' AND
     CURRENT_TIME < '07:45:00' OR CURRENT_TIME > '18:15:00') THEN
    EXCEPTION E_ACCESO_NO_PERMITIDO;

END;

Aquí, solamente al usuario SYSDBA le permitimos conectarse a cualquier hora que desee, todos los demás usuarios deberán iniciar la conexión entre las 07:45:00 y las 18:15:00

Desde luego que también podríamos restringir la conexión por los días de la semana: de Lunes a Viernes tendrían un horario de conexión, los Sábados tendrían otro horario de conexión y los Domingos tendrían otro horario.

O podríamos realizar combinaciones: MARCELA tiene un horario, SILVIA tiene otro horario, SUSANA tiene otro horario, etc.

Conclusión:

Para mantener la confidencialidad y la seguridad de los datos y de la información muchas veces es muy importante impedir que personas no autorizadas puedan conectarse a la Base de Datos. Esas personas podrían tener derechos de conexión a otras bases de datos de la Empresa, pero no a esta Base de Datos.

Usando los triggers de las bases de datos podemos impedir (o al menos dificultar) que se conecten quienes no deberían conectarse. Pero recuerda que ningún método es infalible. El usuario SYSDBA y el creador de la Base de Datos siempre podrán conectarse. Y podrían irse a almorzar dejando la conexión abierta y el intruso aprovechar la ocasión.

Hay muchísimas combinaciones más que puedes realizar pero con los ejemplos anteriores ya tendrás una buena idea de lo que se puede lograr.

Artículos relacionados:

Como evitar que se conecten a una Base de Datos

Evitando que un usuario se conecte más de una vez a la Base de Datos

Evitando que un usuario se conecte más de una vez (método mejorado)

El índice del blog Firebird21

El foro del blog Firebird21

Escribiendo un trigger

5 comentarios

En este artículo habíamos visto como escribir un stored procedure:

https://firebird21.wordpress.com/2014/03/13/escribiendo-un-stored-procedure/

Ahora veremos como escribir un trigger.

En realidad son muy parecidos, hay pocas diferencias entre ellos pero es muy importante que sepas cuales son, puedes leer más en este artículo:

https://firebird21.wordpress.com/2013/06/28/similitudes-y-diferencias-entre-stored-procedures-y-triggers/

Cosas que debes saber sobre los triggers:

  1. Tú nunca pides que se ejecute un trigger, el trigger siempre se ejecuta automáticamente
  2. Los triggers no pueden recibir parámetros de entrada
  3. Los triggers no pueden devolver parámetros de salida
  4. Dentro de los triggers puedes usar las variables booleanas INSERTING, UPDATING, DELETING
  5. Dentro de los triggers existen las pseudo-variables NEW y OLD
  6. Dentro de los triggers no puedes usar el comando SUSPEND
  7. Los triggers se ejecutan siguiendo el orden establecido en POSITION. O sea que primero se ejecuta POSITION 0, luego POSITION 1, luego POSITION 2, etc.

Tipos de trigger

Hay dos tipos de trigger:

  1. BEFORE
  2. AFTER

Los triggers BEFORE se ejecutan antes de que la operación de inserción, actualización, borrado, sea realizada. ¿Para qué se los usa? Para validar que las columnas tengan solamente valores correctos.

Los triggers AFTER se ejecutan después de que la operación de inserción, actualización, borrado sea realizada. ¿Para qué se los usa? Típicamente para cambiar el contenido de otra tabla, o de otra fila dentro de la misma tabla.

Ejemplo 1:

Queremos asegurarnos de que al hacer una venta se registre el precio de venta del producto.

CREATE TRIGGER VENTASDET_BI FOR VENTASDET
   ACTIVE BEFORE INSERT OR UPDATE
   POSITION 0
AS
BEGIN

   IF(NEW.VEN_MONTOX IS NULL OR NEW.VEN_MONTOX <= 0) THEN
      EXCEPTION E_SIN_IMPORTE;

END;

La pseudo-variable NEW.VEN_MONTOX tiene el valor que se quiere guardar en la columna VEN_MONTOX. Si ese valor es NULL o es menor o igual que cero eso es un error, no se debe permitir. Por lo tanto se eleva una excepción llamada E_SIN_IMPORTE. Siempre que se eleva una excepción el procesamiento se detiene en ese punto. ¿La consecuencia? que los usuarios jamás podrán grabar una fila si el valor que quieren poner en la columna VEN_MONTOX es NULL o es menor o igual que cero.

En otras palabras, hemos validado que la columna VEN_MONTOX siempre tenga un valor mayor que cero. Hagan lo que hagan los usuarios jamás conseguirán guardar en esa columna un valor NULL, cero, o negativo.

Ejemplo 2:

Queremos asegurarnos que el porcentaje de ganancia siempre sea del 5% o más, nunca debería ser menor.


CREATE TRIGGER PRODUCTOS_BIU FOR PRODUCTOS
   ACTIVE BEFORE INSERT OR UPDATE
   POSITION 1
AS
BEGIN

   IF (NEW.PRD_PREVTA * 100 / NEW.PRD_PRECTO - 100 < 5) THEN
      EXCEPTION E_PORCENTAJE_MENOR_5
         'El porcentaje de ganancia es menor que el 5%. Identificador = ' || NEW.PRD_IDENTI ;

   WHEN EXCEPTION E_PORCENTAJE_MENOR_5 DO BEGIN
      IN AUTONOMOUS TRANSACTION DO
         INSERT INTO ERRORES
                   (ERR_MODULO, ERR_COMENT)
            VALUES ('PRODUCTOS_BIU' , 'El % de ganancia del producto ' || NEW.PRD_NOMBRE || ' es menor que 5%') ;
      EXCEPTION;
   END

END;

En este caso si el porcentaje de ganancia es menor que el 5% lanzamos la excepción E_PORCENTAJE_MENOR_5. Al lanzar una excepción el procesamiento se detiene en ese punto. Luego capturamos la excepción. ¿Por qué la capturamos? Porque queremos guardar en la tabla ERRORES el error que encontramos, así más tarde podremos revisar esa tabla y ver todos los errores que ocurrieron. Si no capturamos la excepción entonces el producto no será grabado en la tabla PRODUCTOS pero no sabremos el motivo. Recuerda que puede haber muchos motivos por los cuales no se lo pudo grabar (Primary Key duplicada, Unique Key duplicada, restricción Check no cumplida, etc.). Finalmente relanzamos la excepción. ¿Para qué? Para que el Servidor del Firebird no grabe la fila y le diga al Cliente (y el Cliente se lo dirá a nuestra aplicación) que ocurrió una excepción.

Entonces nuestro trigger hace lo siguiente: Valida que el porcentaje de ganancia sea igual o mayor que el 5%. Si lo es, continúa normalmente. Si no lo es, inserta una fila en la tabla ERRORES.

Ejemplo 3:

Queremos asegurarnos de que exista la Sucursal que se quiere usar.


CREATE TRIGGER BIU_PRODUCTOS FOR PRODUCTOS
   ACTIVE BEFORE INSERT OR UPDATE
   POSITION 1
AS
BEGIN

   IF(NEW.PRD_CODSUC NOT IN (SELECT SUC_CODIGO FROM SUCURSALES)) THEN
      EXCEPTION E_SIN_SUCURSAL;

END;

Lo que estamos diciendo aquí es: si la Sucursal que se quiere grabar no existe en la tabla SUCURSALES entonces lanzar una excepción para que no se grabe este producto.

O sea, nos aseguramos que en la columna PRD_CODSUC se tenga el código de una Sucursal que existe.

Ejemplo 4:

Tenemos una tabla donde guardamos los saldos acumulados de cada cuenta contable. Cuando el usuario quiere ver un Balance de Sumas y Saldos, un Balance General o un Estado de Resultados lo que hacemos es mostrarle el contenido de esa tabla de saldos acumulados. Pero si alguno de los asientos contables ha cambiado entonces se debe reprocesar la tabla de saldos acumulados. ¿Cómo sabemos antes de mostrar un informe si debemos reprocesar la tabla de saldos acumulados? Leyendo el valor de la columna CON_RECSAL (recalcular saldos) de la tabla CONFIGURACIÓN. Si la columna CON_RECSAL tiene una “V” (que significa “verdadero”) entonces sí debemos recalcular los saldos acumulados.


CREATE TRIGGER AIUD_ASIENTOSDET FOR ASIENTOSDET
   ACTIVE AFTER INSERT OR UPDATE OR DELETE
   POSITION 2
AS
BEGIN

   UPDATE OR INSERT INTO CONFIGURACION
            (CON_IDENTI,CON_RECSAL)
     VALUES (1         , 'V');

END;

Fíjate que este es un trigger que se ejecutará después de que la inserción, actualización o borrado se haya realizado en la tabla ASIENTOSDET. ¿Por qué después? Porque dice que se activará AFTER. Y la palabra inglesa “after” significa “después”.

En otras palabras, este trigger solamente se ejecutará si ningún trigger BEFORE lanzó una excepción. Si un trigger BEFORE lanzó una excepción o un trigger AFTER con un valor de POSITION menor que 2 lanzó una excepción entonces este trigger jamás se ejecutará.

Si este trigger llega a ejecutarse eso significa que a la tabla ASIENTOSDET exitosamente se le insertó, actualizó o borró una fila. Y en ese caso actualiza a las columnas CON_IDENTI y CON_RECSAL de la tabla CONFIGURACIÓN. ¿Para qué? Para que sepamos que las columnas de la tabla SALDOS_ACUMULADOS tienen valores incorrectos y volvamos a calcularlos.

Conclusión:

Los triggers son una de las herramientas poderosísimas que nos provee el Firebird y debemos utilizarlos al máximo para tener unas aplicaciones robustas y súper confiables.

Un buen uso de los triggers nos asegurará de que jamás tengamos basura en nuestra Base de Datos (se le llama “basura” a los datos que están pero que no deberían estar).

Artículos relacionados:

Entendiendo a los triggers

Uso de los dos puntos dentro de un stored procedure o trigger

Similitudes y diferencias entre stored procedures y triggers

El índice del blog Firebird21

Capturando una excepción y continuando con el procesamiento

7 comentarios

Supongamos la siguiente situación: tenemos que insertar 100 filas en una tabla, pero no serán insertadas manualmente sino leídas desde otra tabla o archivo. En otras palabras, un proceso por lotes.

Pero si alguna de esas 100 filas tiene algún problema el proceso debería continuar. La fila problemática no será insertada (o será insertada con alguno de sus valores originales cambiados) pero las restantes 99 filas sí deberán ser insertadas.

Por ejemplo, la fila 27 tiene un error: no cumple con una restricción UNIQUE KEY.

Así que aquí tenemos dos opciones:

  1. No capturamos la excepción que detecta la violación a las restricciones UNIQUE KEY
  2. Capturamos la excepción que detecta la violación a las restricciones UNIQUE KEY

Si elegimos la opción 1. entonces el Servidor del Firebird cuando encuentre una violación a la restricción UNIQUE KEY detendrá el procesamiento y las filas 27 a 100 nunca serán insertadas. Las filas 1 a 26 serán insertadas o no dependiendo de como se manejan las transacciones: si después de cada INSERT hay un COMMIT entonces sí habrán sido insertadas, pero si el COMMIT se ejecutará solamente después de procesar las 100 filas, entonces no serán insertadas. Todo esto implica que si elegimos la opción 1. entonces es seguro que (algunas, muchas, o todas) las filas no serán insertadas si ocurre una violación a la restricción UNIQUE KEY.

Si elegimos la opción 2. tenemos más control sobre lo que ocurrirá cuando se detecte una violación a la restricción UNIQUE KEY. Al capturar una excepción:

  • Podemos manejarla y “engañarle” al Firebird diciéndole que tal excepción nunca ocurrió
  • Podemos manejarla y decirle al Firebird que continúe con sus acciones normales. O sea que a partir de ahí continuará como si hubiéramos elegido la opción 1. Decirle al Firebird que continúe con sus acciones normales después de que nosotros hayamos manejado la excepción se llama “relanzar la excepción”.

En general, aunque no es obligatorio, es muy útil escribir en una tabla de LOG los errores que causaron que una excepción ocurriera. Así, más adelante podremos revisar esa tabla de LOG y descubrir que fue lo que anduvo mal. Y tomar las medidas adecuadas para que no vuelva a ocurrir.

Múltiples niveles

A veces, tenemos un caso como el siguiente:

El stored procedure 1 llama al stored procedure 2 el cual llama al stored procedure 3

Servidor de Firebird —> stored procedure 1 —> stored procedure 2 —> stored procedure 3

¿Qué ocurre con las excepciones en ese caso?

Si la excepción ocurrió en el stored procedure 3 entonces éste puede manejarla o no. Si la manejó puede relanzarla o no. Si no la manejó o la relanzó entonces la excepción llegará al stored procedure 2. Así mismo, el stored procedure 2 puede manejar la excepción que recibió del stored procedure 3 o no. Si no la manejó o si la relanzó entonces la excepción llegará al stored procedure 1. También el stored procedure 1 puede manejar o no la excepción que le llegó desde el stored procedure 2. Si no la manejó o si la relanzó entonces la excepción llegará al Servidor del Firebird.

Si la excepción llega al Servidor del Firebird entonces allí mismo se detiene el procesamiento. Se termina.

Pero si la excepción nunca llegó al Servidor del Firebird entonces el procesamiento continuará normalmente, como si la excepción nunca hubiera ocurrido.

En nuestro ejemplo de las 100 filas a insertar con la fila 27 teniendo problemas, si alguno de los stored procedures capturó la excepción y no la relanzó entonces el Servidor del Firebird nunca sabrá que tal excepción ocurrió. Y por ello continuará procesando normalmente a las restantes filas.

CREATE PROCEDURE MISTOREDPROCEDURE3
AS
   DECLARE VARIABLE lcNombre VARCHAR(128);
BEGIN

   FOR SELECT
      BAN_NOMBRE
   FROM
      BANCOS
   INTO
      :lcNombre
   DO BEGIN
      INSERT INTO BANCOS_NUEVOS
                 (BAN_NOMBRE)
          VALUES (:lcNombre);
      WHEN SQLCODE -803 DO BEGIN -- Violación de una UNIQUE KEY
         IN AUTONOMOUS TRANSACTION DO BEGIN
            INSERT INTO LOG
                       (LOG_COMENT)
                VALUES ('ERROR AL INSERTAR EL BANCO: ' || :lcNombre);
         END
      END
   END

END;

Tenemos dos tablas: BANCOS y BANCOS_NUEVOS, y queremos que los nombres de todos los Bancos que se encuentran en la tabla BANCOS se inserten en la tabla BANCOS_NUEVOS. Pero como la tabla BANCOS_NUEVOS ya tiene algunas filas entonces algunos nombres podrían estar repetidos, violando por lo tanto una restricción UNIQUE KEY. Pero todos los nombres que no estén repetidos deben ser insertados en la tabla BANCOS_NUEVOS.

El stored procedure MiStoredProcedure3 se encarga de esa tarea. Como la excepción -803 (que detecta las violaciones a la restricción UNIQUE KEY) no fue relanzada entonces ni el stored procedure MiStoredProcedure2 ni el stored procedure MiStoredProcedure1 ni el Servidor del Firebird se enterarán de que tal excepción ocurrió alguna vez.

¿Queremos que el stored procedure MiStoredProcedure2 se entere de que ocurrió la excepción -803? Entonces debemos escribir la palabra EXCEPTION para relanzarla.

WHEN SQLCODE -803 DO BEGIN -- Violación de una UNIQUE KEY
   IN AUTONOMOUS TRANSACTION DO BEGIN
      INSERT INTO LOG
                 (LOG_COMENT)
          VALUES ('ERROR AL INSERTAR EL BANCO: ' || :lcNombre);
   END
   EXCEPTION;
END

Aquí, se maneja la excepción -803 (la cual indica que ocurrió una violación a la restricción UNIQUE KEY) escribiendo en un archivo de LOG el error ocurrido. Luego, se relanza la excepción para que el stored procedure MiStoredProcedure2 se entere de que ocurrió esa excepción. E igualmente puede manejarla o no.

Resumiendo:

Cuando ocurre una excepción dentro de un stored procedure o de un trigger tenemos tres posibilidades:

  1. No manejarla
  2. Manejarla y no relanzarla
  3. Manejarla y relanzarla

Si no la manejamos entonces subirá un nivel. Si en ese nivel tampoco la manejamos subirá otro nivel. Y así hasta llegar al Servidor del Firebird el cual detendrá el procesamiento.

Si la manejamos y no la relanzamos entonces el nivel superior nunca se enterará de que la excepción ocurrió.

Si la manejamos y la relanzamos entonces el nivel superior sabrá que ocurrió esa excepción en el nivel inferior.

Conclusión:

Las excepciones son una herramienta buenísima de la que disponemos y que debemos utilizar para detectar y manejar cualquier error que ocurra dentro de un stored procedure o de un trigger.

Si nosotros no manejamos a las excepciones entonces el Servidor del Firebird se encargará de ello, pero eso implica que el proceso que estábamos realizando se detendrá.

Si no queremos que el proceso se detenga entonces debemos capturar la excepción y no relanzarla, de esa manera el Servidor del Firebird no se enterará de que la excepción ocurrió y no detendrá el proceso que estábamos realizando.

Artículos relacionados:

Entendiendo las excepciones

Capturando excepciones

Capturando excepciones. Códigos de error predeterminados

Capturando excepciones del usuario

El índice del blog Firebird21

El foro del blog Firebird21

 

Programación defensiva

Deja un comentario

La tarea más importante que tenemos cuando diseñamos una Base de Datos es evitar que entre basura en ella. Todo lo demás que hagamos será inservible si permitimos la entrada de basura. Se llama “basura” a cualquier dato que está pero que no debería estar.

Ejemplos de basura

  • La empresa inició sus actividades el 11 de octubre de 2009 y hay una venta con fecha 22 de marzo de 2007
  • La venta se hizo el día 14 de mayo de 2010 y la cobranza el día 20 de febrero de 2010
  • El precio de costo de un producto es mayor que su precio de venta
  • Se tiene registrada una venta, pero no la fecha de esa venta
  • Se tiene registrada la venta de un producto, pero el identificador de ese producto no existe en la tabla de PRODUCTOS
  • Se tiene registrada una venta, pero no se registró el identificador del cliente a quien se le vendió

Estos son solamente algunos ejemplos, hay miles más que podrían agregarse. El común denominador de todos ellos es que hay datos faltantes o datos inconsistentes. Un dato es inconsistente cuando es ilógico, cuando algo así no podría ocurrir. Por ejemplo, no se puede vender antes de constituir la empresa, ni se puede cobrar antes de vender, eso no tiene sentido.

Programando defensivamente

La programación defensiva tiene como ventaja que evita la introducción de basura (o al menos disminuye muchísimo la probabilidad de que ocurra) y la desventaja que nos hace trabajar más.

Pero como nuestra principal tarea es evitar la introducción de basura ese trabajo de más es el precio que deberemos pagar.

En la programación defensiva lo que hacemos es poner trabas en cuantos lugares sean posibles a los datos faltantes o inconsistentes. En la programación normal pondríamos una sola traba (aunque un mal diseñador ni siquiera haría eso), en cambio en la programación defensiva pondremos muchas trabas.

Ejemplo 1

Queremos evitar que el precio de costo sea mayor que el precio de venta.

Creamos un dominio llamado D_PRECIO:

CREATE DOMAIN D_PRECIO AS
   NUMERIC(18, 4)
   CHECK (VALUE >= 0);

Este dominio aún no nos asegura que el precio de venta sea mayor que el precio de costo, pero sí que no se introduzcan precios negativos. Algo es algo.

Ahora le agregamos una restricción CHECK a la tabla de PRODUCTOS:

ALTER TABLE PRODUCTOS
   ADD CONSTRAINT CHK_PRODUCTOS
   CHECK (PRD_PREVTA > PRD_PRECTO);

Esta restricción CHECK nos asegurará que siempre el precio de venta sea mayor que el precio de costo. ¿Ya está bien, verdad? ¿Es suficiente con esta restricción? Pues sí, pero queremos programar defensivamente para duplicar las comprobaciones y evitar la introducción de basura, entonces también escribimos un trigger before insert (o sea un trigger que se ejecutará antes de que la fila sea grabada en la tabla):

CREATE TRIGGER BIU_PRODUCTOS FOR PRODUCTOS
   ACTIVE BEFORE INSERT OR UPDATE
   POSITION 1
AS
BEGIN

   IF (NEW.PRD_PREVTA <= NEW.PRD_PRECTO) THEN
      EXCEPTION E_PRECIO_VENTA_MENOR_QUE_COSTO ;

END;

Y a la excepción E_PRECIO_VENTA_MENOR_QUE_COSTO podríamos definirla como:

CREATE EXCEPTION E_PRECIO_VENTA_MENOR_QUE_COSTO
   'El precio de venta no es mayor que el precio de costo';

Ejemplo 2

Queremos asegurarnos que el identificador del producto que vendemos exista en la tabla de PRODUCTOS

Primero, agregamos una FOREIGN KEY a nuestra tabla de movimientos de productos (o sea, la tabla en la cual registramos las ventas)

ALTER TABLE MOVIMDET
   ADD CONSTRAINT FK_MOVIMDET1
   FOREIGN KEY (MOV_CODSUC, MOV_IDEPRD)
   REFERENCES PRODUCTOS(PRD_CODSUC, PRD_IDENTI);

Ya está, ¿verdad? con esto nos aseguramos que no se pueda vender un producto cuyo identificador no se encuentre en la tabla de PRODUCTOS. Muy bien, funcionará, pero somos exquisitos y queremos aumentar la seguridad, entonces también escribimos una restricción CHECK en la tabla MOVIMDET

ALTER TABLE MOVIMDET
   ADD CONSTRAINT CHK_MOVIMDET CHECK (
      MOV_CODSUC IN (SELECT
                        SUC_CODIGO
                     FROM SUCURSALES) AND
      MOV_IDEPRD IN (SELECT
                        PRD_IDENTI
                     FROM
                        PRODUCTOS
                     WHERE
                        PRD_CODSUC = MOVIMDET.MOV_CODSUC)
);

Esta restricción CHECK nos asegurará que el código de la sucursal se encuentre en la tabla de SUCURSALES y que el identificador del producto se encuentre en la tabla de PRODUCTOS. ¿Ya está bien, ya es suficiente? Pues no, estamos programando defensivamente así que también escribiremos un trigger que se ejecutará antes de insertar o actualizar filas:

CREATE TRIGGER BIU_MOVIMDET FOR MOVIMDET
   ACTIVE BEFORE INSERT OR UPDATE
   POSITION 1
AS
   DECLARE VARIABLE lnCodigoSucursal TYPE OF D_CODIGOSUCURSAL;
   DECLARE VARIABLE lnIdentificadorProducto TYPE OF D_IDENTIFICADOR;
BEGIN

   lnCodigoSucursal = (SELECT
                          SUC_CODIGO
                       FROM
                          SUCURSALES
                       WHERE
                          SUC_CODIGO = NEW.MOV_CODSUC);

   IF (lnCodigoSucursal IS NULL) THEN
      EXCEPTION E_SIN_SUCURSAL;

   lnIdentificadorProducto = (SELECT
                                 PRD_IDENTI
                              FROM
                                 PRODUCTOS
                              WHERE
                                 PRD_CODSUC = NEW.MOV_CODSUC AND
                                 PRD_IDENTI = NEW.MOV_IDEPRD);

   IF (lnIdentificadorProducto IS NULL) THEN
      EXCEPTION E_SIN_PRODUCTO;

END;

¿Qué hicimos en este trigger? Primero, averiguamos si el código de la Sucursal que se quiere grabar en la columna MOV_CODSUC existe en la tabla de SUCURSALES. Si el resultado de la búsqueda es NULL eso significa que no existe y por lo tanto enviamos una excepción. Al enviar una excepción el código finaliza con error, todo el código fuente que se encuentre después de la excepción jamás se ejecutará porque el control regresó al nivel superior. Si la sucursal existe entonces continuamos y ahora averiguamos si existe un producto con ese identificador en esa sucursal. Si el resultado de la búsqueda es NULL, eso significa que no existe, y por lo tanto enviamos una excepción.

Recuerda que si un trigger envía una excepción las columnas no se graban en la tabla. Al enviar una excepción le estamos diciendo al Firebird: “aquí encontré un error, no grabes estas columnas en la tabla porque hay algo que está mal”.

Conclusión:

La programación defensiva es una herramienta que tenemos para conseguir que nuestro código fuente sea más confiable. Siempre debemos pensar que los usuarios son muy tontos o muy malos, tipos que pueden hacer un desastre por ignorancia o por maldad, y debemos tomar todas las precauciones habidas y por haber para evitar que causen problemas.

Entonces, en nuestro lenguaje de programación ya les debemos poner trabas a sus acciones, pero también debemos hacerlo en la Base de Datos. Firebird nos provee de cinco herramientas muy buenas para evitar que introduzcan basura en nuestras tablas: los dominios, las restricciones foreign key, las restricciones check, las restricciones unique keys y los triggers.

Debemos usarlas y quitarles el máximo provecho, que para eso están.

La programación defensiva nos hará trabajar más, pero también nos dará mayor tranquilidad, porque les tendremos mucha confianza a los datos que están guardados en las tablas.

Y si nuestras aplicaciones son muy robustas tendremos clientes muy contentos. Y clientes muy contentos siempre significa ganar más dinero.

Artículos relacionados:

Entendiendo a los dominios

Entendiendo a las Foreign Keys

Usando la restricción CHECK

Entendiendo las excepciones

Entendiendo la integridad referencial

Entendiendo a los triggers

El índice del blog Firebird21

Conociendo la aplicación que insertó filas a una tabla

2 comentarios

Cuando una Base de Datos puede ser accedida desde varias aplicaciones es muy importante saber cual aplicación insertó las filas que tiene cada tabla.

¿Por qué?

Porque solamente la aplicación que insertó la fila debería tener el derecho de modificar o de borrar esa fila. Si otra aplicación pudiera hacerlo, eso solamente acarreará problemas. Imagínate que la aplicación de VENTAS registró una venta y cuando se la quiere consultar no se la encuentra por ningún lado porque CONTABILIDAD borró esa venta. Eso es inadmisible.

¿Qué necesitaremos?

  1. Una tabla APLICACIONES, para guardar los nombres de las aplicaciones que pueden usarse
  2. Una tabla GTT, para guardar los datos de la conexión actual, incluyendo el Identificador de la aplicación usada para el acceso
  3. Un trigger de Base de Datos que le inserte una fila a la tabla GTT
  4. Desde nuestra aplicación actualizar la tabla GTT para que tenga el Identificador de aplicación correcto
  5. Agregarle una columna a todas las tablas que pueden usarse por más de una aplicación
  6. Dos excepciones, para mostrar los mensajes de error
  7. Agregarle un trigger a cada tabla que puede usarse por más de una aplicación, para decidir si la fila puede ser modificada o eliminada

Tabla APLICACIONES

APLICACIÓN1

Captura 1. Si haces clic en la imagen la verás más grande

CREATE TABLE APLICACIONES (
   APL_IDENTI D_IDENTIFICADOR NOT NULL,
   APL_NOMBRE D_NOMBRE40);

   ALTER TABLE APLICACIONES ADD CONSTRAINT PK_APLICACIONES PRIMARY KEY (APL_IDENTI);

   ALTER TABLE APLICACIONES ADD CONSTRAINT UQ_APLICACIONES UNIQUE (APL_NOMBRE);

SET TERM ^ ;

CREATE TRIGGER BI_APLICACIONES_APL_IDENTI FOR APLICACIONES
   ACTIVE BEFORE INSERT
   POSITION 0
AS
BEGIN
   IF (NEW.APL_IDENTI IS NULL OR NEW.APL_IDENTI = 0) THEN
      NEW.APL_IDENTI = GEN_ID(APLICACIONES_APL_IDENTI_GEN, 1);
END^

SET TERM ; ^

El contenido de esta tabla podría ser algo así:

APLICACIÓN2

Captura 2. Si haces clic en la imagen la verás más grande

Tabla GTT: CONEXION_ACTUAL

En una tabla GTT guardamos los datos que necesitamos conocer de la conexión que actualmente está usando el usuario.

APLICACIÓN5

Captura 3. Si haces clic en la imagen la verás más grande

Trigger de Base de Datos para insertarle una fila a la tabla GTT

CREATE TRIGGER NEW_DBTRIGGER_C
   ACTIVE ON CONNECT
      POSITION 0
AS
BEGIN

   INSERT INTO
      CONEXION_ACTUAL
      (CON_IDENTI, CON_USUARI, CON_TIMEST)
   VALUES
      (CURRENT_CONNECTION, CURRENT_USER, CURRENT_TIMESTAMP) ;

END;

El valor de la columna faltante (CON_APLICA) debería ser puesto por la aplicación correspondiente.

Contenido de la tabla CONEXION_ACTUAL

APLICACIÓN6Captura 4. Si haces clic en la imagen la verás más grande

Ejemplo:

A la tabla CLIENTES pueden insertarle filas las aplicaciones de Contabilidad, Facturación, y Ventas. Si la fila fue insertada por Contabilidad entonces Facturación y Ventas no deberían tener el derecho de modificar esa fila ni de borrarla. De la misma manera, si la fila fue insertada por Facturación o por Ventas las otras aplicaciones no deberían poder cambiarla.

Cualquier aplicación puede consultar una fila, pero solamente la aplicación que la insertó puede modificarla o borrarla.

APLICACIÓN3

Captura 5. Si haces clic en la imagen la verás más grande

Y para ver los nombres de las aplicaciones que insertaron las filas podríamos escribir algo como:

SELECT
   C.CLI_IDENTI,
   C.CLI_NOMBRE,
   C.CLI_IDEAPL,
   A.APL_NOMBRE AS CLI_NOMAPL
FROM
   CLIENTES C
JOIN
   APLICACIONES A
      ON C.CLI_IDEAPL = A.APL_IDENTI

que nos dará como resultado algo similar a:

APLICACIÓN4

Captura 6. Si haces clic en la imagen la verás más grande

Aquí como puedes ver las filas de MARCELA, DIANA y MIRTA fueron insertadas por CONTABILIDAD, en cambio la fila de SILVIA fue insertada por FACTURACIÓN. Eso implica que si debemos modificar o borrar las filas de MARCELA, DIANA, o MIRTA deberemos hacerlo desde la CONTABILIDAD y si queremos modificar o borrar la fila de SILVIA deberemos hacerlo desde FACTURACIÓN.

Esto nos asegura que nunca una aplicación modifique o borre las filas que no le corresponden.

Las excepciones para mostrar los mensajes de error

CREATE EXCEPTION E_UPDATE_NO_ADMITIDO 'No puedes modificar el contenido de esta fila';

CREATE EXCEPTION E_DELETE_NO_ADMITIDO 'No tienes permiso para borrar filas de esta tabla';

El trigger para modificar o borrar filas de la tabla CLIENTES

CREATE TRIGGER BUD_CLIENTES1 FOR CLIENTES
   ACTIVE BEFORE UPDATE OR DELETE
      POSITION 0
AS
   DECLARE VARIABLE lnAplicacion SMALLINT;
BEGIN

   lnAplicacion = (SELECT CON_APLICA FROM CONEXION_ACTUAL);

   IF (UPDATING AND NEW.CLI_IDEAPL <> lnAplicacion) THEN
      EXCEPTION E_UPDATE_NO_ADMITIDO;

   IF (DELETING AND OLD.CLI_IDEAPL <> lnAplicacion) THEN
      EXCEPTION E_DELETE_NO_ADMITIDO;

END;

Terminando:

Y listo, eso es todo, ahora solamente la aplicación que insertó una fila podrá modificar o borrar esa fila. Los intentos realizados por las demás aplicaciones serán  rechazados, como tiene que ser.

Conclusión:

Siempre es muy importante delimitar exactamente que puede hacer cada aplicación que se conecta a nuestra Base de Datos, no podemos permitir que una aplicación modifique o borre filas que fueron insertadas por otra aplicación porque eso solamente nos traerá problemas y ningún beneficio.

Artículos relacionados:

Los triggers de la Base de Datos

El índice del blog Firebird21

La problemática de las tablas maestras

11 comentarios

Cuando diseñamos una Base de Datos normalmente creamos cuatro tipos de tablas:

  • Configuración
  • Maestras
  • Movimientos
  • Auxiliares

Las tablas de configuración servirán para guardar datos que ayudarán a personalizar la aplicación relacionada.

Las tablas maestras servirán para guardar datos que luego usarán las tablas de movimientos.

Las tablas de movimientos servirán para guardar lo que ocurre en la operación normal de la empresa u organización, sus actividades diarias. A su vez se clasifican en cabecera y en detalle.

Las tablas auxiliares servirán para guardar datos temporales.

En las tablas de movimientos siempre tenemos una columna donde guardamos la fecha del movimiento, así que no es problema conocer las fechas cuando las necesitamos.

Pero eso muchas veces no ocurre con las tablas maestras, en ellas no se suelen guardar las fechas. Y puede ser muy necesario conocerlas.

Ejemplo:

Tenemos una tabla maestra de CLIENTES donde entre otros datos guardamos el Identificador del Cliente, su Nombre, su Dirección, su Teléfono. Y una tabla cabecera de VENTAS donde guardamos el Identificador de la Venta, el Identificador del Cliente, el Número de la Factura, la Fecha de la venta y otros datos.

Le hacemos una venta al cliente Juan Pérez, e imprimimos la Factura Número “001-002-3456789” que corresponde a esa venta. En ella consta que la Dirección de Juan Pérez es “Colón 12345” y que su teléfono es el “0123-456789”. Todo bien hasta ahí, ningún problema, se va Juan Pérez con su Factura.

Un tiempo después regresa Juan Pérez, le hacemos otra venta, pero nos dice que se mudó y que por lo tanto se cambió su Dirección, su Teléfono y hasta su Localidad. Así que ahora le imprimimos la Factura Número “001-002-9876543” que corresponde a esta última venta, la nueva Dirección es “Spartacus 44444” y el nuevo teléfono es el “0333-555555”. Todo bien hasta ahí, ningún problema, se va Juan Pérez con su Factura.

Pero …. luego surge el problema.

Hay una auditoría, el ente recaudador de impuestos hace un control cruzado entre las Facturas de Juan Pérez y nuestras Facturas y al consultar la Factura “001-002-3456789” le dice que la Dirección es “Spartacus 44444” y el Teléfono es “0333-555555” porque esos son los datos que tenemos guardados de Juan Pérez en nuestra Base de Datos actualmente.

Y está mal.

Está mal porque la Dirección que se imprimió en esa Factura era “Colón 12345”. Y aunque pasen los años y Juan Pérez se mude de casa muchas veces siempre que consultemos los datos de la Factura Número “001-002-3456789” deberíamos ver que la Dirección es “Colón 12345”. No la última Dirección que registramos en nuestra tabla de CLIENTES sino la Dirección original.

¿Cuál es la solución a este problema?

Tenemos tres alternativas, dos malas y una buena.

Una alternativa mala es restaurar un backup de la fecha de la primera venta a Juan Pérez para saber que Dirección teníamos guardada ese día.

Otra alternativa mala es guardar en la tabla cabecera de VENTAS la Dirección, el Teléfono, la Localidad, el Email y otros datos de Juan Pérez. Es cierto que eso nos aseguraría de imprimir siempre los datos correctos pero nuestra tabla no estaría normalizada y por lo tanto gastaríamos mucho más espacio en el disco duro que el necesario.

La alternativa buena es guardar en otra tabla, no en la tabla de CLIENTES sino en otra tabla, los cambios que se realicen a los datos de nuestros clientes. La estructura de esta tabla (la podríamos llamar CAMBIOS_CLIENTES) debería ser idéntica a la tabla CLIENTES pero tendría una columna más, esa columna adicional sería el Identificador de la tabla (el que usamos para su Primary Key).

La tabla CLIENTES (y por lo tanto la tabla CAMBIOS_CLIENTES) debe tener una columna donde se guarde el nombre del usuario que insertó la fila y otra columna donde se guarden la fecha y la hora de esa inserción.

De esta manera siempre podríamos saber cuales eran los datos de Juan Pérez en cualquier día de cualquier mes de cualquier año.

¿Cuál era la Dirección de Juan Pérez el día 12 de agosto de 2013?

  1. Buscamos en la tabla CAMBIOS_CLIENTES la fila más nueva que corresponda a Juan Pérez y que su fecha sea 12 de agosto de 2013 o anterior.
  2. Si la encontramos, esa es la Dirección de Juan Pérez el día 12 de agosto de 2013
  3. Si no la encontramos, buscamos en la tabla CLIENTES cual es la Dirección que originalmente tenía Juan Pérez
  4. Si esa fila fue insertada el día 12 de agosto de 2013 o antes, esa es la respuesta buscada
  5. Si la fila fue insertada posteriormente al 12 de agosto de 2013 entonces Juan Pérez aún no era nuestro cliente ese día y por lo tanto no podemos saber cual era su Dirección.

El problema con las tablas maestras

Lo que vimos en el ejemplo anterior, con las ventas a Juan Pérez y sus cambios de direcciones se aplica a todas las tablas maestras. Cualquier tabla maestra que tengamos adolecerá del mismo problema si no tomamos las debidas precauciones: que estaremos viendo los últimos datos, no los datos que teníamos anteriormente. Y en muchos casos los que deberíamos ver son los datos que teníamos anteriormente.

Una Base de Datos que no puede ser retrotraída a cualquier instante anterior no está bien diseñada.

Es así de simple:

  • Si yo necesito recurrir a un backup para que una consulta me muestre los datos correctos entonces mi Base de Datos está mal diseñada. Nada que discutir.
  • Si las tablas no están normalizadas entonces mi Base de Datos está mal diseñada. Nada que discutir.

Un buen diseño implica más trabajo

Todo tiene sus ventajas y sus desventajas, nada es perfecto. Podemos tener una Base de Datos perfectamente diseñada que nos permita responder a cualquier pregunta en cualquier momento, pero eso demandará más de nuestro tiempo. Y es que por cada tabla maestra deberemos tener una tabla de CAMBIOS. Y por cada tabla maestra deberemos tener un trigger AFTER UPDATE que le inserte una fila a la tabla de CAMBIOS. Y a su vez nuestras consultas (SELECTs) serán más complicadas cuando involucren a las preguntas ¿quién? ¿cuándo? o ¿desde cuándo? y ¿hasta cuándo?

La diferencia entre tener una Base de Datos muy bien diseñada y una pobremente diseñada la verás el día que quieras venderle tu aplicación a gente que entiende de Informática. Mientras tus clientes sean ignorantes en temas informáticos podrás venderles casi cualquier cosa pero el tema es muy distinto cuando hablas con gente entendida. Por ejemplo si una empresa tiene un Departamento de Informática es seguro que tendrás que entrevistarte con los encargados y allí enseguida descubrirán tu pobre diseño.

Por lo tanto, si quieres vender tus aplicaciones a empresas de cualquier tamaño y no pasar vergüenza cuando hables con el personal informático, diseña tu Base de Datos de la mejor manera posible.

Artículos relacionados:

El índice del blog Firebird21

Older Entries