Insertar, modificar o borrar filas en una Base de Datos externa

9 comentarios

En general, lo recomendable es que todas tus tablas estén en una sola Base de Datos ya que así puedes relacionar muy fácilmente a esas tablas entre sí. Sin embargo a veces no puedes evitar tener dos o más bases de datos. En ese caso: ¿cómo harías para insertar, modificar o borrar filas en una tabla que se encuentra en la otra Base de Datos?

El comando EXECUTE STATEMENT viene rápidamente en tu ayuda.

CREATE PROCEDURE INSERTAR_EN_BASEDATOS_EXTERNA_1
AS
   DECLARE VARIABLE lcComando VARCHAR(200);
   DECLARE VARIABLE lnIdenti  BIGINT;
   DECLARE VARIABLE lcNombre  VARCHAR(40);
BEGIN

   lnIdenti = 0;
   lcNombre = '''Esta es una prueba''';

   lcComando = 'INSERT INTO TARJETAS(TAR_IDENTI, TAR_NOMBRE) VALUES(' ||
               :lnIdenti || ',' || :lcNombre || ')';

   EXECUTE STATEMENT
      lcComando
   ON EXTERNAL
      'E:\BASESDATOS\CONTABILIDAD.FDB'
   AS
      USER 'SYSDBA' PASSWORD 'masterkey';
END;

Como puedes ver la variable lcNombre está rodeada por tres apóstrofos ¿por qué eso? porque si no se hace así ocurre un error ya que el contenido de esa variable debe estar rodeado de apóstrofos en el comando INSERT INTO. Cuando quieres que dentro de un string aparezca un apóstrofo debes escribir dos apóstrofos seguidos. Por ese motivo tuve que escribir tres apóstrofos.

Quizás también hayas notado que aunque la variable lnIdenti es numérica se la concatenó sin necesidad de convertirla previamente a tipo carácter, esa es una gran facilidad que nos da el Firebird.

En este ejemplo, los valores que insertamos en las columnas TAR_IDENTI y TAR_NOMBRE son constantes, sin embargo lo más común es que necesitemos insertar valores variables, como se ve en el siguiente ejemplo:

CREATE PROCEDURE INSERTAR_EN_BASEDATOS_EXTERNA_2(
   tnIdenti BIGINT,
   tcNombre VARCHAR(40))
AS
   DECLARE VARIABLE lcComando VARCHAR(200);
BEGIN

   lcComando = 'INSERT INTO TARJETAS(TAR_IDENTI, TAR_NOMBRE) VALUES(' ||
               :tnIdenti || ',''' || :tcNombre || ''')';

   EXECUTE STATEMENT
      lcComando
   ON EXTERNAL
      'E:\BASESDATOS\CONTABILIDAD.FDB'
   AS
      USER 'SYSDBA' PASSWORD 'masterkey';

END;

En este ejemplo los datos a insertar en la tabla externa fueron enviados como parámetros al stored procedure. Fíjate bien como el parámetro :tcNombre está rodeado por apóstrofos. Debes escribir esos apóstrofos para que funcione.

 Conclusión:

Para poder insertar, modificar o borrar filas en una tabla que se encuentra en otra Base de Datos debemos crear un string y luego ejecutarlo con el comando EXECUTE STATEMENT. Hay que tener en cuenta que las variables de tipo carácter deben estar rodeadas por apóstrofos.

Artículo relacionado:

https://firebird21.wordpress.com/2013/04/18/execute-statement/

Anuncios

Otorgando permisos con EMS SQL Manager

3 comentarios

Como hemos visto en este artículo:

https://firebird21.wordpress.com/2013/05/29/entendiendo-los-derechos-de-acceso/

A los usuarios, a los roles y a los objetos (vistas, triggers, database triggers, stored procedures) se les puede otorgar permisos sobre las tablas y sobre los stored procedures.

Para ello podemos utilizar el programa ISQL (que viene incluido con el Firebird) o algún programa de administración gráfica.

En la imagen de abajo vemos como usaríamos el programa ISQL para ese efecto:

GRANT1

(haciendo clic en la imagen la verás más grande)

Si usamos el programa EMS SQL Manager también podemos otorgar permisos, para ello hacemos clic con el botón derecho sobre el nombre de la tabla o del stored procedure, luego elegimos la opción “Tasks” (tareas) y luego la opción “Grants for table…” como podemos ver en la imagen de abajo:

GRANT2

(haciendo clic en la imagen la verás más grande)

 Y ahora la pantalla cambiará para mostrarnos algo similar a esto:

GRANT3

(haciendo clic en la imagen la verás más grande)

 Para otorgale permisos a un usuario hacemos clic sobre su nombre y luego sobre la operación que le queremos otorgar o sobre las opciones que vemos a la izquierda. Por ejemplo, para otorgarle al usuario “Silvia” el derecho de SELECT sobre la tabla PRODUCTOS haríamos clic allí.

GRANT4

(haciendo clic en la imagen la verás más grande)

Fácilmente podemos saber si el usuario “Silvia” tiene un permiso mirando el color del botón. Si es rojo, no lo tiene.

Para otorgarle todos los permisos podemos hacer clic sucesivamente en cada botón o elegir la opción “Grant all” que se encuentra a la izquierda:

GRANT5

(haciendo clic en la imagen la verás más grande)

Después de hacer clic sobre la opción “Grant all” esto es lo que vemos (todos los colores de los botones han cambiado y también las opciones mostradas a la izquierda han cambiado)

GRANT7

(haciendo clic en la imagen la verás más grande)

Si queremos  quitarle todos los permisos utilizaríamos la opción “Revoke all”

Importante:

Para que la acción de otorgar o de quitar permisos funcione debemos hacer clic sobre la opción “Compile”. Recién después de hacer clic sobre “Compile” el usuario tendrá o dejará de tener los permisos.

GRANT6

(haciendo clic en la imagen la verás más grande)

 Resumiendo:

  • Si el botón es de color verde, tiene el permiso
  • Si el botón es de color rojo, no tiene el permiso
  • Para otorgarle todos los permisos podemos hacer clic sobre la opción “Grant all”
  • Para quitarle todos los permisos podemos hacer clic sobre la opción “Revoke all”
  • Si elegimos “with grant option” significa que el usuario puede otorgar ese mismo permiso a otros usuarios
  • Para que el usuario tenga o deje de tener estos permisos debemos hacer clic sobre la opción “Compile”

¿En cuál carpeta tener las bases de datos?

5 comentarios

Ya instalaste el Servidor del Firebird y ahora quieres crear bases de datos (o copiar las que ya tienes) en alguna carpeta de esa computadora. Así que podrías estar preguntándote: ¿dónde? ¿en cuál carpeta?

Las carpetas donde no se recomienda que estén tus bases de datos son las siguientes:

%SYSTEMROOT%
%PROGRAMFILES%
%PROGRAMFILES(X86)%
%PROGRAMDATA%
%USERPROFILE%

Eso significa que no deben estar en C:\WINDOWS ni en C:\WINNT ni en algo similar. Tampoco en “Archivos de Programa” y tampoco dentro de algún usuario como “Walter”, “Fátima”, “Yanine”, etc.

Además la carpeta donde estarán tus bases de datos no debe estar compartida, jamás, por ningún motivo.

Ejemplos de carpetas correctas:

D:\DATABASES\

D:\MISDATOS\

E:\DB\

D:\ARCHIVOS\

E:\MISISTEMA\MISDATOS\

Recuerda que esas carpetas no deben estar compartidas o estarás comprometiendo la seguridad de tus bases de datos.

Si tienes dos discos duros entonces lo recomendable es que el Sistema Operativo se encuentre en un disco duro y tus bases de datos en otro disco duro, se consigue un mejor rendimiento de esa manera.

Y si tienes tres discos duros entonces lo ideal es que los archivos temporales se encuentren en el tercer disco duro (eso lo estableces cambiando la entrada “TempDirectories” en el archivo FIREBIRD.CONF) porque eso mejora aún más el rendimiento.

 Artículo relacionado:

El índice del blog Firebird21

 

Entendiendo los derechos de acceso

9 comentarios

En este artículo hemos visto como agregar usuarios, borrar usuarios, y cambiarles la contraseña:

https://firebird21.wordpress.com/2013/04/21/agregando-modificando-y-borrando-usuarios/

Esas personas podrán acceder a la Base de Datos pero aún no podrán realizar alguna operación en ella, ni siquiera una consulta. El Firebird por defecto les impide que ejecuten los comandos INSERT, UPDATE, DELETE, SELECT, EXECUTE PROCEDURE.

¿Por qué eso?

Porque para mantener el acceso y la utilización de la Base de Datos seguros los usuarios deben tener permisos solamente a las operaciones que específicamente se les hayan otorgado. Si no se les otorgó el permiso para realizar una operación, no podrán realizar esa operación.

Por ejemplo, a los vendedores no se les otorga el permiso para modificar los precios de venta de los productos; a los empleados no se les otorga el permiso para cambiar su propio sueldo; a los contadores no se les otorga el permiso de cambiar los datos de una venta; etc.

Si intentan realizar una operación a la cual no tienen permiso, el Firebird lo impedirá.

¿Qué es un rol?

Es el nombre otorgado a un grupo de usuarios que tienen exactamente los mismos permisos. Por ejemplo, se podría tener un rol llamado R_VENDEDORES en el cual se especificarán los permisos que poseen las personas que tienen ese rol. Todas las personas cuyo rol sea R_VENDEDORES tendrán esos permisos pero ninguno más. A los miembros de este rol se les podrían otorgar el permiso de insertar ventas (pero no modificarlas ni borrarlas), el permiso de consultar las cantidades en stock y los precios de venta de los productos (pero no insertar ni modificar ni borrar los datos de los productos). A los miembros de este rol no se les permitirá ver las compras ni las cobranzas ni los pagos ni los sueldos ni las comisiones de otros vendedores ni muchas otras cosas más.

¿Cómo se crea un rol?

Con el comando CREATE ROLE NombreRol

¿Cómo se elimina un rol?

Con el comando DROP ROLE NombreRol

¿Cuál es el comando usado para otorgarles permisos a los usuarios y a los roles?

El comando se llama GRANT (conceder, otorgar, en castellano) y su sintaxis es la siguiente:

GRANT
{<permisos> ON <objeto> | rol}
TO <concedido>
[WITH {GRANT|ADMIN} OPTION]
[{GRANTED BY | AS} [USER] otorgador]

¿Cuáles son los permisos que se pueden otorgar?

SELECT
Consultar datos
INSERT
Agregar nuevas filas
UPDATE
Modificar datos existentes
DELETE
Borrar filas
REFERENCES
Referirse a una Primary Key desde una Foreign Key. Siempre se debe 
otorgar este permiso para que puedan seleccionarse las columnas de 
la tabla referida.

ALL
Todos los anteriores

EXECUTE
Ejecuta un stored procedure o lo llama usando SELECT cuando se 
trata de un stored procedure seleccionable

ROLE
Adquiere todos los privilegios otorgados al rol. Una vez que un 
rol existe y se le han otorgado permisos, se convierte en un 
permiso que se les puede otorgar a los usuarios.

¿Qué significa WITH GRANT OPTION?

Que el usuario a quien se le otorgó ese permiso tiene el derecho de otorgarles ese mismo permiso a otros usuarios.

¿Cómo se quita un permiso?

 Con el comando REVOKE.

REVOKE <permisos>

ON <objeto>

FROM <revocado>

 Ejemplo 1. Otorgando el permiso de hacer SELECT a la tabla PRODUCTOS al usuario ALICIA

GRANT SELECT ON PRODUCTOS TO ALICIA

Ejemplo 2. Otorgando los permisos de SELECT y de INSERT y de UPDATE a la tabla PRODUCTOS al usuario SUSANA

GRANT INSERT, UPDATE, SELECT ON PRODUCTOS TO SUSANA

Ejemplo 3. Otorgando todos los permisos a la tabla PRODUCTOS al usuario GRACIELA

GRANT ALL ON PRODUCTOS TO GRACIELA

Ejemplo 4. Otorgando el permiso de cambiar el Código de Barras y el Precio de Venta de los productos al usuario INES

GRANT UPDATE (PRD_CODBAR, PRD_PREVTA) ON PRODUCTOS TO INES

Ejemplo 5. Otorgando el permiso de ejecutar el stored procedure GRABAR_PRODUCTO al usuario RAQUEL

GRANT EXECUTE ON PROCEDURE GRABAR_PRODUCTO TO RAQUEL

Ejemplo 6. Recibiendo y otorgando el permiso de hacer SELECT a la tabla PRODUCTOS

GRANT SELECT ON PRODUCTOS TO SILVIA WITH GRANT OPTION

Y ahora Silvia puede escribir:

GRANT SELECT ON PRODUCTOS TO MARCELA

GRANT SELECT ON PRODUCTOS TO LUCIANA WITH GRANT OPTION

Ejemplo 7. Otorgando permisos a un stored procedure

GRANT INSERT, UPDATE ON PRODUCTOS TO PROCEDURE GRABAR_PRODUCTO

En este ejemplo al stored procedure GRABAR_PRODUCTO se le otorgó el permiso de INSERT y de UPDATE a la tabla PRODUCTOS. Eso significa que si un usuario tiene el permiso de ejecutar el stored procedure también tendrá el permiso se insertar y de actualizar la tabla respectiva. Si no se hace así entonces habrá que asignarle a cada usuario individualmente (y a cada rol individualmente) los derechos de insertar y de actualizar la tabla, y eso llevaría mucho más tiempo.

GRANT ALL ON PRODUCTOS TO PROCEDURE RECALCULAR_SALDOS

En este ejemplo al stored procedure RECALCULAR_SALDOS se le otorgaron todos los permisos sobre la tabla PRODUCTOS. O sea que dentro de ese stored procedure se puede insertar, modificar, borrar, seleccionar datos y referenciar tablas.

Ejemplo 8. Otorgando los permisos a varios usuarios a la vez

GRANT SELECT ON PAISES TO ALICIA, GRACIELA, SILVIA, SUSANA

Estos cuatro usuarios podrán realizar consultas a la tabla PAISES

Ejemplo 9. Creando roles

CREATE ROLE R_ADMINISTRACION

CREATE ROLE R_VENDEDORES

(El nombre del rol no tiene por qué empezar con R_, esa es solamente una costumbre del autor de este blog, si quieres a tu rol lo puedes llamar: ADMINISTRACION, CONTADORES, VENDEDORES, etc., pero si el nombre empieza con R_ es más fácil saber que se trata de un rol)

Ejemplo 10. Otorgándole permisos al rol

GRANT UPDATE(PRD_PREVTA) ON PRODUCTOS TO R_ADMINISTRACION

GRANT INSERT, SELECT ON PRODUCTOS TO R_VENDEDORES

Ejemplo 11. Quitándole el permiso de hacer SELECT a la tabla PRODUCTOS al usuario ALICIA

REVOKE SELECT ON PRODUCTOS FROM ALICIA

Ejemplo 12. Quitándole todos los permisos a la tabla PRODUCTOS al usuario SUSANA

REVOKE ALL ON PRODUCTOS FROM SUSANA

Ejemplo 13. Quitándole el derecho de ejecutar el stored procedure GRABAR_PRODUCTO al usuario RAQUEL

REVOKE EXECUTE ON PROCEDURE GRABAR_PRODUCTO FROM RAQUEL

Ejemplo 14. Quitándole el derecho de otorgar permisos al usuario SILVIA

REVOKE GRANT OPTION FOR SELECT ON PRODUCTOS FROM SILVIA

Artículo relacionado:

El índice del blog Firebird21

 

Usando un script para insertar datos fijos

1 comentario

Muchas veces en nuestra Base de Datos tenemos tablas cuyas filas son fijas, por ejemplo: Estados, Provincias, Departamentos, Localidades, Bancos, Tarjetas de Crédito, etc.

Cuando trabajamos con la Base de Datos en nuestra computadora, lo normal es que agreguemos, modifiquemos y borremos un montón de filas mientras hacemos las pruebas que nos aseguren que está todo bien.

Después, cuando copiamos la Base de Datos con la cual estuvimos trabajando en la computadora del usuario: ¿cómo sabemos que los datos de todas esas tablas sean los correctos? Con tantas inserciones, modificaciones y borrados que estuvimos haciendo no podríamos asegurarlo así que una alternativa es revisar las tablas, una por una, y ponerles los valores correctos.

Eso funciona, pero es lento. En una aplicación grande tendrías cientos de tablas y si cada vez que empiezas a trabajar con ellas debes volver a verificarlas perderás muchísimo tiempo.

Hay una mejor alternativa: creando scripts que se encarguen de esa tarea.

Ejemplo. Creando un script para insertar tarjetas de crédito

En este ejemplo creamos un script que se encargará de llenar la tabla TARJETAS con las filas correspondientes. Para asegurarnos que esta tabla tiene las filas correctas solamente debemos ejecutar el script.

Listado 1.

DELETE FROM TARJETAS;

COMMIT;

SET GENERATOR TARJETAS_TAR_IDENTI_GEN TO 0;

COMMIT;

INSERT INTO TARJETAS VALUES(0, 'AMERICAN EXPRESS');
INSERT INTO TARJETAS VALUES(0, 'MASTERCARD');
INSERT INTO TARJETAS VALUES(0, 'VISA');

COMMIT;

Aquí, primero eliminamos todas las filas de la tabla TARJETAS, luego inicializamos el generador de la Primary Key a cero, a continuación le insertamos todas las filas que deseamos, finalmente escribimos el COMMIT para que los datos queden grabados permanentemente.

Por lo tanto, si ejecutamos este script en la Base de Datos que se encuentra en el Servidor del usuario la tabla TARJETAS tendrá todos sus valores correctos.

Y desde luego, así como hicimos con la tabla TARJETAS también haríamos con todas las demás tablas cuyos datos son fijos.

Artículos relacionados:

Entendiendo a los scripts

Usando scripts para documentar la Base de Datos

Ejecutando un script desde Visual FoxPro

El índice del blog Firebird21

El foro del blog Firebird21

Usando un índice descendente

3 comentarios

Cuando creamos un nuevo índice y no especificamos si lo queremos ascendente (es decir: 1, 2, 3, 4, 5, etc.) o descendente (es decir: 10, 9, 8, 7, 6, etc.) el Firebird por defecto lo crea ascendente.

¿Por qué?

Porque para la mayoría de los casos es la mejor opción, porque es la que más frecuentemente se usa.

Cuando definimos una restricción Primary Key, Foreign Key o Unique Key el Firebird automáticamente crea un índice para esas restricciones. Y ese índice es siempre ascendente.

¿Y qué sucede si se le envía como argumento a la función MAX() la columna de la Primary Key?

SELECT
   MAX(PRD_IDENTI)     /* PRD_IDENTI es la Primary Key de la tabla PRODUCTOS */
FROM
   PRODUCTOS

Si miramos el PLAN de esta consulta veremos que no usa un índice:

INDICE1

(haciendo clic en la imagen la verás más grande)

¿Por qué no se usó un PLAN?

Porque el índice de la Primary Key, el índice que el Firebird automáticamente crea para esa restricción es siempre ascendente. Si queremos que la función MAX() use un índice en esta consulta entonces tenemos que crear ese índice de forma descendente.

CREATE DESC INDEX IDX_PRODUCTOS ON PRODUCTOS(PRD_IDENTI);

Si después de crear un índice descendente para la Primary Key nos fijamos cual es el PLAN que se usará en la consulta de arriba esto será lo que veremos:

INDICE2

(haciendo clic en la imagen la verás más grande)

 O sea que ahora sí el Firebird está usando un índice.

Conclusión:

Verifica siempre cual es el PLAN de tus consultas, a veces agregándoles un índice se puede obtener una ganancia en velocidad impresionantemente grande.

Artículos relacionados:

https://firebird21.wordpress.com/2013/04/30/usando-un-plan/

https://firebird21.wordpress.com/2013/05/03/algo-mas-sobre-plan/

Usando subconsultas en expresiones

1 comentario

Antes de la versión 2.5 del Firebird no se podían usar subconsultas en expresiones aunque dichas subconsultas retornaran un solo valor. Esto nos obligaba a usar la construcción SELECT … INTO

Ahora, estas asignaciones ya son posibles dentro de un stored procedure, un trigger o un execute block:

MiVar = (SELECT … FROM …);

IF ((SELECT … FROM …) = 1) THEN …

IF(25 = ANY(SELECT … FROM …)) THEN …

IF(2104 IN (SELECT … FROM …)) THEN …

Por supuesto que en los dos primeros ejemplos el SELECT debe retornar una sola fila.

lcNomCli = (SELECT CLI_NOMBRE FROM CLIENTES WHERE CLI_IDENTI = 11111);     /* Nombre del cliente */

lnPreVta = (SELECT PRD_PREVTA FROM PRODUCTOS WHERE PRD_IDENTI = 12345);     /* Precio de venta del producto */

Fíjate que debes rodear al SELECT con paréntesis o recibirás un mensaje de error.

Older Entries