Unidad. Lenguaje de control de datos (DCL)

Apuntes de BD para DAW, DAM y ASIR

José Juan Sánchez Hernández

Curso 2023/2024

1 El lenguaje DCL de SQL

El DCL (Data Control Language) o Lenguaje de Control de Datos es la parte de SQL dedicada a controlar el acceso a los datos de una base de datos. Las sentencias DCL más utilizadas son las siguientes:

GRANT: se utiliza para asignar permisos a un usuario o rol.
REVOKE: se utiliza para eliminar los permisos que se han asignado a un usuario o rol.

1.1 Gestión de usuarios en MySQL Server

1.1.1 Crear un nuevo usuario

La sintaxis simplificada para crear un usuario en MySQL es la siguiente:

CREATE USER [IF NOT EXISTS]
    user [auth_option] [, user [auth_option]] ...
    DEFAULT ROLE role [, role ] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...
    [COMMENT 'comment_string' | ATTRIBUTE 'json_object']

Ejemplo:

Habrá que reemplazar nombre_usuario y contraseña por los datos del nuevo usuario que desea crear:

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

Una vez que hemos creado el usuario hay que asignarle permisos para que pueda acceder a la/s base/s de datos que queramos.

Referencia:

Documentación oficial de CREATE USER en MySQL

1.1.2 Eliminar un usuario

La sintaxis para eliminar un usuario en MySQL es la siguiente:

DROP USER [IF EXISTS] nombre_usuario [, nombre_usuario] ...

Ejemplo:

DROP USER IF EXISTS 'nombre_usuario'@'localhost';

Referencia:

Documentación oficial de DROP USER en MySQL

1.1.3 Obtener el listado de usuarios

Los usuarios de MySQL se almacenan en la tabla mysql.user. La clave primaria de esta tabla está formada por los valores user y host, de modo que cada fila vendrá identificada por un nombre de usuario y el host desde el que puede conectarse.

La siguiente consulta nos devuelve el listado de usuarios que tenemos en MySQL y desde qué host pueden conectarse:

SELECT user,host FROM mysql.user;

En nuestra caso la consulta anterior devuelve el siguiente resultado:

+------------------+--------------+
| user             | host         |
+------------------+--------------+
| root             | localhost    |
| debian-sys-maint | localhost    |
| mysql.session    | localhost    |
| mysql.sys        | localhost    |
+------------------+--------------+

El siguiente diagrama muestra un ejemplo de dos usuarios que se están conectando a una máquina con MySQL Server. El usuario root@localhost es un usuario que sólo puede conectarse desde la máquina local, mientras que el usuario root@'%' es un usuario que se puede conectar desde una máquina remota.

El carácter % es un comodín que indica que el usuario se puede conectar desde cualquier dirección IP. También es posible indicar una dirección IP concreta o un rango de direcciones IP.

Por ejemplo, en la siguiente tabla el usuario user1 sólo puede conectarse desde la dirección IP 172.16.0.11 y el usuario user2 sólo puede conectarse desde direcciones IP que empiezan por 172.16., que sería equivalente a la red 172.16.0.0/16.

+------------------+--------------+
| user             | host         |
+------------------+--------------+
| user1            | 172.16.0.11  |
| user2            | 172.16.%     |
+------------------+--------------+

1.2 Gestión de privilegios o permisos

1.2.1 Tipos de permisos que podemos aplicar

ALL PRIVILEGES: con esta opción podemos asignar todos los privilegios de una vez.
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 seleccionados en tablas.
WITH GRANT OPTION: permite a un usuario asignar sus privilegios a otros usuarios.

1.2.2 Asignar permisos a un usuario

La sintaxis simplificada para asignar permisos a un usuario en MySQL es la siguiente:

GRANT permiso ON nombre_base_de_datos.nombre_tabla TO 'nombre_usuario'@'localhost';

Ejemplo 1:

GRANT ALL PRIVILEGES ON *.* TO 'nombre_usuario'@'localhost';

En este comando, los asteriscos indican que estamos aplicando el permiso ALL PRIVILEGES al usuario nombre_usuario para todas las tablas de cada una de las bases de datos.

Después de este comando habrá que ejecutar el siguiente comando para refrescar todos los privilegios a los usuarios.

FLUSH PRIVILEGES;

Referencia:

Documentación oficial de GRANT en MySQL

Ejemplo 2:

En este ejemplo vamos a crear la base de datos prestashop y el usuario user@localhost, y le vamos a asignar todos los permisos sobre la base datos.

-- Creamos la base de datos prestashop
DROP DATABASE IF EXISTS prestashop;
CREATE DATABASE prestashop CHARACTER SET utf8mb4;
USE prestashop;

-- Creamos un usuario y le asignamos todos los persisos
DROP USER IF EXISTS 'user'@'localhost';
CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON prestashop.* TO 'user'@'localhost';

-- Refrescamos los privilegios si queremos que se apliquen los cambios inmediatamente
FLUSH PRIVILEGES;

Ejemplo 3:

En este ejemplo vamos a crear la base de datos prestashop y el usuario user@localhost, pero esta vez sólo le vamos a asignar al usuario los permisos de SELECT, INSERT, UPDATE y DELETE.

-- Creamos la base de datos prestashop
DROP DATABASE IF EXISTS prestashop;
CREATE DATABASE prestashop CHARACTER SET utf8mb4;
USE prestashop;

-- Creamos un usuario y le asignamos todos los persisos
DROP USER IF EXISTS 'user'@'localhost';
CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE, DELETE ON prestashop.* TO 'user'@'localhost';

-- Refrescamos los privilegios si queremos que se apliquen los cambios inmediatamente
FLUSH PRIVILEGES;

Ejemplo 4:

En este ejemplo vamos a utilizar la opción WITH GRANT OPTION para que el usuario pueda asignar sus privilegios a otros usuarios.

-- Creamos la base de datos prestashop
DROP DATABASE IF EXISTS prestashop;
CREATE DATABASE prestashop CHARACTER SET utf8mb4;
USE prestashop;

-- Creamos un usuario y le asignamos todos los persisos
DROP USER IF EXISTS 'user'@'localhost';
CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON prestashop.* TO 'user'@'localhost' WITH GRANT OPTION;

-- Refrescamos los privilegios si queremos que se apliquen los cambios inmediatamente
FLUSH PRIVILEGES;

Como el usuario 'user'@'localhost' tiene la opción WITH GRANT OPTION y tiene todos los permisos (ALL PRIVILEGES) sobre todas las tablas de base de datos prestashop, podrá asignar asignar estos mismos permisos a otros usuarios sobre las mismas tablas de la base de datos prestashop.

Ejemplo 5:

En el siguiente ejemplo el usuario 'user'@'localhost' sólo podrá asignar a otros usuarios el permiso de SELECT sobre la tabla customer de la base de datos prestashop.

GRANT SELECT ON prestashop.customer TO 'user'@'localhost' WITH GRANT OPTION;

1.2.3 Eliminar permisos a un usuario

La sintaxis simplificada para elminar permisos a un usuario en MySQL es la siguiente:

REVOKE permiso ON nombre_base_de_datos.nombre_tabla FROM 'nombre_usuario'@'localhost';

Referencia:

Documentación oficial de REVOKE en MySQL

Ejemplo:

En este ejemplo vamos a eliminar los permisos de INSERT, UPDATE y DELETE, que tiene el usuario user@localhost sobre todas las tablas de la base de datos prestashop.

REVOKE INSERT, UPDATE, DELETE ON prestashop.* FROM 'user'@'localhost';

Si queremos que los cambios se apliquen inmediatamente, tendremos que ejecutar la sentencia:

FLUSH PRIVILEGES;

1.2.4 Cómo consultar los permisos de un usuario

También podemos consultar qué permisos específicos tiene un determinado usuario. La siguiente consulta nos devuelve los permisos que tiene el usuario root:

SHOW GRANTS FOR root@localhost;

+---------------------------------------------------+
| Grants for root@localhost                         |
+---------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' |
+---------------------------------------------------+

1.3 Gestión de roles

Un rol es un conjunto de privilegios que se pueden asignar a uno o más usuarios.

1.3.1 Crear un rol

La sintaxis para crear un rol en MySQL es la siguiente:

CREATE ROLE [IF NOT EXISTS] nombre_rol [, nombre_rol ] ...

Para asignar privilegios a un rol se utiliza la sentencia GRANT:

GRANT permiso ON nombre_base_de_datos.nombre_tabla TO nombre_rol;

Para asignar un rol a un usuario también se utiliza la sentencia GRANT:

GRANT nombre_rol TO nombre_usuario;

Referencia:

Documentación oficial de CREATE ROLE en MySQL

Ejemplo:

En este ejemplo vamos a crear tres roles: rol_lectura_escritura, rol_lectura y rol_escritura.

DROP ROLE IF EXISTS 'rol_lectura_escritura', 'rol_lectura' y 'rol_escritura';
CREATE ROLE 'rol_lectura_escritura', 'rol_lectura' y 'rol_escritura';

Ahora vamos a asignar los privilegios que tendrá cada rol y sobre qué base datos tendrá estos privilegios.

GRANT ALL ON base_de_datos.* TO 'rol_lectura_escritura';
GRANT SELECT ON base_de_datos.* TO 'rol_lectura';
GRANT INSERT, UPDATE, DELETE ON base_de_datos.* TO 'rol_escritura';

Finalmente, vamos a crear varios usuarios y vamos a asignarles los roles que hemos creado:

-- Creamos los usuarios
DROP USER IF EXISTS admin;
CREATE USER admin@'localhost' IDENTIFIED BY 'password1';

DROP USER IF EXISTS usuario_lectura_1;
CREATE USER usuario_lectura_1@'localhost' IDENTIFIED BY 'password2';

DROP USER IF EXISTS usuario_lectura_2;
CREATE USER usuario_lectura_2@'localhost' IDENTIFIED BY 'password3';

DROP USER IF EXISTS usuario_escritura_1;
CREATE USER usuario_escritura_1@'localhost' IDENTIFIED BY 'password4';

DROP USER IF EXISTS usuario_escritura_2;
CREATE USER usuario_escritura_2@'localhost' IDENTIFIED BY 'password5';

-- Asignamos los roles a los usuarios
GRANT 'rol_lectura_escritura' TO admin@'localhost';
GRANT 'rol_lectura' TO usuario_lectura_1@'localhost', usuario_lectura_2@'localhost';
GRANT 'rol_escritura' TO usuario_escritura_1@'localhost', usuario_escritura_2@'localhost';

1.3.2 Eliminar un rol de un usuario

REVOKE nombre_rol FROM nombre_usuario;

1.3.3 Eliminar un rol

La sintaxis para eliminar un rol en MySQL es la siguiente:

DROP ROLE [IF NOT EXISTS] nombre_rol [, nombre_rol ] ...

Referencia:

Documentación oficial de REMOVE ROLE en MySQL

1.4 Ejercicios

Crea una base de datos llamada wordpress para la aplicación web WordPress.
Crea un usuario llamado wp_local_user que tenga todos los privilegios sobre la base de datos wordpress. Tenga en cuenta que el usuario wp_local_user sólo podrá conectarse desde la máquina local.
Crea un usuario llamado wp_remote_user que tenga todos los privilegios sobre la base de datos wordpress y que pueda conectarse desde cualquier máquina.
Crea un usuario llamado wp_read_user que sólo tenga permisos de lectura sobre la base de datos wordpress y que pueda conectarse desde cualquier máquina.
Vuelva a crear un usuario llamado wp_read_user que tenga todos los privilegios sobre la base de datos wordpress y que sí pueda conectarse desde cualquier máquina. Utilice una contraseña diferente a la que utilizó para el usuario anterior.
Quítele los privilegios de CREATE, DROP, INSERT, DELETE y UPDATE al usuario wp_read_user que puede conectarse desde cualquier máquina sobre a base de datos wordpress.
Muestre un listado de todos lo usuarios que ha creado en MySQL.
Muestre los permisos que tiene el usuario wp_read_user que puede conectarse desde cualquier máquina.
Elimine el usuario wp_read_user que puede conectarse desde cualquier máquina.

2 Licencia

Esta página forma parte del curso Bases de Datos de José Juan Sánchez Hernández y su contenido se distribuye bajo una licencia Creative Commons Reconocimiento-NoComercial-CompartirIgual 4.0 Internacional.