Apuntes de BD para DAW, DAM y ASIR
José Juan Sánchez Hernández
Curso 2023/2024
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.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:
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:
La sintaxis para eliminar un usuario en MySQL es la siguiente:
Ejemplo:
Referencia:
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:
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.% |
+------------------+--------------+
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 datosDELETE
: 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.La sintaxis simplificada para asignar permisos a un usuario en MySQL es la siguiente:
Ejemplo 1:
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.
Referencia:
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
.
La sintaxis simplificada para elminar permisos a un usuario en MySQL es la siguiente:
Referencia:
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
.
Si queremos que los cambios se apliquen inmediatamente, tendremos que ejecutar la sentencia:
También podemos consultar qué permisos específicos tiene un
determinado usuario. La siguiente consulta nos devuelve los permisos que
tiene el usuario root
:
+---------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' |
+---------------------------------------------------+
Un rol es un conjunto de privilegios que se pueden asignar a uno o más usuarios.
La sintaxis para crear un rol en MySQL es la siguiente:
Para asignar privilegios a un rol se utiliza la sentencia
GRANT
:
Para asignar un rol a un usuario también se utiliza la sentencia
GRANT
:
Referencia:
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';
La sintaxis para eliminar un rol en MySQL es la siguiente:
Referencia:
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.
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.