Saltar a contenido

SQL - Control de usuarios

Propuesta didáctica

En esta UT finalizaremos los RA2: Crea bases de datos definiendo su estructura y las características de sus elementos según el modelo relacional y RA4: Modifica la información almacenada en la base de datos utilizando asistentes, herramientas gráficas y el lenguaje de manipulación de datos.

Criterios de evaluación

Respecto al RABD.2:

  • CE2g: Se han creado los usuarios y se les han asignado privilegios.
  • CE2h: Se han utilizado asistentes, herramientas gráficas y los lenguajes de definición y control de datos.

Respecto al RABD.4:

  • CE4d: Se han diseñado guiones de sentencias para llevar a cabo tareas complejas.
  • CE4e: Se ha reconocido el funcionamiento de las transacciones.
  • CE4f: Se han anulado parcial o totalmente los cambios producidos por una transacción.
  • CE4g: Se han identificado los efectos de las distintas políticas de bloqueo de registros.
  • CE4h: Se han adoptado medidas para mantener la integridad y consistencia de la información.

Contenidos

Bases de datos relacionales:

  • Usuarios. Privilegios.
  • Lenguaje de control de datos (DCL).

Tratamiento de datos:

  • Transacciones.
  • Políticas de bloqueo. Concurrencia.

Cuestionario inicial

  1. ¿Qué mecanismos de control de acceso implementan los SGBD?
  2. ¿Qué sublenguaje de SQL se encarga de gestionar el control de acceso de los usuarios?
  3. ¿Cómo puedo crear un usuario en MariaDB?

Programación de Aula (8h)

Esta unidad es la novena, impartiéndose a mitad de la segunda evaluación, a principios de febrero, con una duración estimada de 8 sesiones lectivas:

Sesión Contenidos Actividades Criterios trabajados
1 Gestión de usuarios AC901 CE2g, CE2h
2 Gestión de permisos AC902 CE2g, CE2h
4 Transacciones AC903 CE3c, CE3e
5 Uso de commit AC903 CE3c, CE3e
6 Deshaciendo transacciones AC906 CE3e
7 Checkpoints AC907 CE3e
8 Supuesto AC912 CE3c, CE3d, CE3e

Seguridad

La seguridad en un SGBD es fundamental para proteger la integridad, confidencialidad y disponibilidad de la información almacenada.

Para ello, se implementan mecanismos de control de acceso mediante la gestión de usuarios y la asignación de privilegios. Este control permite definir qué operaciones puede realizar cada usuario o rol sobre los distintos objetos de la base de datos, limitando el acceso a información sensible y previniendo acciones no autorizadas.

En SQL, dentro del DCL, las sentencias como GRANT y REVOKE permiten otorgar o revocar permisos de acceso y modificación sobre los datos. De esta forma, se establece un entorno seguro donde cada usuario tiene acceso únicamente a los recursos necesarios para sus funciones.

Además del control de acceso, la seguridad de un SGBD también se refuerza mediante el uso de transacciones y políticas de bloqueo y concurrencia. Las transacciones garantizan que las operaciones sobre la base de datos se ejecuten de manera atómica, consistente, aislada y duradera (propiedades ACID), lo que previene inconsistencias en caso de errores o fallos del sistema.

Para manejar el acceso concurrente de múltiples usuarios, los SGBD implementan mecanismos de bloqueo que evitan conflictos y garantizan la integridad de los datos. Estos bloqueos, combinados con estrategias de control de concurrencia, aseguran que las transacciones se gestionen de forma ordenada y segura, minimizando riesgos como condiciones de carrera, lecturas sucias o actualizaciones perdidas. Todo esto contribuye a mantener la coherencia y fiabilidad de la base de datos frente a accesos simultáneos y posibles fallos.

DCL

El Data Control Language (DCL- Lenguaje de control de datos) se emplea para gestionar los permisos y privilegios de acceso a los objetos dentro de una base de datos, estableciendo políticas de seguridad que determinan qué usuarios o roles pueden interactuar con los datos y de qué manera.

A través del DCL, los administradores de bases de datos pueden otorgar o revocar permisos, asegurando que cada usuario solo pueda realizar las acciones que le han sido expresamente autorizadas. Este control es esencial para proteger la información sensible, evitar accesos no autorizados y mantener la integridad de los datos.

Usuarios

Así pues, el primer paso es la creación de usuarios y sus credenciales.

SQL proporciona comandos específicos para crear, modificar y eliminar usuarios, aunque la sintaxis puede variar ligeramente según el SGBD (como MariaDB, PostgreSQL u Oracle).

Para crear un nuevo usuario se realiza con la sentencia CREATE USER, mediante la sintaxis

CREATE USER nombre_usuario IDENTIFIED BY 'contraseña';

Al indicar la contraseña, esta se encriptará con un hash mediante el algoritmo mysql_native_password. El hash es irreversible, lo que significa que no se puede recuperar la contraseña original a partir de él.

Además, en MariaDB, podemos especificar el host desde donde se permite la conexión:

CREATE USER 'nombre_usuario'@'localhost' IDENTIFIED BY 'contraseña';

Recuerda que cuando comenzamos a usar MariaDB, el usuario se creó desde Docker, pero podemos crear uno nuevo mediante:

CREATE USER s8abd IDENTIFIED BY 's8a' ;

Usuarios existentes

Para averiguar qué usuarios existen en el sistema, en MariaDB debemos hacer una consulta sobre la tabla mysql.user:

select host, user, password from mysql.user;
-- +-----------+-------------+-------------------------------------------+
-- | Host      | User        | Password                                  |
-- +-----------+-------------+-------------------------------------------+
-- | localhost | mariadb.sys |                                           |
-- | localhost | root        | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
-- | %         | root        | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
-- | 127.0.0.1 | healthcheck | *FD0D4D1DB5F16AF81D702C8863D5723BA1D87FCD |
-- | ::1       | healthcheck | *FD0D4D1DB5F16AF81D702C8863D5723BA1D87FCD |
-- | localhost | healthcheck | *FD0D4D1DB5F16AF81D702C8863D5723BA1D87FCD |
-- | %         | s8a         | *3D3F19386045EE9D580D9527B41DF893A95325B7 |
-- | %         | s8abd       | *DFEDA1CE49CA7F95D9C9EDAF0BFA9A21B2BDF0FD |
-- +-----------+-------------+-------------------------------------------+
-- 8 rows in set (0.002 sec)

La columna host indica desde dónde nos podemos conectar a la base de datos, de manera que mediante root sólo nos podemos conectar desde la propia máquina (localhost), mientras que los usuarios s8a y s8abd pueden conectarse desde cualquier lugar (% hace la función de comodín).

Si necesitamos cambiar la contraseña o actualizar las propiedades de un usuario existente, utilizaremos la instrucción ALTER USER, con la sintaxis:

ALTER USER nombre_usuario IDENTIFIED BY 'nueva_contraseña';

También se pueden modificar atributos adicionales, como el límite de conexiones o configuraciones de autenticación.

Finalmente, para eliminar usuarios utilizaremos la sentencia DROP USER, revocando todos sus privilegios y eliminando su acceso a la base de datos.

DROP USER nombre_usuario;

Es recomendable revocar previamente los permisos asignados para asegurar una gestión ordenada.

Permisos

Hemos comentado previamente que los usuarios tendrán diferentes permisos sobre los recursos de una base de datos. Para ello, las principales sentencias del DCL son GRANT y REVOKE.

La instrucción GRANT permite asignar permisos específicos a usuarios o roles, como la capacidad de consultar (SELECT), insertar (INSERT), actualizar (UPDATE) o eliminar (DELETE) datos en tablas o vistas. También se pueden otorgar privilegios sobre procedimientos almacenados o esquemas completos.

Por otro lado, la sentencia REVOKE se utiliza para retirar esos permisos cuando ya no son necesarios o cuando se detecta un posible riesgo de seguridad.

Además, algunos sistemas permiten incluir la opción WITH GRANT OPTION, que autoriza a un usuario a conceder los privilegios que ha recibido a otros usuarios. Esta gestión granular de permisos es clave para implementar políticas de seguridad robustas, evitando accesos indebidos y minimizando el riesgo de errores o ataques malintencionados.

Comprobando permisos

¿Cómo puedo saber los permisos que tengo? Para ello, necesitamos ejecutar el comando SHOW GRANTS:

SHOW GRANTS for CURRENT_USER;
-- +----------------------------------------------------------------------------------------------+
-- | Grants for s8a@%                                                                             |
-- +----------------------------------------------------------------------------------------------+
-- | GRANT ALL PRIVILEGES ON *.* TO `s8a`@`%` IDENTIFIED BY PASSWORD '*3D...B7' WITH GRANT OPTION |
-- | GRANT ALL PRIVILEGES ON `pruebas`.* TO `s8a`@`%`                                             |
-- +----------------------------------------------------------------------------------------------+
-- 2 rows in set (0.001 sec)

En cambio, si comprobamos los permisos del usuario que hemos creado en esta sesión:

SHOW GRANTS for s8abd;
-- +---------------------------------------------------------------------+
-- | Grants for s8abd@%                                                  |
-- +---------------------------------------------------------------------+
-- | GRANT USAGE ON *.* TO `s8abd`@`%` IDENTIFIED BY PASSWORD '*DF...FD' |
-- +---------------------------------------------------------------------+
-- 1 row in set (0.003 sec)

Tras crear un usuario, es necesario asignar permisos mediante el DCL con GRANT y retirarlos con REVOKE.

Otorgando permisos

Así pues, mediante la sentencia GRANT daremos privilegios a un usuarios sobre uno o más objetos, mediante la siguiente sintaxis:

GRANT privilegio ON objeto TO usuario [WITH GRANT OPTIONS]

Por ejemplo,

GRANT SELECT, INSERT ON empresa TO s8abd;
GRANT SELECT, INSERT ON retail.customers TO s8abd;

Si queremos dar permisos de administración, podemos usar: 

GRANT ALL PRIVILEGES ON base_de_datos.* TO nombre_usuario;

Una gestión adecuada de usuarios implica también definir roles, agrupar permisos y establecer políticas de acceso según los principios de mínimos privilegios, donde cada usuario tiene solo los permisos estrictamente necesarios.

Permisos disponibles

SHOW PRIVILEGES: muestra todos los privilegios posibles.

  • USAGE: para usar un objeto específico de la base de datos.
  • SELECT: para acceder a tablas o vistas.
  • INSERT[(nombre_columna)]: Si se especifica el nombre_columna, se otorga permiso para insertar en la columna especificada. Si se omite entonces se permite insertar valores en todas las columnas.
  • UPDATE[(nombre_columna)]: Lo mismo que INSERT para modificar.
  • DELETE: para eliminar registros de una tabla o vista.
  • REFERENCES[(nombre_columna)]: Si se especifica nombre_columna permite referirse a la columna indicada como vínculo de integridad, si se omite se aplica a todas las comunas y también a las que se añadan después.
  • GRANT OPTION: permite dar permisos a otro usuario.
  • ALL: otorga todos los permisos menos GRANT OPTION.

Quitando permisos

Revocar privilegios sobre un objeto: REVOKE

REVOKE privilegio ON objeto FROM usuario

CREATE USER 'desarrollador'@'localhost' IDENTIFIED BY 'desarrollador';
GRANT SELECT, UPDATE ON empleado to 'desarrollador'@'localhost';
GRANT SELECT, UPDATE ON oficina to 'desarrollador'@'localhost';
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'admin';
GRANT ALL PRIVILEGES ON jardineria.* TO 'admin'@'localhost' WITH GRANT OPTION;
SHOW GRANTS FOR CURRENT_USER;

PhpMyAdmin

Transacciones

Referencias

Actividades

  • AC901. (RABD.2 // CE2g, CE2h // 3p) Sobre la instalación que tienes de MariaDB, realiza las siguientes operaciones:

    1. asdf

  • AR916. (RABD.3 // CE6b, CE6c, CE6d, CE6e, CE6f, CE6g // 3p) Una vez finalizada la unidad, responde todas las preguntas del cuestionario inicial, con al menos un par de líneas para cada una de las cuestiones.