Libro Base de Datos - Parte B (PDF)

BASES DE DATOS - B Desarrollo de aplicaciones multiplataforma Ilerna ILERNA, centro autorizado con código 25002775...

BASES DE DATOS - B Desarrollo de aplicaciones multiplataforma Ilerna ILERNA, centro autorizado con código 25002775 (Lleida), 28077294 (Madrid) y 41023090 (Sevilla) www.ilerna.es © Ilerna Online S.L., 2021 Maquetado e impreso por Ilerna Online S.L. © Imágenes: Shutterstock Impreso en España - Printed in Spain Reservado todos los derechos. No se permite la reproducción total o parcial de esta obra, ni su incorporación a un sistema informático, ni su transmisión en cualquier forma o por cualquier medio (electrónico, mecánico, fotoco- pia, grabación u otros) sin autorización previa y por escrito de los titulares del copyright. La infracción de dichos derechos puede constituir un delito contra la propiedad intelectual. Ilerna Online S.L. ha puesto todos los recursos necesarios para reconocer los derechos de terceros en esta obra y se excusa con antelación por posibles errores u omisiones y queda a disposición de corregirlos en posteriores ediciones. 1.a edición: febrero 2021  ÍNDICE Bases de datos - B 8. Tutorial de instalación SGBD Oracle................................................. 6 8.1. Instalación software JDK................................................................7 8.2. Instalación del servidor Oracle Database Express..................... 12 8.3. Instalación del cliente Oracle: SQL Developer........................... 16 9. Creación de bases de datos............................................................. 20 9.1. Creación de bases de datos en Oracle........................................ 21 9.2. Creación de BBDD en MySQL...................................................... 26 10. Gestión de usuarios......................................................................... 32 10.1. Administración de usuarios......................................................... 33 10.1.1. Administración de usuarios en Oracle......................... 33 10.1.2. Administración de usuarios en MySQL........................ 40 10.2. Administración de privilegios..................................................... 46 10.2.1. Administración de privilegios en Oracle...................... 46 10.2.2. Administración de privilegios en MySQL..................... 50 10.3. Administración de roles............................................................... 54 10.4. Administración de perfiles........................................................... 57 10.5. Normativa legal vigente sobre la protección de datos.............. 58 11. Programación en bases de datos (BBDD)....................................... 60 11.1. Entornos de desarrollo al entorno de las BBDD......................... 61 11.2. Sintaxis del lenguaje de programación....................................... 63 11.2.1. Bloques en PL/SQL........................................................ 63 11.2.2. Variables........................................................................ 64 11.2.3. Operadores.................................................................... 67 11.3. Procedimientos y funciones........................................................ 73 11.4. Control de errores........................................................................ 79 11.5. Cursores y transacciones............................................................. 83 11.6. Disparadores o triggers............................................................... 93 12. Uso de las BBDD objeto-relacionales............................................. 98 12.1. Características............................................................................ 100 12.2. Tipos de datos objeto................................................................ 104 12.3. Definición de tipos de objeto.................................................... 104  12.4. Herencia...................................................................................... 109 12.5. Identificadores, referencias....................................................... 111 12.6. Tipos de datos colección........................................................... 112 12.7. Declaración e inicialización de objetos.................................... 115 12.8. Sentencia SELECT. Inserción, modificación y borrado de objetos........................................................................................ 116 Bibliografía / webgrafía.....................................................................120 Solucionario.......................................................................................121 8 TUTORIAL DE INSTALACIÓN SGBD ORACLE  Bases de datos - B Para la instalación del SGBD de Oracle necesitamos instalar primero el software de Java correspondiente. Por tanto, en este tema analizaremos primero la instalación de la herra- mienta JDK, y después del cliente y el servidor Oracle. 8.1. Instalación software JDK El SGBD de Oracle está desarrollado en Java y se ejecuta sobre la plataforma de Java, por tanto, para usar la BD de Oracle debemos tener instalada en nuestra computadora una serie de requisitos Java. Oracle nos suministra vía web la herramienta JDK (Java Development Kit), que es una herramienta que todo pro- grama implementado en Java necesita tener. Para instalar JDK en nuestra computadora debemos seguir estos pasos: 1. Accedemos a la página web oficial de Oracle. BUSCA EN LA WEB www.oracle.com/es/index.html 2. Navegamos por la web hasta acceder a la sección de descargas JDK. BUSCA EN LA WEB www.oracle.com/technetwork/es/java/javase/ downloads/index.html 7  Tema 8: Tutorial de instalación SGBD Oracle 3. Seleccionamos la descarga para el sistema operativo y arquitectura que nos corresponda en nuestra computadora (Windows, Linux, MacOS…). 8  Bases de datos - B 4. Aceptamos las condiciones de uso antes de la descarga. 5. Oracle nos solicitará una cuenta. En caso de no tenerla, podemos crear una fácil y gratis. 9  Tema 8: Tutorial de instalación SGBD Oracle 6. Una vez realizado el paso anterior, descargaremos en nuestra computadora el software JDK de Oracle. 7. Una vez descargado, comenzaremos con el proceso de instalación. 8. La instalación nos pregunta por la ruta de la carpeta de instalación. Tiene una asignada por defecto, pero podemos cambiarla haciendo clic en Change. 10  Bases de datos - B 9. La instalación avanzará de manera automática. 10. Hasta que el programa de instalación nos avise de que ya está instalado. Adicionalmente, nos ofrece la opción de acceder a tutoriales, guías y otros contenidos. Esto no es estrictamente necesario, pero podríamos optar por ello haciendo clic en Next Steps: 11  Tema 8: Tutorial de instalación SGBD Oracle 8.2. Instalación del servidor Oracle Database Express Una vez ya tenemos instalado el JDK, pasaremos a descar- gar e instalar el servidor de la BD. Oracle posee diversos SGBD, pero en nuestro caso usaremos Oracle Database Express que, hasta la fecha, es el único servidor de BBDD que Oracle suministra de manera gratuita. 1. Accedemos a la sección de descarga de Oracle Database Express dentro de la web de Oracle y seleccionamos la op- ción Descargar. BUSCA EN LA WEB www.oracle.com/es/database/technologies/ appdev/xe.html 2. Elegimos la descarga correspondiente según el sistema operativo de nuestra computadora. Para ello, la web nos solicitará aceptar las condiciones de uso y tener una cuenta de Oracle, al igual que en el proceso de descarga e instala- ción del JDK. 12  Bases de datos - B 3. Tras el paso anterior, un archivo con el software de- seado se descargará en modo comprimido en nuestra computadora. 4. Una vez descargado el archivo, lo descomprimimos. 5. Una vez descomprimido, pasamos a la configuración e instalación del servidor. Para ello, ejecutamos el archivo de configuración llamado Setup. 6. Nos aparecerá un asistente de instalación que nos guiará durante dicho proceso, en el que nos pedirá la con- firmación de acuerdo de licencia y la carpeta a usar para la instalación. 13  Tema 8: Tutorial de instalación SGBD Oracle 7. Llegado el momento, el sistema nos pedirá una con- traseña para el servidor. Es muy importante recordar esta contraseña, porque en caso de olvidarla, probablemente tendríamos que volver a instalar el servidor. Una vez sumi- nistrada la contraseña, seleccionamos la opción Siguiente y confirmamos la instalación. 14  Bases de datos - B 8. A continuación, la instalación se efectuará. Puede tardar unos minutos. 9. Cuando el asistente finalice la instalación, solo quedará seleccionar la opción Terminar. Tras este proceso, ya tenemos instalado el servidor de la BD. Ya solo nos falta instalar la parte del cliente. 15  Tema 8: Tutorial de instalación SGBD Oracle 8.3. Instalación del cliente Oracle: SQL Developer Una vez realizada la instalación de la parte del servidor, procederemos a instalar la parte del cliente. Para ello usa- remos el software Oracle SQL Developer. El software cliente se conectará al software servidor y le lanzará las consultas SQL. Por tanto, debe existir una conexión entre Database Express y SQL Developer. Los pasos que debemos seguir para la instalación del SQL Developer de Oracle son: descargar el software, desem- paquetar y descomprimir el software y ejecutarlo. Los detallamos a continuación. 1. Accedemos a la página web de Oracle y accedemos a la sección del SQL Developer. BUSCA EN LA WEB www.oracle.com/es/tools/downloads/sqldev-v192- downloads.html 16  Bases de datos - B 2. Seleccionamos la descarga adecuada en función de nuestro sistema operativo. Aceptamos los términos y con- diciones para la descarga del software. 3. Al igual que con otras instalaciones de Oracle, la web nos pedirá estar registrados en una cuenta de Oracle. Podemos usar la misma cuenta que utilizamos anteriormente. 4. Una vez validada nuestra cuenta, se descargará automá- ticamente el archivo de SQL Developer. 17  Tema 8: Tutorial de instalación SGBD Oracle 5. El archivo se descargará en un formato comprimido. De- bemos extraerlo para su utilización normal. 6. Una vez lo hayamos extraído, haremos doble clic sobre el ejecutable. El sistema probablemente nos pedirá la ruta donde está ubicado el JDK que hemos instalado anterior- mente. Entonces, se abrirá el software SQL Developer. 18  Bases de datos - B 7. Una vez comprobado que el cliente SQL Developer fun- ciona correctamente, es recomendable crear un acceso directo de esta aplicación en el escritorio. ponte a prueba Indica el orden correcto que debemos seguir para instalar SQL Developer de Oracle. a) Descargar, descomprimir, desempaquetar y ejecutar b) Descargar, desempaquetar, ejecutar y descomprimir c) Descargar, desempaquetar, descomprimir y ejecutar d) Desempaquetar, descargar, descomprimir y ejecutar 19 9 CREACIÓN DE BASES DE DATOS  Bases de datos - B En este tema vamos a aprender a crear una BD, tanto en el SGBD de Oracle como en el SGBD de MySQL. Emplearemos el mismo ejemplo para ambos sistemas. En el caso del SGBD de Oracle, primero deberemos enlazar manualmente la parte del cliente con la del servidor. En el caso de MySQL y Workbench, por defecto deberían estar conectados. 9.1. Creación de bases de datos en Oracle Antes de empezar con la creación de la BD vamos a co- nectar el software cliente con el servidor. Lo primero que haremos es comprobar que el servidor se está ejecutando. De no ser así, basta con hacer doble clic sobre su icono en el escritorio. A continuación, ejecutaremos el cliente y, accedien- do a Conexiones, crearemos una nueva conexión (+). Nota: En el caso de no tener un icono en el escritorio, po- demos abrir tanto el software cliente como el servidor a través de la consola del sistema operativo. Para ello, se debe conocer la ruta donde está instalado. Para comprobar en el sistema operativo si el software ser- vidor se está ejecutando, podemos listar todos los servicios que están en ejecución. En el caso de Windows, podemos consultarlo en la opción Servicios, tal como se muestra en esta imagen, en la cual podemos observar cómo los pro- cesos ServiceXE y el TNSListener están en ejecución, por lo que nos aseguramos de que el servidor está activo. Para simplificar la prueba, vamos a nombrar la conexión exactamente igual que al usuario que la va a utilizar. Por tanto, el nombre de la conexión va a ser SYSTEM. Usuario: SYSTEM Contraseña: root 21  Tema 9: Creación de bases de datos Si quisiéramos un usuario diferente, tendríamos que crear- lo primero en el servidor para que, después, pudiéramos utilizarlo en una conexión. Podemos seleccionar la opción Guardar contraseña para que quede memorizada y no nos la vuelva a pedir. Se- guidamente, probaremos la conexión comprobando que el estado es correcto. Una vez hecha esta comprobación, podremos guardar y conectar. En la imagen observamos numerados el orden de los pasos que se deben seguir para la nueva conexión. 22  Bases de datos - B Si hemos seguido los pasos correctamente, tendremos una conexión a la BD perfectamente funcional y con todos los privilegios. 23  Tema 9: Creación de bases de datos A continuación, veremos los diferentes pasos para crear las tablas dentro de una conexión que actuará como BD. 1. Creamos las tablas mediante comandos SQL. 2. A continuación, seleccionamos los comandos y pulsa- mos sobre el acceso directo Sentencia de ejecución. 24  Bases de datos - B 3. Para comprobar que las órdenes han sido efectuadas con éxito, refrescamos la conexión y, en el apartado de Tablas deben aparecer las que hemos creado. ponte a prueba Las conexiones a BBDD no utilizan nunca de contraseña, ya que esta podría deshabi- litar el usuario principal del sistema. a) Verdadero b) Falso 25  Tema 9: Creación de bases de datos 9.2. Creación de BBDD en MySQL Para crear una BBDD en MySQL daremos los siguientes pasos: 1. Iniciar correctamente MySQL Workbench. 2. Conectarnos al servidor. 3. Codificar un ejemplo de una BD nueva. 4. Guardar la BD en el sistema. 5. Asegurarse de que la BD queda almacenada correcta- mente. 1. Lo primero que debemos hacer es abrir MySQL Work- bench. Para ello, debemos buscar la aplicación en nuestro sistema operativo y ejecutarla. Por ejemplo, en Windows 10 podemos escribir directamente workbench en la barra de tareas y hacer clic en el icono de MySQL Workbench. De tal modo que se debería abrir MySQL Workbench en nuestro sistema. 26  Bases de datos - B 2. Como podemos observar en la imagen anterior, Wor- kbench nos ofrece la opción de conectarnos al servidor MySQL. Al hacer doble clic en la referencia al servidor, el sistema nos pedirá la contraseña para conectarnos al mismo. El Workbench nos abrirá una nueva pestaña. Si deseamos conocer más detalles sobre la conexión al servidor, po- demos hacer clic en la opción Server Status, tal como se señala en la imagen. 27  Tema 9: Creación de bases de datos Al hacer clic en Server Status podremos conocer los detalles de la conexión al servidor. 3. Para crear código MySQL en Workbench utilizaremos el editor de textos que nos ofrece Workbench. En el editor codificaremos la BD deseada. En este caso, usaremos el mismo ejemplo de una BD nueva que hicimos con Oracle: 28  Bases de datos - B 4. Cuando el código de la nueva BD ya esté creado, debemos ejecutarlo para que se almacene en el SGBD. Al ejecutarlo, Workbench nos informará del éxito de la operación o de los posibles errores, en caso de que los hubiese. Para ello debemos presionar el símbolo del rayo. Al ejecutarse el código, Workbench nos informará del éxito o error del código en la sección inferior Action Output. En este caso el código se ejecutó correctamente. 5. Al ejecutarlo de manera exitosa, la nueva BD quedará registrada en el servidor. Para verla en la sección de SCHE- MAS, debemos hacer clic en el símbolo de Actualizar. 29  Tema 9: Creación de bases de datos Independientemente de la BD ya creada y guardada en el servidor, es conveniente guardar el código que hemos creado en Workbench. Para ello usaremos el icono Guardar. De este modo, habremos finalizado la tarea de Crear una BD nueva en el servidor MySQL a través de la interfaz grá- fica de MySQL Workbench. ponte a prueba En MySQL Workbench es posible conocer los detalles de conexión al servidor, ¿cuál de las siguientes opciones utilizaremos? a) Server Status b) Check connection c) Data show d) Status variables 30 Bases de datos - B 31 10 GESTIÓN DE USUARIOS  Bases de datos - B 10.1. Administración de usuarios Para interactuar con una BD se utilizan los usuarios. Existen usuarios de todo tipo: desde usuarios de sistema hasta usuarios con muchas restricciones. Las BBDD albergan una lista válida de usuarios a los que se les permite la conexión al sistema. Por este, motivo se debe crear una cuenta de usuario para cada persona que preten- da acceder a la BD, en la que se especifique: el nombre de usuario, el método de autentificación, el perfil de usuario y, en el caso de Oracle, el tablespace. 10.1.1. Administración de usuarios en Oracle En Oracle vamos a diferenciar las dos formas por las que se puede autentificar un usuario: Autentificación por BD: la administración de la cuenta es de tipo usuario/contraseña, de forma que se va a guar- dar encriptada y su autentificación se realizará por Oracle. Autentificación externa: la cuenta la mantiene Oracle, aunque la administración de la contraseña y la auten- tificación de usuario es realizada externamente por el sistema operativo. El usuario tiene la posibilidad de co- nectarse a la BD sin necesidad de indicar el nombre del usuario o la clave, porque es el mismo nombre de usuario del sistema operativo. Cuentas administrativas: tal como hemos podido com- probar al instalar Oracle, existen por defecto dos cuentas con permisos administrativos concedidos para realizar tareas de optimización y monitorización de las BBDD. – SYS: funciona como súper administrador de la BD (rol de DBA) y no interesa modificar su esquema porque es donde se crea el diccionario de datos. – SYSTEM: contiene el mismo rol que la anterior y, por defecto, tiene una serie de tablas y vistas administrati- vas ya creadas. – SYSMAN: realiza tareas administrativas utilizando En- terprise Manager. – DBSMNP: controla la aplicación Enterprise Manager. Privilegios administrativos: la seguridad en las BBDD es un factor muy importante, ya que en caso de violacio- nes de seguridad los datos pueden quedar expuestos. Por este motivo, hay que estar muy atentos a los privilegios y accesos que se otorgan a los DBA (administradores de 33  Tema 10: Gestión de usuarios base de datos). Existen ciberataques dirigidos especial- mente a los usuarios con más privilegios en una empresa, aprovechando sus cuentas para hacer un mal uso. Para evitar este tipo de incidentes, se recomienda realizar una separación de responsabilidades. Esta separación con- siste en dividir las tareas administrativas entre diferentes usuarios y evitar que recaigan en un solo individuo todo- poderoso. Esto reduce la probabilidad de que un usuario abuse de los privilegios y, por otro lado, minimiza el riesgo de recibir un ataque en este tipo de cuentas. Existen dos grandes privilegios administrativos del sistema capacitados para realizar operaciones de alto nivel, como crear, cerrar, iniciar, realizar copias de seguridad o recupe- rar la BD: SYSDBA: tiene un control total sobre la BD. SYSOPER: permite al usuario realizar las tareas operati- vas básicas, pero sin poder visualizar los datos. Una buena práctica para realizar la separación de respon- sabilidades es evitar utilizar el privilegio SYSDBA y, en su lugar, conceder privilegios más específicos como SYSBAC- KUP, SYSDG, SYSRAC y SYSKM para realizar las tareas de copias de seguridad de la BD, gestión de la protección de los datos, gestión de RAC y gestión de las credenciales de usuarios. La vista v$PWFILE_USERS nos permite examinar a los usuarios administrativos. Características de los usuarios de Oracle Como hemos explicado anteriormente, los usuarios deben poseer un nombre. Veamos las restricciones que existen a la hora de crearlos: Nombre usuario: debe ser único e irrepetible. Su longitud máxima no ha de sobrepasar los 30 caracteres. Además, solamente puede contener caracteres alfanuméricos y los signos $ y _como caracteres especiales. Configuración física: espacio que posee el usuario para almacenar su información y límite de almacenamiento. En Oracle se denomina tablespace. Perfil asociado: son los diferentes recursos de los que dispone el usuario del sistema. Privilegios y roles: concesión de funciones que pueden realizar los usuarios. 34  Bases de datos - B STATUS (estados) de una cuenta de usuario OPEN (abierta): el estado de la cuenta es accesible, por lo que el usuario puede trabajar sin problema en las ac- ciones habilitadas. EXPIRED (expirada): la cuenta de usuario ha agotado el tiempo máximo del que disponía, por lo que su acceso queda denegado. EXPIRED GRACE (expirada en gracia): la cuenta ha ca- ducado, pero sigue siendo accesible. En este estado, se conceden X días para cambiar la contraseña antes de de- negar el acceso. LOCKED (bloqueada): el administrador del sistema ha bloqueado la cuenta, por lo que el usuario no podrá rea- lizar ninguna acción. LOCKED TIMED (bloqueada por tiempo): tras varios intentos fallidos de inicio de sesión, la cuenta queda blo- queada temporalmente (FAILED_LOGIN_ATTEMPTS). EXPIRED & LOCKED (expirada y bloqueada): la cuenta ha caducado y ha sido bloqueada por el administrador del sistema. Creación de usuario Antes de comenzar con la creación de usuarios, debemos asegurarnos de que nos conectamos a un usuario que posee permisos concedidos para la creación de usuarios. Existen dos formas diferentes para conectarnos a un usuario: 1. Mediante la creación de una conexión, como hemos visto en el apartado anterior con el usuario SYSTEM 2. Mediante comandos en Oracle (Run SQL Command Line) 35  Tema 10: Gestión de usuarios La sentencia para crear una cuenta de usuario que permita la autentificación de este en el SGBD con un nivel determi- nado de privilegios es: Sintaxis CREATE USER nombre_usuario IDENTIFIED BY 'passwords' [opciones]; Ejemplo CREATE USER ilerna IDENTIFIED BY 'root'; Para saber a qué usuario estamos conectados utilizaremos el siguiente comando: Ejemplo SHOW USER Si estamos trabajando con las hojas de trabajo del cliente de Oracle SQL Developer, hay que tener en cuenta que, al crear un usuario y conectarnos a él, una vez terminemos el script, este se desconectará automáticamente. En el caso de estar utilizando la consola de comandos, este usuario se quedará conectado hasta que cerremos la conexión o la consola. Sentencia de creación y conexión: CREATE USER ilerna IDENTIFIED BY 'root'; GRANT CREATE SESSION TO ilerna; CONN ilerna / root Cuando se crea un usuario, también podemos elegir las siguientes opciones: Sintaxis CREATE USER nombre {IDENTIFIED BY 'contraseña'| EXTERNALLY | GLOBALLY AS nombreGlobal} [DEFAULT TABLE SPACE tableSpacePorDefecto] [TEMPORARY TABLESPACE tableSpacetTemporal] [QUOTA{cantidad[K|M]|UNLIMITED}ON tablespace [QUOTA{cantidad[K|M]|UNLIMITED}ON tablespace[…]]] [PASSWORD EXPIRE] [ACCOUNT{UNLOCK|LOCK}]; [PROFILE{perfil|DEFAULT}] 36  Bases de datos - B Ejemplo CREATE USER ilerna IDENTIFIED BY 'root' DEFAULT TABLESPACE 'Alumnos' QUOTA 15M ON 'Alumnos' Modificación de usuario Sintaxis ALTER USER nombre_usuario [opciones]; Ejemplo ALTER USER ilerna ACCOUNT UNLOCK; Para quitarle el límite de cuota: Ejemplo ALTER USER ilerna QUOTA UNLIMITED ON Alumnos; 37  Tema 10: Gestión de usuarios Borrado de usuario Sintaxis DROP USER nombre_usuario [CASCADE]; La opción CASCADE elimina primero los objetos que están asociados al usuario y, después, el usuario. Por tanto, emplearemos la opción de eliminar el usuario con CASCADE si tenemos intención de borrar todos sus objetos asociados: Ejemplo DROP USER ilerna CASCADE; Cada vez que borremos un usuario, borraremos también el esquema asociado a sus objetos. Consulta de usuario Mediante DBA_USERS se muestra la lista y configuración de los usuarios del sistema. Para visualizar la estructura de un usuario podemos utilizar el comando DESC DBA_ USERS o DESCRIBE DBA_USERS. DESC es un diminutivo de DESCRIBE. Ejemplo DESC DBA_USERS; Para conocer todos los usuarios del sistema podemos usar la siguiente expresión: Ejemplo SELECT * FROM DBA_USERS; Espacios en tablas (tablespaces) Los espacios de tablas son almacenes para estructuras de una BD (tablas, vistas, procedimientos, etc.). Una BD cons- ta de uno o más espacios de tablas. Por defecto, Oracle crea los siguientes tablespaces: 1. SYSTEM (diccionario de datos) 2. SYSAUX (componentes opcionales de la BD) 3. TEMP 4. UNDOTBS1 (undotablespace, para rollbacks de transac- ciones) 5. USERS 38  Bases de datos - B Operaciones con espacios de tablas Vemos la sintaxis de creación de un tablespace que contie- ne todas las opciones que le podemos indicar. Para crear un tablespace asociado a un usuario lo primero que haremos es crear el espacio de tablas relacionado con un fichero físico de nuestro equipo: Sintaxis CREATE [TEMPORARY/UNDO] TABLESPACE DATAFILE/TEMPFILE' 'SIZE [, '' SIZE [, ''SIZE[,...]]] BLOCKSIZE AUTOEXTEND {[OFF/ON(NEXT MAXSIZE )/UNLIMITED]} LOGGING/NOLOGGING(Logging default) ONLILE/OFFLINE(Online default) EXTENT MANAGEMENT {[DICTIONARY] [LOCAL default (AUTOALLOCATE/UNIFORM )]} PERMANENT/TEMPORARY(Permanent default) MINIMUM EXTENT DEFAULT STORAGE {[INITIAL] [NEXT ] [PCTINCREASE ] [MINEXTENTS ] [MAXEXTENTS /UNLIMITED] [FREELISTS ] [FREELISTSGROUPS ] [OPTIMAL /NULL] [BUFFER_POOL ]} CHUNK NOCACHE; Y, a continuación, creamos el usuario relacionado con la tabla de espacios anterior: Ejemplo CREATE USER ilerna IDENTIFIED BY 'root' DEFAULT TABLESPACE ejercicios; 39  Tema 10: Gestión de usuarios 10.1.2. Administración de usuarios en MySQL Cuentas reservadas MySQL posee algunas cuentas predefinidas o reservadas que vienen por defecto al instalar el SGBD de MySQL. Son las siguientes: ROOT: cuenta que tiene todos los privilegios en el SGBD. Puede realizar cualquier operación. Por motivos de se- guridad, es posible cambiar el nombre de ROOT por otro diferente, dada la gran cantidad de privilegios que posee esta cuenta y lo llamativo del nombre ROOT que puede resultar para un usuario malintencionado. MYSQL.SYS: se usa para definir objetos del esquema sys (vistas, procedimientos, funciones, etc.). Esta cuenta soluciona el problema en el caso de que la cuenta ROOT fuese eliminada. Esta cuenta está protegida, de modo que no acepta conexiones desde clientes. MYSQL.SESSION: cuenta usada internamente por plu- gins (aplicaciones de extensión, complementos) para acceder al servidor. Esta cuenta está protegida, de modo que no acepta conexiones desde clientes. Privilegios administrativos En MySQL existen tres privilegios administrativos que son los siguientes: Privilegios de administrador: permite al usuario reali- zar acciones en el servidor MySQL de manera global, es decir, en cualquier BD contenida en el servidor. 40  Bases de datos - B Privilegios de una BD: son aquellos que atañen a una BD y todos los objetos que hubiese dentro de dicha BD, tales como tablas, índices, vistas, etc. Privilegios de objetos: son los privilegios que se aplican a objetos concretos de una BD, tales como tablas, rutinas, vistas, etc. Creación de usuarios Existen dos formas diferentes de un usuario: mediante la interfaz gráfica de Workbench y mediante comandos. 1. Mediante la interfaz gráfica. Debemos hacer clic en Users and Privileges, de modo que aparecerá la pestaña de usuarios y privilegios con la lista de los usuarios existentes. Para crear uno nuevo clicaremos en AddAccount, tal como se señala en la imagen anterior. 41  Tema 10: Gestión de usuarios Al hacer clic en Add account se nos abrirán las pestañas de especificación del nuevo usuario. En ellas podemos asignar el nombre de usuario, tipo de autentificación, contraseña, privilegios, restricciones de acceso, etcétera. 2. Mediante comandos Para crear un usuario nuevo en MySQL por medio de comandos podemos utilizar CREATE USER. Sintaxis CREATE USER nombre_usuario IDENTIFIED BY 'password'; Ejemplo CREATE USER 'mrobinson'@'localhost' IDENTIFIED BY 'mypassword123'; En nuestro ejemplo, creamos un usuario sin especificar ninguna característica, de modo que MySQL asigna dichas características por defecto. Sin embargo, la sintaxis com- pleta de CREATE USER puede ser mucho más extensa. La sintaxis completa de CREATE USER es la siguiente: Sintaxis CREATE USER [IF NOT EXISTS] user [auth_option][, user [auth_option]]… [REQUIRE {NONE|tls_option[[AND]tls_option]…}] [WITH resource_option [ resource_option ] … ] [password_option | Lock_option] … Para ver todas las opciones se puede consultar el manual MySQL (en inglés) en su página web oficial: BUSCA EN LA WEB dev.mysql.com/doc/refman/8.0/en/create-user.html Modificación de usuario Para modificar una cuenta de usuario en MySQL usamos el comando ALTER USER. Este comando pueden ejecutarlo solo los usuarios con privilegios de administrador en su cuenta o también los que tengan el privilegio del comando UPDATE. 42  Bases de datos - B Para listar los usuarios existentes en el sistema y ver qué privilegios tiene cada uno podemos utilizar la siguiente sentencia: SELECT * FROM mysql.user; Al realizar dicha sentencia, MySQL Workbench nos infor- mará de las cuentas de usuario existentes y sus privilegios, como se observa en la imagen siguiente. Cuando usamos el comando ALTER USER modificamos las propiedades de una cuenta de usuario, de modo que aquellas propiedades que no se especifiquen en la sentencia mantendrán los valores que tenían asignados anteriormente. Sintaxis ALTERUSER [ IF EXISTS ] User [auth_option] [, user [auth_option]] … [REQUIRE {NONE | tls_option [[AND] tls_option… }] [WITH resource_option [resource_option] …] [password_option | Lock_option ] … Con la siguiente sentencia actualizaremos la contraseña de una cuenta de usuario concreto. Ejemplo ALTER USER 'mrobinson'@'localhost' IDENTIFIED BY 'newpassword456'; Si además queremos fijar el requisito de que el usuario tenga que establecer una contraseña nueva cada 200 días, además de otro tipo de autentificación, lo expresaremos a través de la siguiente sentencia. Ejemplo ALTER USER 'mrobinson'@'localhost' IDENTIFIED WITH sha256_password BY 'new_password' PASSWORD EXPIRE INTERVAL 200 DAY; 43  Tema 10: Gestión de usuarios Borrado de usuario En MySQL la sentencia DROP USER se utiliza para elimi- nar un usuario junto con sus privilegios. Su sintaxis es la siguiente. Sintaxis DROP USER [IF EXISTS] 'nombre_usuario'@ 'dominio_usuario'; Como se puede observar en el código, el nombre del usua- rio y su dominio debe estar entrecomillado y con una @ entre ambos. Para que un usuario borre otro usuario, el primero debe tener el privilegio de crear usuarios (CREATE USER) o el de eliminarlos (DELETE USER). En versiones de MySQL anteriores a la 5.0.2 el comando DROP USER solo permite borrar usuarios que no tengan privilegios asociados, pero en las versiones de MySQL 5.0.2 y posteriores sí es posible. Si realizamos un DROP USER sobre una cuenta de usuario que en ese instante está abierta, la sentencia no se hará efectiva hasta que la sesión del usuario a borrar se haya cerrado. Una vez se haya cerrado dicha sesión, la cuenta se eliminará y, por tanto, no se podrá abrir más veces la sesión con esa cuenta de usuario. A continuación, vemos un ejemplo de borrado de un usuario. Sintaxis DROP USER 'mrobinson' @ 'localhost'; 44  Bases de datos - B ponte a prueba ¿Cuáles de las siguientes características siguientes debería de cumplir un usuario de BBDD? a) El nombre de usuario no debe sobrepasar los 30 caracteres. b) El usuario debe tener asignados privilegios y permisos. c) No debe contener una contraseña. d) A y B son correctas. Las cuentas de BBDD no tienen diferentes estados, se crean y se eliminan, pero siempre con el mismo estado. a) Verdadero. b) Falso. Una buena práctica es evitar hacer uso del privilegio SYSDBA, para ello existen diferentes tipos de privilegios dependiendo de la tarea que deseemos realizar. Si se pretende gestionar las credenciales de los usuarios, ¿qué privilegio se utilizará? a) SYSBACKUP. b) SYSKM. c) SYSDG. d) SYSRAC. Mediante el comando DBA_USERS se muestra la lista y configuración de los usuarios del sistema. Entre los siguientes comandos, ¿cuál es el correcto para visualizar la estructura? a) DESC DBA_USERS. b) SHOW USERS. c) SHOW DBA_USERS. d) DESCRIPT DBA_USER. Existen varias formas de crear usuarios en MySQL. Indica cuál de ellas es la correcta. a) Mediante interfaz gráfica. b) Modificando usuarios. c) Mediante comandos. d) Las respuestas a y c son correctas. e) Las respuestas a, b y c son correctas. 45  Tema 10: Gestión de usuarios 10.2. Administración de privilegios Los privilegios son aquellos permisos y derechos que po- seen los usuarios de una BD que les permiten manipular los objetos que haya en la BD. Es decir, los privilegios represen- tan las opciones que se les han asignado a los usuarios de una determinada BD para realizar acciones cómo: Ejecutar un tipo de sentencia SQL. Acceder a un objeto de otro usuario. Ejecutar distintos procedimientos. Los privilegios permiten jerarquizar el uso de una BD, de manera que nos aseguramos de que solo los usuarios ade- cuados puedan modificar ciertos elementos delicados de la BD. De esta manera ganamos seguridad en la BD. Los privilegios se pueden asignar a los usuarios de manera explícita o también se pueden asignar por medio de roles, que veremos más adelante. 10.2.1. Administración de privilegios en Oracle En Oracle podemos diferenciar entre dos tipos de privile- gios: Privilegios de sistema: ofrecen la posibilidad de reali- zar determinadas acciones de administración en la BD, en cualquier esquema. Afecta a todos los usuarios. Por ejemplo, CREATE USER o CREATE TABLE. Privilegios de objetos: ofrecen a un usuario la posibili- dad de acceder, manipular o ejecutar objetos concretos (tablas, vistas, secuencias, procedimientos, funciones o paquetes). Por ejemplo, borrar o consultar filas de una tabla concreta Al igual que en otros SGBD, el comando que asigna los pri- vilegios en Oracle es GRANT. De la misma forma que se le pueden asignar privilegios, usando el comando REVOKE se le pueden denegar. Para asignar privilegios, la sintaxis del comando GRANT es: Sintaxis GRANT tipo_privilegio [(columna)][,tipo_privilegio[(columna)]… ON {nombre_tabla|*|*.*|base_datos.nombre_tabla} TO usuario [IDENTIFIED BY[PASSWORD]'password']]… [WITH option[,opción]… 46  Bases de datos - B GRANTOPTION |MAX_QUERIES_PER_HOUR count |MAX_UPDATES_PER_HOUR count |MAX_CONNECTIONS_PER_HOUR count |MAX_USER_CONNECTIONS count En este caso: tipo_privilegio: es el tipo de permiso que se le asigna al usuario (select, insert, update, etc.) Los objetos sobre los que puede operar el usuario y los privilegios que se le pueden aplicar se muestran en las siguientes expresiones: – nombre_tabla→ sobre la tabla nombre_tabla. – *→ sobre todas las tablas de la BD que se está utilizando. – *.*→ sobre todas las tablas de todas las BBDD. – Base_datos.*→ sobre todas las tablas de la BD base_ datos. – Base_datos.nombre_tabla→ sobre la tabla nombre_ tabla que pertenece a la BD base_datos. 47  Tema 10: Gestión de usuarios TO: indica el usuario al que se quiere otorgar el permiso. Si este no existe, se crea con el password indicado me- diante la cláusula IDENTIFIED BY. WITH permite indicar ciertas opciones: Expresión Función GRANT OPTION Concede a otros usuarios los permisos que tiene el primer usuario. MAX_QUERIES_PER_HOUR count Restringe el número de consultas por hora que puede realizar un usuario. MAX_UPDATES_PER_HOUR count Restringe el número de modificaciones por hora que puede realizar un usuario. MAX_CONNECTIONS_PER_HOUR count Restringe las conexiones por hora que realice un usuario. MAX_USER_CONNECTIOS count Limita el número de conexiones simultáneas que puede tener un usuario. Tipos de privilegios Privilegio Significado ALL Da todos los permisos simples excepto GRANT OPTION. ALTER Permite el uso de ALTER TABLE. ALTER Modifica o borra rutinas almacenadas. ROUTINE CREATE Permite el uso de CREATE TABLE. CREATE Crea rutinas almacenadas. ROUTINE CREATE Permite el uso de CREATE TEMPORARY TABLE. TEMPORARY TABLES CREATE USER Permite el uso de CREATE USER, DROP USER, RENAME USER y REVOKE. 48  Bases de datos - B CREATE VIEW Permite el uso de CREATE VIEW. DELETE Permite el uso de DELETE. DROP Permite el uso de DROP TABLE. EXECUTE Permite al usuario ejecutar rutinas almacenadas. FILE Permite el uso de SELECT, INTO OUTFILE y LOAD DATA INFILE. INDEX Permite el uso de CREATE INDEX y DROP INDEX. INSERT Permite el uso de INSERT. LOCK TABLES Permite el uso de LOCK TABLES en tablas para las que tenga el permiso SELECT. PROCESS Permite el uso de SHOW FULL PROCESSLIST. RELOAD Permite el uso de FLUSH. REPLICATION Permite al usuario preguntar dónde están los servidores maestros o CLIENT esclavos. REPLICATION Necesario para los esclavos de replicación. SLAVE SELECT Permite el uso de SELECT. SHOW Muestra todas las BBDD. DATABASES SHOW VIEW Permite el uso de SHOW CREATE VIEW. SHUTDOWN Permite el uso de mysqladmin shutdown. UPDATE Permite el uso de UPDATE. USAGE Sinónimo de no privileges, permite únicamente la conexión al gestor. GRANTOPTION Posibilita dar permisos. 49  Tema 10: Gestión de usuarios Veamos algunos ejemplos de concesión de permisos: Si deseamos que el usuario ilerna@localhost solo pueda seleccionar las columnas nombreCliente, apellido y direc- ción, procederemos a ejecutar la siguiente sentencia: Ejemplo GRANT SELECT (Nombre, Apellido, Dirección) ON alumnos TO ilerna@localhost; '/home/oracle/oradata/orcl/ejercicios.dbf 'SIZE 1000M autoextend on; Si deseamos otorgar permisos de select a todas las tablas de todas las BBDD, permitiendo al usuario ceder esos permisos a otros usuarios: Ejemplo GRANT SELECT ON *.* TO ilerna@localhost WITH GRANT OPTION; '/home/oracle/oradata/orcl/ejercicios.dbf 'SIZE1000M autoextend on; Nota: Esta sentencia solo es válida para MySQL. Con esta instrucción damos el derecho indicado sobre la tabla que señalemos al usuario citado. La última cláusula, que es opcional, permite al usuario que se le conceda el permiso y la posibilidad de ceder el permiso a otra persona. El DBA (Data Base Administrator) tendrá todos los permi- sos con la cláusula ALL, tanto para concederlos como para revocarlos. Ejemplo REVOKE ALL PRIVILEGES ON*.*FROM ilerna@localhost 10.2.2. Administración de privilegios en MySQL En MySQL podemos distinguir entre los siguientes privi- legios: Privilegios de administrador: estos privilegios permiten a un usuario realizar cualquier acción sobre el servidor MySQL. Son privilegios de carácter global, es decir, no atañen a una BD concreta. Privilegios de la BD: estos privilegios atañen a una BD concreta, por tanto, permiten a un usuario realizar cam- bios en la BD especificada. Privilegios para objetos: estos privilegios permiten al usuario manipular tablas, vistas, índices y rutinas alma- cenadas que haya en una BD. 50  Bases de datos - B En MySQL, al igual que en otros SGBD, el comando para asignar privilegios es GRANT y para eliminarlos es REVOKE. Veamos la sintaxis simplificada del comando GRANT. Sintaxis simplificada GRANT tipo_privilegio ON [tipo_de_objeto] nivel_de_privilegio TO usuario A continuación, vamos a desglosar las opciones para cada variable en esta sintaxis. Tipos de privilegio Los tipos de privilegios son múltiples. Podemos listar algu- nos en la siguiente tabla: Privilegio Significado ALL [PRIVILEGES] Concede todos los privilegios a excepción de GRANT OPTION y PROXY. ALTER Permite el uso de ALTER TABLE. A nivel global, de BD y de tabla. ALTER ROUTINE Modificar o borrar rutinas almacenadas. A nivel global, de BD y de rutina. CREATE Permite crear tablas y BBDD. A nivel global, de BD y tabla. CREATE ROLE Permite la creación de un ROL a nivel global. CREATE ROUTINE Permite crear rutinas almacenadas a nivel global o de BD. CREATE USER Permite el uso de CREATE USER, DROP USER, RENAME USER y REVOKE ALL PRIVILEGES a nivel global. CREATE VIEW Permite crear y modificar vistas. A nivel global, de BD o de tabla. DELETE Permite el uso de DELETE. A nivel global, de BD o de tabla. DROP Permite borrar BBDD, tablas o vistas. A nivel global, de BD o de tabla. EXECUTE Permite al usuario ejecutar rutinas almacenadas. A nivel global, de BBDD y de rutina. FILE Permite al usuario leer o escribir archivos del servidor a nivel global. GRANT OPTION Concede el privilegio de conceder o quitar privilegios a otros usuarios. Niveles: global, BBDD, rutina, tabla y proxy. 51  Tema 10: Gestión de usuarios INSERT Permite el uso del comando INSERT. A nivel global, de BD, de columna y de tabla. LOCK TABLES Permite el uso de LOCK TABLES en tablas para las que tenga el permiso SELECT. A nivel global y de BD. PROCESS Permite al usuario observar todos los procesos (SHOW PROCESSLIST) a nivel global. RELOAD Permite el uso de FLUSH a nivel global. REFERENCES Permite al usuario la creación de claves ajenas. A nivel global, de BD, de tabla y de columna. SELECT Permite el uso de SELECT. A nivel global, de BD, de tabla y de columna. SHOW DATABASES Permite ver todas las BBDD (SHOW DATABASES) a nivel global. SHOW VIEW Permite el uso del SHOW CREATE VIEW. A nivel global, de BD y de tabla. TRIGGER Permite las operaciones de disparadores (triggers). A nivel global, de BD y de tabla. UPDATE Permite el uso de UPDATE. A nivel global, de BD, de tabla y de columna. Nota: es posible consultar la tabla completa con todos los tipos de privilegios en la documentación oficial de MySQL vía web. BUSCA EN LA WEB dev.mysql.com/doc/refman/8.0/en/grant. html#grant-privileges Tipo de objeto Los tipos de objetos sobre los que se les puede poner pri- vilegios son: Tabla (TABLE): refiere a las tablas de una BD Función (FUNCTION): es un bloque con nombre que pue- 52  Bases de datos - B de estar almacenado en la BD y puede ser llamado para ejecutar diferentes acciones. Procedimiento (PROCEDURE): es un bloque de instruc- ciones que se pueden llamar por un nombre específico. Nivel de privilegio Los niveles de privilegio posibles son los siguientes: Expresión El permiso se aplica a: Nombre_tabla La tabla "Nombre_tabla". * A todas las tablas de la BD en uso. *.* Todas las tablas de todas las BBDD. Base_datos.* Todas las tablas de la BD Base_datos. Base_datos. Solamente a la tabla Nombre_tabla de la BBDD Nombre_tabla Base_datos. Tipo de usuario Con el comando GRANT, los tipos de usuario que pueden establecerse en la concesión de privilegios son: Nombre de una cuenta de usuario (USER) Nombre de un rol (ROLE) Para finalizar, con TO y especificando el nombre de usuario, se indica el permiso que se otorgará al usuario. Un ejemplo de uso de concesión de privilegios a un usuario podría ser el siguiente: si quisiéramos darle la opción a la cuenta de usuario mrobinson, la opción de operar con los comandos SELECT y INSERT en todos los objetos de la BD de municipio, realizaríamos la siguiente sentencia: Ejemplo GRANT SELECT, INSERT ON municipio.*TO 'mrobinson'@'localhost'; Para eliminar los privilegios otorgados usamos el comando REVOKE. Ejemplo REVOKE ALL ON municipio.* FROM 'mrobinson'@'localhost'; 53  Tema 10: Gestión de usuarios ponte a prueba Un usuario de una BBDD posee una serie de permisos y privilegios que nos indicarán qué acciones pueden realizar dichos usuarios dentro de la BBDD. a) Verdadero b) Falso ¿Cuáles de las siguientes opciones no es un tipo de privilegios dentro de una BBDD? a) GRANT OPTION b) EXECUTE USER c) INSERT d) SELECT e) ALL f) DELETE 10.3. Administración de roles Un rol es un grupo de privilegios a los que se asigna un nombre. El empleo de roles facilita la administración, de forma que no hace falta especificar uno a uno los privilegios que se conceden a cada nuevo usuario. Basta con asignarle un rol para que herede todos los privilegios de este. Entre sus principales características, podemos destacar: Se puede otorgar a cualquier usuario o rol, pero no a sí mismo, ni de forma circular. Puede tener contraseña. Posee un nombre único en la BD. No forma parte de ningún esquema. Trabajar con roles ofrece una serie de beneficios que, a continuación, vamos a señalar: Simplifican el manejo de privilegios. Se pueden asignar diferentes permisos a un rol y este también puede ser asignado a distintos usuarios. 54  Bases de datos - B Maneja los privilegios de forma dinámica, es decir, si se modifican los privilegios asociados al rol se actualizan di- chos privilegios en todos los usuarios que lo posean de manera inmediata. Disponibilidad selectiva de privilegios. Los roles asigna- dos a un usuario pueden ser activados o desactivados temporalmente y se pueden proteger con clave. El uso de roles disminuye el número de GRANT almace- nados en el diccionario de datos, por lo que mejora la productividad. Oracle proporciona roles predefinidos para ayudar a la ad- ministración de las BBDD. Son los siguientes: CONNECT: incluye únicamente el privilegio CREATE SES- SION, que permite conectar a la BD. Si el usuario se crea con OEM, este rol se asigna automáticamente. RESOURCE: incluye CREATE CLUSTER, CREATE INDEX- TYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE 55  Tema 10: Gestión de usuarios SEQUENCE, CREATE TABLE, CREATE TIGGER y CREATE TYPE. Además, otorga el privilegio UNLIMITED TABLESPACE. DBA: incluye todos los privilegios del sistema mediante la opción WITH ADMIN OPTION. – No incluye arrancar o parar la BD. – SYS y SYSTEM lo poseen. Las definiciones de los roles están almacenadas en el diccionario de datos. Normalmente se crea un rol y a pos- teriori se le asignan privilegios. Así, el grupo de usuarios pertenecientes a este rol adoptan sus privilegios. Veamos un ejemplo en el que primero nos creamos un rol y, a continuación, le asignamos permisos para que estos los adquieran un grupo de usuarios. Ejemplo CREATE ROLE rol_ilerna; GRANT CREATE ANYTABLE, DROP TABLE, INSERT ON Alumnos TO rol_ilerna; GRANT rol_ilerna TO ilerna; ponte a prueba Los roles son grupos de privilegios que se definen en el usuario de forma directa, ya que estos no pueden ser almacenados en la BBDD. a) Verdadero. b) Falso. ¿Cuál de las siguientes opciones no es un beneficio de trabajar con roles? a) Se disminuye el número de GRANT que están almacenados en el diccionario de datos. b) E  s posible activar o desactivar los roles de un usuario. c) Un rol solamente puede abarcar un tipo de permiso. d) E  s posible aplicar un mismo rol a diferentes usuarios. 56  Bases de datos - B 10.4. Administración de perfiles Los perfiles no se manejan en todos los sistemas gestores de BBDD. En el caso de Oracle, sí. En el de MySQL también, pero solo en versiones anteriores, porque en las últimas han caído en desuso. Los perfiles definen un conjunto de límites de recursos de la BD y del sistema operativo para los distintos usuarios. Existe un perfil por defecto llamado DEFAULT que tiene todos los recursos asignados con valor UNLIMITED. Este perfil se asigna por defecto, a no ser que se especifique uno distinto. Cada usuario tiene únicamente un perfil en un instante de tiempo determinado. Necesita el privilegio del sistema CREATE PROFILE, cuya sintaxis es: Sintaxis CREATE PROFILE LIMIT |UNLIMITED|DEFAULT |UNLIMITED|DEFAULT; En la posición de parámetro podemos asignar parámetros sobre recursos o parámetros sobre la contraseña. Algunos de los parámetros que podemos determinar sobre los re- cursos son: SESSIONS_PER_USER: se determina cuántas sesiones simultáneas activas puede tener un perfil, como máximo. CPU_PER_SESSION: se determina el máximo de tiempo de CPU para una sesión. Se expresa en centésimas de se- gundo. CPU_PER_CALL: se determina un tiempo límite de res- puesta para realizar una llamada (en centésimas de segundo). CONNECT_TIME: se determina el tiempo máximo que puede estar activa una sesión. Se expresa en minutos. IDLE_TIME: Se determina el tiempo máximo de inacti- vidad en una sesión (minutos). LOGICAL_READS_PER_SESSION: Determina el número máximo de bloques de datos leídos en una determinada sesión. LOGICAL_READS_PER_CALL: Determina el número máximo de bloques leídos en una llamada. 57  Tema 10: Gestión de usuarios Algunos de los parámetros que podemos determinar sobre los recursos son los siguientes: FAILED_LOGIN_ATTEMPTS: determina el número de intentos fallidos para iniciar sesión en una cuenta. Si se supera la cuenta se bloqueará. Por defecto el valor es de 10 intentos PASSWORD_LIFE_TIME: determina el número de días que una contraseña puede ser usada para iniciar sesión. Por defecto son 180 días En la posición de valor pondremos el valor del parámetro que hayamos asignado. Se asigna el valor UNLIMITED si es un parámetro de recur- so que se puede usar una cantidad de veces ilimitada. Esto suele darse en el caso de parámetros que tienen asignada una contraseña que no ha fijado límites. En cambio, el valor DEFAULT se utiliza cuando queremos omitir algún pará- metro asignado a un usuario por defecto. 10.5. Normativa legal vigente sobre la protección de datos La Ley Orgánica 3/2018, de 5 de diciembre, de Protec- ción de Datos Personales y garantía de los derechos digitales tiene como objetivo garantizar y proteger en todo lo relativo a los datos personales, las libertades pú- blicas y los derechos fundamentales de las personas físicas y especialmente su honor, intimidad y privacidad personal y familiar. Regula el tratamiento que se realiza de los datos de ca- rácter personal, excluyendo los datos recogidos para uso doméstico, las materias clasificadas del estado y aquellos ficheros que recogen datos clasificados sobre terrorismo y otras formas de delincuencia organizada. En el ámbito estatal, la organización encargada del cum- plimiento de dicha orden es la Agencia Española de Protección de Datos (AEPD). BUSCA EN LA WEB En el siguiente enlace podemos acceder al BOE para descargarnos dicha ley y poder estudiarla más detenidamente: Ley Orgánica de Protección de Datos (LOPD). https://www.boe.es/eli/es/lo/2018/12/05/3 58 Bases de datos - B 59 11 PROGRAMACIÓN EN BASES DE DATOS  (BBDD)  Bases de datos - B Cuando hablamos de programación en BBDD, debemos nombrar PL/SQL (Procedural Language/ Structure Query Language) que es el lenguaje procesal diseñado para poder trabajar junto con SQL. Está incluido en Oracle Database Server. A continuación, detallaremos sus principales ca- ponte a prueba racterísticas: Integrado con SQL ¿Cuál de las siguientes Control de errores y excepciones características no nos aporta el lenguaje PL/SQL? Uso de variables a) Uso de variables Estructuras de control de flujo b) P  ermite una programación Soporta programación orientada a objetos (POO) con funciones Programación modular: procedimientos y funciones. c) Trabaja integrado con SQL d) N  o trabaja con estructuras de control de flujo 11.1. Entornos de desarrollo al entorno de las BBDD En informática, un intérprete de comandos es una aplicación que dispone de una interfaz de usuario a través de la cual es posible enviar órdenes al sistema. Los intérpretes de co- mandos tienen una sintaxis establecida que el usuario debe conocer para poder gestionar dichas órdenes al sistema. Los SGBD también poseen su propio intérprete de coman- dos, por los que se pueden enviar consultas al servidor y los resultados son mostrados por la misma pantalla del propio intérprete. Por ende, tanto Oracle como MySQL también tienen sus propios intérpretes de comandos. En el caso de Oracle disponemos de Oracle SQL * Plus y en MySQL nos ofrece MySQL Command Line Client. Respecto a SQL * Plus, es posible invocarlo desde el sistema operativo, ejecutando el siguiente comando: Sintaxis Sqlplus [{usuario[/password][@database] | / |/nolog }] [AS {SYSDBA | SYSOPER}] Intérprete de comandos Oracle SQL * Plus. 61  Tema 11: Programación en bases de datos (BBDD) Por otro lado, en MySQL disponemos de MySQL Command Line, el cual tiene un comando de ayuda muy útil, help, que nos lista aquellos comandos que el mismo intérprete ejecuta por sí mismo. Es decir, comandos auxiliares de la aplicación, independientemente del servidor. Son útiles para salir del intérprete, cambiar de BD, ejecutar un script y otras funciones. Intérprete de comandos de MySQL. 62  Bases de datos - B 11.2. Sintaxis del lenguaje de programación 11.2.1. Bloques en PL/SQL Los bloques son la forma más básica de programar en PL/ SQL. Son fragmentos de código que no se almacenan en la estructura de la BD y para ejecutarlo solo es necesario introducirlos en la consola como si se tratase de SQL. Un bloque lógico está estructurado en tres partes: 1. Parte de declaraciones (opcional): declararemos to- das las variables, constantes, cursores y excepciones definidas por el usuario. 2. Parte ejecutable (requerida): podremos hacer uso de las sentencias de control que ofrecen los lenguajes de programación: ◦ Secuencias, órdenes del lenguaje, asignaciones, llamadas a funciones o procedimientos, etc. Se colo- carán una detrás de otra y las separaremos con punto y coma (;). ◦ La iteración o bucle repetirá una sentencia o secuen- cia de sentencias mientras se cumpla o hasta que deje de cumplirse una condición. 3. Parte de tratamiento de excepciones (opcional): bloque opcional de código donde podremos tratar los posibles errores que genere la parte ejecutable del código. -- Sintaxis [DECLARE ] -- declaraciones de objetos (variables, cursores, excepciones definidas -- por el usuario)] BEGIN -- comandos y sentencias SQL y PL/SQL [EXCEPTION -- acciones para realizar cuando se producen errores] END; / -- fin de sentencia en consola de comandos En el caso de estar ejecutando el código por la conso- la de SQL, el símbolo / nos cierra el bloque de código y este se ejecuta. En caso de no ponerlo, la consola se queda esperando más líneas de código. Este símbolo también lo utilizaremos en el caso de lanzar dos bloques distintos de código en el SQL Developer. Por ejemplo, al lanzar dos creates juntos tendríamos que separar los bloques ejecutables con el símbolo /. 63  Tema 11: Programación en bases de datos (BBDD) Un bloque PL/SQL puede ser un bloque anónimo, un procedimiento o una función. El objetivo de diseño PL/ SQL es conseguir modularidad, es decir, simplificar la complejidad del problema dividiéndolo en problemas más sencillos y fáciles de implementar. Para ello se utilizan pro- cedimientos y funciones. 11.2.2. Variables Al igual que en otros los lenguajes de programación, los lenguajes diseñados para las BBDD también usan variables. Las variables reservan en la memoria un espacio para asig- nar valores concretos y se caracterizan por tener un tipo de información determinado como pueden ser una fecha, una cadena de texto, un valor numérico, etc. Variables en PL/SQL En cuanto al uso, las variables se declaran y se pueden inicializar en la sección declarativa de un bloque PL/SQL. Permite pasar valores a un subprograma PL/SQL mediante parámetros. Existen tres formas: IN: el valor viene del proceso de llamada. Es un dato de entrada y su valor no se cambia. Es el valor por defecto 64  Bases de datos - B OUT: en una salida de programa sin error, el valor del ar- gumento devuelve al proceso de llamada. IN OUT: es una variable de entrada/salida. La sintaxis para la declaración de variables es: Sintaxis nombre_variable[CONSTANT] tipo_dato [NOTNULL][:=|DEFAULT|expresión ]; CONSTANT declara la variable como constante. Su valor no puede cambiar. Debe ser inicializada. Los tipos de datos de las variables PL/SQL son: Compuesto: los tipos compuestos son las tablas, regis- tros, tablas anidadas y arrays. LOB: los tipos de datos LOB permiten almacenar bloques de datos no estructurados como gráficos, imágenes, ví- deos y texto de hasta cuatro gigabytes de tamaño. Escalar: Tipo BINARY-INTEGER Almacena enteros con signo. Subtipos: NATURAL, POSITIVO NUMBER[(precisión, escala)] Almacena números con punto fijo o flotante. CHAR[(longitud, máxima)] Almacena cadenas de caracteres de longitud fija. VARCHAR2(longitud, máxima) Almacena cadenas de caracteres de longitud variable. LONG Almacena cadenas de caracteres de longitud variable, tamaño máximo 2 Gb. RAW Almacena objetos binarios. LONG RAW Almacena objetos binarios de hasta 2 Gb. BOOLEAN Almacena TRUE, FALSE o NULL. DATE Almacena valores de fecha. ROWID Dirección física de una fila de la BDD. Existen otras variables que no son PL/SQL y podemos cla- sificar en: Variables BIND Variables HOST 65  Tema 11: Programación en bases de datos (BBDD) Variables en MySQL En MySQL debemos diferenciar bien entre las palabras clave y las variables. Una palabra clave (keyword) es una palabra que ya posee un significado predefinido en MySQL, como pueden ser BEGIN, SELECT, CREATE, etcétera. Además, las keyword pueden ser reservadas o no. La di- ferencia entre el manejo de una palabra clave reservada y una que no lo es pasa por tener que poner comillas en el caso de que sea reservada. De tal modo que, si en MySQL queremos crear una tabla con una palabra reservada escri- biremos lo siguiente: Ejemplo CREATE TABLE select (id INT, comentario VARCHAR(100)); La sentencia nos dará error y nos aparecerá el siguiente mensaje: MENSAJE de ERROR ERROR 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select (id INT, comentario VARCHAR(100))' at line 1 Sin embargo, al utilizar las comillas simples el sistema eje- cuta la sentencia perfectamente: Ejemplo CREATE TABLE `select` (id INT, comentario VARCHAR(100)); Por otro lado, en MySQL tenemos las variables de usuario, que pueden emplearse para guardar determinados valores y utilizarlos posteriormente. La sintaxis para declarar una variable es la siguiente: Sintaxis SET @nombre_de_variable= expresión; Por ejemplo, vamos a establecer que la variable mínimo tiene un valor de un millón (1000000): Ejemplo SET @minimo =1000000; 66  Bases de datos - B De este modo, si tuviéramos una tabla llamada población con la siguiente información: id nombre cantidad 1 valencia 850000 2 madrid 3500000 2 barcelona 2000000 Resultado de una sentencia select en el entorno Workbench. Para mostrar solo las ciudades que tienen un mínimo de población de un millón de habitantes, podríamos utilizar la variable mínimo anteriormente declarada: Ejemplo SELECT * FROM población WHERE cantidad > @minimo; Y nos devolvería lo siguiente: id nombre cantidad 2 madrid 3500000 2 barcelona 2000000 Resultado de una sentencia select en el entorno Workbench. 11.2.3. wOperadores Los operadores sirven para relacionar variables entre sí, cuya combinación dará lugar a expresiones aportando re- sultados. Siendo de esta forma un fundamento de la lógica en la programación de BBDD. Las operaciones que se pue- den realizar son las siguientes: OPERADOR ACCIÓN ** Potencia + - (unarios) Signo positivo o negativo * Multiplicación / División 67  Tema 11: Programación en bases de datos (BBDD) + Suma - Resta || Concatenar cadenas =, , =,, != mayor que, menor o igual que, mayor o igual que, distinto que, IS NULL, LIKE distinto que BETWEEN, IN Es nulo, como Entre, en NOT Negación lógica de un booleano AND Operador AND lógico entre tipos de datos booleanos OR Operador OR lógico entre tipos de datos booleanos A continuación, se muestra una tabla con ejemplos de expresiones verdaderas (TRUE) utilizando los operadores anteriores: (3 + 5) / 2 = 4 6+4/2=5 5 != 3 0 NULL 5 IS NOT NULL == TRUE 5 BETWEEN 2 AND 10 'Alumno' LIKE '%u%' 6 IN (2,4,6) 'a' NOT IN ('d','e') (1=5) En la programación estructurada se debe hacer uso de dis- tintos tipos de estructuras de control. Las describiremos a continuación. Selección: este tipo de estructuras ejecutan un conjunto de instrucciones dependiendo de si se cumple o no una determinada condición. Sentencia IF: evalúa una expresión y en función de si el valor de esta es verdadero o falso se ejecutan unas ins- trucciones u otras. 68  Bases de datos - B Sintaxis IF condición THEN instrucciones; [ELSIF condición THEN instrucciones;] [ELSE instrucciones;] END IF; / Nota: el contenido de la sintaxis que está entre corchetes […] indica que es opcional incluirlo en el código. Por tanto, no es obligatorio poner ELSE o ELSIF en una sentencia IF. Ejemplo IF dept = 'Administración' THEN numero := numero * 2; ELSIF dept = 'Secretaría' THEN numero := numero * 1.5; ELSE numero := numero * 1.1; END IF; Sentencia CASE: evalúa un conjunto de condiciones hasta encontrar alguna que se cumpla y ejecuta las ins- trucciones que esta tiene asociadas. Sintaxis CASE [expresión] WHEN {condición1|valor1 } THEN bloque_instrucciones_1 WHEN {condición2|valor2 } THEN bloque_instrucciones_2 ELSE bloque_instrucciones_por_defecto END CASE: / Ejemplo SELECT Nombre, CASE EC WHEN 'C' THEN 'Casado/a' WHEN 'S' THEN 'Soltero/a' WHEN 'D' THEN 'Divorciado/a' ELSE 'Otros' END AS "Estado civil" FROM Empleados; 69  Tema 11: Programación en bases de datos (BBDD) Iteración: consiste en la repetición de un conjunto de instrucciones un número determinado de veces. A con- tinuación, se describen distintos tipos de estructuras de iteración. – Estructura repetitiva básica: Sintaxis LOOP sentencias; END LOOP; / EJEMPLO En estos ejemplos de bucles vamos a utilizar la sentencia dbms_output.put_line para ver los valores de la variable cont por pantalla, de tal forma que nos ayude a entender mejor las condiciones de salida de los bucles. Para que esta sentencia funcione correctamente tenemos que lanzar la sentencia SET SERVEROUTPUT ON, que activa el visionado de los mensajes por consola. Estas sentencias vienen explicadas en el punto 4.5 de cursores y transacciones en el apartado de Herramientas de depuración y control de código. En esta unidad formativa también encontraremos algunas funciones de Oracle, las cuales harán referencia a la fecha, al identificador de la fila, etc. Por ejemplo: Sysdate: fecha diaria Rowid: identificador de la fila User: usuario del sistema de BD NLS_Session_Parameters: parámetros de sesión DUAL: tabla del sistema donde encontramos la fecha y otros parámetros Este bucle estará repitiéndose infinitamente, ya que no