UNIDAD 3 Usuarios, Privilegios y Roles de las BD PDF

POLITECNICO MARIA DE LA ALTAGRACIA, DE VILLA DUARTE (POMAVID).- “Virtud y Ciencia por un Mundo Mejor” Profesora: Flor Denise Matos Pérez Módulo 5: Administración de Base de Datos (ABD-BDA) Nivel: 3 Código: MF_057_3 Duración: 405 horas Año Escolar: 2024-...

POLITECNICO MARIA DE LA ALTAGRACIA, DE VILLA DUARTE (POMAVID).- “Virtud y Ciencia por un Mundo Mejor” Profesora: Flor Denise Matos Pérez Módulo 5: Administración de Base de Datos (ABD-BDA) Nivel: 3 Código: MF_057_3 Duración: 405 horas Año Escolar: 2024-2025.- UNIDAD 3: USUARIOS, PRIVILEGIOS, ROLES Y PERMISOS DE LAS BASES DE DATOS Usuarios y administradores de una base de datos El objetivo principal de un sistema de bases de datos es recuperar información y almacenar nueva información en la base de datos. Las personas que trabajan con una base de datos se pueden catalogar como usuarios de bases de datos o como administradores de bases de datos. Administrador de la base de datos Es el responsable técnico y puede acceder a cualquiera de los aspectos del sistema, configurando o modificando cualquier parámetro de éste. Este usuario tiene control total, por lo que puede realizar cualquier operación de configuración o mantenimiento sobre este. Una de las principales razones de usar SGBD es tener un control centralizado tanto de los datos como de los programas que acceden a esos datos. La persona que tiene este control central sobre el sistema se llama administrador de la base de datos (ABD). Podemos definir a los usuarios como toda persona que tenga todo tipo de contacto con el sistema de base de datos desde que éste se diseña, elabora, termina y se usa. Usuarios de bases de datos e interfaces de usuario Hay cuatro tipos diferentes de usuarios de un sistema de base de datos diferenciados en la forma en que ellos esperan interactuar con el sistema, de manera que se desarrollan interfaces diferentes estas para cada uno de estos usuarios. Prof. Flor Denise Matos Unidad 3 Usuarios, Roles, Privilegios y Permisos de las BD Página 1 Usuarios normales Son usuarios no sofisticados que interactúan con el sistema mediante la innovación de algunos programas de aplicación que se ha escrito previamente. Un ejemplo lo encontramos en un cajero bancario que necesita transferir determinada cantidad de dinero de una cuenta a otra invocando un programa llamado transferir, este programa pide al cajero el importe de dinero a transferir, la cuenta de donde se tomara el dinero y la cuenta a donde se transferirá el dinero. De manera que la interfaz de un usuario normal es una interfaz de formularios (ver figura), donde el usuario puede rellenar los campos apropiados del formulario. Este tipo de usuarios también pueden leer informes generados de la base de datos. Programadores de aplicaciones Son profesionales informáticos que escriben programas de aplicación. Los programadores de aplicaciones pueden elegir entre muchas herramientas (ver figura) para desarrollar interfaces de usuario. Las herramientas de desarrollo rápido de aplicaciones (DRA) son herramientas que permiten al programador de aplicaciones construir formularios e informes sin escribir un programa. Hay también tipos especiales de lenguajes de programación que combinan estructuras de control imperativo (por ejemplo, para bucles for, bucles while e instrucciones if then-else) con instrucciones del lenguaje de manipulación de datos. Estos lenguajes, llamados a veces lenguajes de cuarta generación, a menudo incluyen características especiales para facilitar la generación de formularios y la presentación de datos en pantalla. Usuarios sofisticados Interactúan con el sistema sin programas escritos. En su lugar, ellos forman sus consultas en un Prof. Flor Denise Matos Unidad 3 Usuarios, Roles, Privilegios y Permisos de las BD Página 2  lenguaje de consulta de bases de datos (ver figura). Cada una de estas consultas se envía al procesador de consultas, cuya función es transformar instrucciones LMD a instrucciones que el gestor de almacenamiento entienda. Los analistas que envían las consultas para explorar los datos en la base de datos entran en esta categoría. Usuarios especializados Son usuarios que escriben aplicaciones de bases de datos especializadas que no son adecuadas en el marco de procesamiento de datos tradicional. Entre estas aplicaciones están los sistemas de diseño asistido por computador, sistemas de bases de conocimientos y sistemas expertos, sistemas que almacenan los datos con tipos de datos complejos (por ejemplo, datos gráficos y datos de audio) y sistemas de modelado del entorno. Tarea de los usuarios dentro de las bases de datos Procesar objetos de base de datos Leer datos de base de datos. Leer metadatos de base de datos. Agregar usuarios a funciones de base de datos existentes. Crear funciones de base de datos nuevas. Definir permisos para funciones de base de datos. Usuarios MySQL Los usuarios de MySQL se utilizan para conectarse a su servidor de base de datos. MySQL tiene su propio sistema de permisos complejo, y cada vez que necesites acceder a una base de datos MySQL, debes iniciar sesión con un nombre de usuario y una contraseña que reconozcas. Cada vez que crees una nueva base de datos MySQL, deberás especificar un nombre de usuario único que desees crear para permitir el acceso a él. Cuando creas un usuario, solo tienes acceso a MySQL y debe ser único en todas las demás cuentas de MySQL. MySQL también verifica desde dónde se está conectando y solo permite el acceso desde direcciones IP o hosts autorizados. ROLES En general se emplean para asignar los privilegios relacionados con los usuarios finales de las aplicaciones de un sistema o para asignar roles a otro roles. Los usuarios se pueden agrupar en roles en función de las operaciones que se les permite realizar. Prof. Flor Denise Matos Unidad 3 Usuarios, Roles, Privilegios y Permisos de las BD Página 3 Es decir, que cada rol se caracteriza por disponer de una serie concreta de permisos. Se entiende por permiso la posibilidad de ejecutar determinadas operaciones o no sobre los diferentes elementos del CMS. Sistema de gestión de contenido. Los roles de la base de datos tienen la siguiente funcionalidad: 1) Un rol puede tener privilegios del sistema y privilegios de objetos del schema. 2) Un rol se puede asignar a otros roles (excepto a sí mismo directa o indirectamente). 3) A cualquier usuario de la base de datos se le puede asignar cualquier rol. 4) Un rol asignado a un usuario se puede habilitar o inhabilitar en cualquier momento. 5) Un rol garantizado indirectamente puede ser explícitamente habilitado o inhabilitado al usuario. Un privilegio es un derecho para ejecutar un tipo particular de sentencia o para acceder a un objeto de otro usuario. Un usuario puede recibir los privilegios de dos maneras: 1. Explícitamente 2. Se asignan privilegios a un rol (un conjunto nominado de privilegios) y luego se asignan estos roles a uno o más usuarios. El objetivo de los roles es permitir una mejor administración de los privilegios, por lo general, se deberían garantizar privilegios a los roles y no a los usuarios individuales Niveles de privilegios Los conceptos de usuarios y privilegios están íntimamente relacionados. No se pueden crear usuarios sin asignarle al mismo tiempo privilegios. De hecho, la necesidad de crear usuarios está ligada a la necesidad de limitar las acciones que tales usuarios pueden llevar a cabo. MySQL permite definir diferentes usuarios, y además, asignar a cada uno determinados privilegios en distintos niveles o categorías de ellos. En MySQL existen cinco niveles distintos de privilegios:  Globales: se aplican al conjunto de todas las bases de datos en un servidor. Es el nivel más alto de privilegio, en el sentido de que su ámbito es el más general.  De base de datos: se refieren a bases de datos individuales, y por extensión, a todos los objetos que contiene cada base de datos.  De tabla: se aplican a tablas individuales, y por lo tanto, a todas las columnas de esas tabla. Prof. Flor Denise Matos Unidad 3 Usuarios, Roles, Privilegios y Permisos de las BD Página 4   De columna: se aplican a una columna en una tabla concreta.  De rutina: se aplican a los procedimientos almacenados. Aún no hemos visto nada sobre este tema, pero en MySQL se pueden almacenar procedimientos consistentes en varias consultas SQL. Hay algunas cosas que no se pueden hacer con el sistema de privilegios de MySQL:  No se puede especificar explícitamente que a un usuario se le deba denegar el acceso.  No se puede especificar que un usuario tenga privilegios para crear o eliminar tablas en una base de datos, pero que no pueda crear o eliminar la propia base de datos. Hasta ahora hemos usado sólo el usuario 'root', que es el administrador, y que dispone de todos los privilegios disponibles en MySQL. Sin embargo, normalmente no será una buena práctica dejar que todos los usuarios con acceso al servidor tengan todos los privilegios. Para conservar la integridad de los datos y de las estructuras será conveniente que sólo algunos usuarios puedan realizar determinadas tareas, y que otras, que requieren mayor conocimiento sobre las estructuras de bases de datos y tablas, sólo puedan realizarse por un número limitado y controlado de usuarios. Autenticarnos en MYSQL SERVER Para que nosotros generemos un nuevo usuario lo primero que debemos de hacer es autenticarnos en el servidor. (Eso lo hace el DBA). Para esto ejecutamos la siguiente línea de código desde el prompt o el command line de Mysql como se muestra en la siguiente figura. Crear usuarios: Hay varias maneras de crear usuarios en nuestras BD. Pueden ser: 1.- Con privilegios: Cuando usamos GRANT en lugar de CREATE USER Prof. Flor Denise Matos Unidad 3 Usuarios, Roles, Privilegios y Permisos de las BD Página 5 Vemos si tenemos acceso a las BD: Ejecutamos el siguiente comando. Como podemos ver las restricciones son limitadas, solo aparecen las BD por defecto. Hay que tener en cuenta que la contraseña se debe Nota: introducir entre comillas de forma obligatoria. 2.- Sin privilegios: Cuando usamos CREATE USER en lugar de GRANT Particularmente yo sugiero usar siempre la de CREATE USER, ya que le permite al DBA tener un mayor control al momento de otorgar permisos a los usuarios de las BD. Hasta este punto, nosotros ya podemos autenticarnos con el servidor utilizando el nuevo usuario, sin embargo, una vez autenticado las acciones que podemos hacer son mínimas, debido a que este usuario no posee los permisos necesarios para trabajar con las bases de datos. Veamos el siguiente ejemplo: Si queremos visualizar las bases de datos que tenemos diseñadas en nuestro servidor solo podremos ver las que están por defecto, ya que aún no tenemos los permisos necesarios. Tal como se muestra en la imagen. Para que este usuario pueda tener acceso a los objetos de la BD se debe asignar los permisos. Veamos Conceder privilegios y o permisos Para que un usuario pueda hacer algo más que consultar algunas variables del sistema debe tener algún privilegio, para eso está la función GRANT de Mysql. Para poder establecer permisos, las siguientes sentencias deben de ejecutarse utilizando el usuario root. Para esto vuelvo y me conecto como DBA. Prof. Flor Denise Matos Unidad 3 Usuarios, Roles, Privilegios y Permisos de las BD Página 6  Si queremos que el nuevo usuario tenga permisos de administrador (Todos los permisos), debemos de ejecutar la siguiente sentencia. Los asteriscos indican que los permisos serán asignados a todas las bases de datos y a todas las tablas (primer asteriscos bases de datos, segundo asterisco tablas). Para verificar si ya tenemos todos los permisos al servidor de la base de datos, pues visualizamos nueva vez el comando show databases, y vemos que si nos salen todas las bases de datos. Asignar permisos DDL, DML y DCL: Veamos que significan cada uno DDL: Lenguaje de Definición de Datos significa Data Definition Language o Lenguaje de Definición de Datos, en español. Este lenguaje permite definir las tareas de las estructuras que almacenarán los datos. Sentencias de DDL (Data Definition Language)  CREATE: Utilizado para crear nuevas tablas, campos e índices.  ALTER: Utilizado para modificar las tablas agregando campos o cambiando la definición de los campos.  DROP: Empleado para eliminar tablas e índices.  TRUNCATE: Empleado para eliminar todos los registros de una tabla.  RENAME: Tal como su nombre lo indica es utilizado para renombrar objetos. (Lo veremos más adelante con mayor detalle). ¿Qué es DML? Lenguaje de Manipulación de Datos DML significa Data Manipulation Language o Lenguaje de Manipulación de Datos, en español. Este lenguaje permite realizar diferentes acciones a los datos que se encuentran en una base de datos. Permite recuperar, almacenar, modificar, eliminar, insertar y actualizar datos de una base de datos. Elementos del DML (Data Manipulation Language) Prof. Flor Denise Matos Unidad 3 Usuarios, Roles, Privilegios y Permisos de las BD Página 7   SELECT: Utilizado para consultar registros de la base de datos que satisfagan un criterio determinado.  INSERT: Utilizado para cargar de datos en la base de datos en una única operación.  UPDATE: Utilizado para modificar los valores de los campos y registros especificados  DELETE: Utilizado para eliminar registros de una tabla de una base de datos. ¿Qué es DCL? Lenguaje de Control de Datos Permite crear roles, permisos e integridad referencial, así como el control al acceso a la base de datos. Elementos del DCL (Data Control Language)  GRANT: Usado para otorgar privilegios de acceso de usuario a la base de datos.  REVOKE: Utilizado para retirar privilegios de acceso otorgados con el comando GRANT. Lenguaje de consulta de datos (DQL) Los comandos DQL, por su parte, se utilizan para consultar y analizar datos en tablas almacenadas. Algunos ejemplos de comandos DQL son SELECT, DISTINCT, y operadores matemáticos como COUNT, SUM, AVG, MIN y MAX. Esto es lo que te permiten hacer estos operadores:  DISTINCT: Recupera valores únicos de una columna especificada, eliminando los duplicados.  COUNT: Devuelve el número de filas que coinciden con una condición especificada.  SUM: Calcula la suma total de una columna numérica.  AVG: Calcula el valor medio de una columna numérica.  MIN: Busca el valor mínimo de una columna.  MAX: Busca el valor máximo de una columna. Nota: Select pertenece tanto a los DML como DQL Si queremos asignar permisos para ciertas acciones, la sentencia quedaría de la siguiente manera. Reemplazamos ALL PRIVILEGES y colocamos las acciones que queremos asignar. Prof. Flor Denise Matos Unidad 3 Usuarios, Roles, Privilegios y Permisos de las BD Página 8 En esta ocasión estamos indicando que el nuevo usuario podrá consultar, crear, actualizar y eliminar registros, así cómo podrá crear o eliminar elementos (tablas, índices, columnas, funciones, gestores, etc). Como se ve, para este usuario sólo existe la base de datos 'punto_de_venta'. Además, podrá hacer consultas, insertar, actualizar eliminar, crear y borrar datos solo dentro de esa base de datos, no para las demás que existan. Para conceder privilegios globales se usa ON *.*, para indicar que los privilegios se conceden en todas las tablas de todas las bases de datos. Para conceder privilegios en bases de datos se usa ON nombre_db.*, indicando que los privilegios se conceden sobre todas las tablas de la base de datos 'nombre_db'. Usando ON nombre_db.nombre_tabla, concedemos privilegios de nivel de tabla para la tabla y base de datos especificada. Todos estos permisos serán válidos únicamente en la base de datos punto_de_venta y se aplicarán a todas las tablas. Si queremos asignar permisos únicamente a una tabla, reemplazamos el asterisco por el nombre de la tabla. Ejemplo punto_de_venta.productos Aquí un listado de algunos permisos y/ o privilegios que podemos asignar.  ALL: para conceder todos los privilegios.  CREATE permite crear nuevas tablas o bases de datos.  DROP permite eliminar tablas o bases de datos.  DELETE permite eliminar registros de tablas.  INSERT permite insertar registros en tablas.  SELECT permite leer registros en las tablas.  UPDATE permite actualizar registros en las tablas.  GRANT OPTION Permite otorgar privilegios de otros usuarios.  SHOW DATABASE Permite listar las bases de datos existentes. Recuerda: Un detalle importante es que para pasar sus permisos de privilegios a otros usuarios se debe tener el privilegio GRANT OPTION, y que sólo se pueden conceder privilegios que se posean. La opción WITH GRANT OPTION facilita al usuario el poder de darles a otros usuarios sus mismos privilegios. Con la siguiente query darías todos los permisos (excepto WITH GRANT OPTION) a un usuario sobre una base de datos especificada: Prof. Flor Denise Matos Unidad 3 Usuarios, Roles, Privilegios y Permisos de las BD Página 9  Veamos. Para dar todos los permisos, incluido el WITH GRANT OPTION, debe de indicarse con su opción pertinente. Veamos. Cabe destacar que esto no es aconsejable, y es altamente inseguro ya que únicamente el usuario administrador debería poder agregar o eliminar privilegios. De la siguiente forma únicamente le darías permiso para realizar algunas queries sobre la base de datos. Cabe destacar que el usuario no podría crear nuevas tablas ni nuevas bases de datos. Veamos. Listar los usuarios creados Listado de todos los usuarios. SELECT User FROM mysql.user; y/o SELECT * from mysql.user; La función REVOKE es empleada para retirar privilegios a los usuarios. Su sintaxis es muy similar a la de GRANT. Veamos. Remover permisos en concreto (Ejemplo update y delete). REVOKE UPDATE, DELETE ON *.* FROM ‘usuario’@’localhost’; Remover todos los privilegios. REVOKE ALL PRIVILEGES ON *.* FROM’usuario’@localhost’; De este modo quitarías todos los permisos del usuario. Veamos Mostrar los privilegios de un usuario Podemos ver qué privilegios se han concedido a un usuario mediante la sentencia SHOW GRANTS. La salida de esta sentencia es una lista de sentencias GRANT que se deben ejecutar para conocer los privilegios que tiene el usuario. Prof. Flor Denise Matos Unidad 3 Usuarios, Roles, Privilegios y Permisos de las BD Página 10  Para mostrar los permisos a un usuario en específico Nombres de usuarios y contraseñas Como podemos ver por la salida de la sentencia SHOW GRANTS, el nombre de usuario no se limita a un nombre simple, sino que tiene dos partes. La primera consiste en un nombre de usuario, en nuestro ejemplo 'Flor'. La segunda parte, que aparece separada de la primera por el carácter '@' es un nombre de máquina (host). Este nombre puede ser bien el de una máquina, por ejemplo, 'localhost' para referirse al ordenador local, o cualquier otro nombre, o bien una IP. Conexiones remotas a MYSQL Si creamos un usuario para una máquina o conjunto de máquinas determinado, ese usuario no podrá conectar desde otras máquinas. Por ejemplo: Esto quiere decir que este usuario solo podrá entrar desde el mismo ordenador donde se está ejecutando el servidor. Veamos este otro ejemplo. Esto quiere decir que el usuario 'denise' sólo puede conectarse desde un ordenador cuyo IP sea '10.28.56.15'. Conexiones remotas o locales: Cuando indicamos el % estamos diciendo que se puede conectar desde cualquier maquina Prof. Flor Denise Matos Unidad 3 Usuarios, Roles, Privilegios y Permisos de las BD Página 11 Ayuda del comando Grants: Recordad que el comando HELP de la shell de MySQL os mostrará la ayuda relativa a cada comando. Para encontrar ayuda de GRANT usamos HELP GRANT y vemos que además de la información relativa al comando aparecen varios ejemplos de uso: Flush privileges: Sirve para recargar la tabla de privilegios pero sólo es necesario cuando se manipulan directamente las tablas de privilegios ejecutando INSERT, DELETE. Eliminar y/o Borrar usuarios Para eliminar usuarios se usa la sentencia DROP USER. Por ejemplo: DROP USER’usuario´@’localhost; Aquí una ilustración de todos los lenguajes que maneja MYSQL Prof. Flor Denise Matos Unidad 3 Usuarios, Roles, Privilegios y Permisos de las BD Página 12