Roles, Permisos y Usuarios en MariaDB/MySQL

Securizar los accesos a las bases de datos

Cuando trabajamos con bases de datos es muy importante el asignar los permisos más restringidos posibles para evitar cualquier tipo de problema o incidencia que pueda provocar un usuario al tener permisos de más sobre los esquemas de las bases de datos.

Vamos a enumerar unas pequeñas recomendaciones para la creación de las bases de datos y aplicar sobre sobre ellas diferentes tipos de permitimos que usaremos mediante unos roles. Estos roles serán los que se apliquen a los usuarios para restringir según los tipos de acceso que permitamos.

Creación del Esquema de Base de datos

Crearemos la base de datos, normalmente y después sobre ella crearemos los roles necesarios con los permisos (GRANT) que queremos asignar.

Suponiendo que la vamos a crear una nueva base de datos y la vamos a llamar bbdd escribiremos lo siguiente:

CREATE DATABASE bbdd;

Tipos de Roles

Para facilitar las separación de permisos, simplificaremos los roles en tres tipos, los roles serán solo aplicados a un esquema/base de datos y no serán de aplicación general sobre otras bases de datos.

• admin_role: Usaremos este rol para asignar permisos y permitir realizar  sobre su propio esquema/bbdd cualquier acción: lectura, escritura y incluso cambios sobre la estructura de las tablas de su propia base de datos tales como  borrados de tablas o del propio esquema.
• write_role: Este rol otorga permisos para escribir, modificar o borrar datos en las tablas, pero sólo sobre su propio esquema/base de datos.
• read_role: Unicamente se permite consultar los datos de las tablas de su esquema/base de datos.

El procedimiento  será crear un rol de cada tipo por cada esquema/base de datos que tengamos en MariaDB/MySQL.

Suele ser conveniente identificar el rol de ese esquema mediante el nombre del prefijo del esquema/base de datos seguido por el tipo de rol:

# Suponiendo que el esquema se llama 'bbdd'
# Creamos los Roles:

CREATE ROLE 'bbdd_admin_role'@'%';
CREATE ROLE 'bbdd_write_role'@'%';
CREATE ROLE 'bbdd_read_role'@'%';

Aplicando Permisos a los Roles

Con los nombre de los roles creados con una sintaxis que nos permite identificar lo que pueden hacer cada uno de ellos, ahora aplicaremos los GRANT o permisos a cada rol.

Para que el funcionamiento sea correcto a todos los roles les aplicaremos un permiso para poder usar MariaDB/MySQL:

# Permitimos USAGE a todos los roles creados:
GRANT USAGE ON *.* TO nombre_rol;

Después aplicaremos la restricción particular necesaria a cada rol del siguiente modo:

# Permisos para el Rol ADMIN:
GRANT ALL PRIVILEGES ON 'bbdd'.* TO 'bbdd_admin_role'@'%';

# Permisos para el Rol WRITE:
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES ON 'bbdd'.* TO ' bbdd_write_role'@'%';

# Permisos para el Rol READ:
GRANT SELECT, SHOW VIEW ON 'bbdd'.* TO ' bbdd_read_role'@'%';

Creación de los Usuarios

A la hora de crear los usuarios, debemos identificar de forma clara, que usuario tiene acceso a cada esquema de la base de datos y además que tipo de permisos tiene.
Para ello lo mejor es seguir una nomenclatura sencilla como por ejemplo:
usuario  + bbdd + rol-asignado

Siguiendo con el ejemplo de nuestro esquema de base de datos bbdd, y los roles creados anteriormente, podemos crear los usuarios, fijarles una clave y asignarles un rol y esos permisos asociado en una única instrucción:

# Usuario ADMIN del esquema (con rol ADMIN)
CREATE USER 'usu_bbdd_admin'@'%' IDENTIFIED BY 'clave' DEFAULT ROLE 'bbdd_admin_role'@'%';

# Usuario WRITE del esquema (con rol WRITE)
CREATE USER 'usu_bbdd_write'@'%' IDENTIFIED BY 'clave' DEFAULT ROLE 'bbdd_write_role'@'%';

# Usuario READ del esquema (con rol READ)
CREATE USER 'usu_bbdd_read'@'%' IDENTIFIED BY 'clave' DEFAULT ROLE 'bbdd_read_role'@'%';

¿A quien dejamos usar cada usuario?

El usuario admin lo pueden usar los desarrolladores en los entornos que NO sean Producción, para que pueden trabajar con la base de datos y modificar la estructura de las tablas en el proceso de desarrollo.
En Producción no se debe permitir su uso salvo, por el SysAdmin de  MariaDB/MySQL.

El usuario write, lo usará únicamente la aplicación para escribir datos, ya no debe de poder cambiar nada del formato de datos de las tablas.

El usuario read, se puede entregar a los grupos de personas y/o aplicaciones que únicamente necesiten leer datos de ese esquema de base de datos.

Aplicar Roles a Usuarios ya existentes

Si los usuarios ya existiesen y quisiéramos asignarles roles debemos realizarlo del siguiente modo:

1. Asignar ROL:
   GRANT nombre-rol TO usuario;
2. Permitir que los usuarios puedan usar a partir de ese momento y por defecto los Roles que les hemos asignado:
   SET DEFAULT ROLE ALL TO 'usuario1', 'usuario2', 'usuario3', 'usuarioN';

Revisar Roles y Asignación de ellos a los Usuarios

Para poder ver los roles existentes debemos ejecutar lo siguiente:

• MariaDB: SELECT * FROM mysql.user WHERE is_role='y';
• MySQL: SELECT * FROM mysql.role_edges;

Para ver los permisos de los roles: SHOW GRANTS FOR 'nombre-rol';

En el caso anterior de nuestro esquema bbdd escribiríamos:

SHOW GRANTS FOR 'bbdd_admin_role'@'%';
SHOW GRANTS FOR 'bbdd_write_role'@'%';
SHOW GRANTS FOR 'bbdd_read_role'@'%';

Para poder ver que rol tiene asignado cada usuario: ejecutaríamos:
SHOW GRANTS FOR 'usuario';

En nuestro caso deberíamos ejecutar:

SHOW GRANTS FOR 'usu_bbdd_admin'@'%';
SHOW GRANTS FOR 'usu_bbdd_write'@'%';
SHOW GRANTS FOR 'usu_bbdd_read'@'%';

Eliminación de Roles

Si necesitamos eliminar algún rol usaremos la sintaxis:
DROP ROLE IF EXISTS nombre_rol;

Hay que tener en cuenta que Los usuarios que tuviesen ese Rol asignado se quedan sin ningún tipo de permisos en todo MariaDB/MySQL y no podrán realizar ningún tipo de operación en la base de datos.

Filtrando por IPs y Redes

En todos los ejemplos anteriores hemos usado 'nombre-rol'@'%' ó 'usuario'@'%'.
El  % que hemos usado hasta ahora en todos los ejemplos es el símbolo del comodín para su uso en en MariaDB/MySQL, con lo que se admiten conexiones desde cualquier IP.

Para proteger correctamente las conexiones, debemos filtrar con el mejor ajuste posible, desde donde vamos a permitir que se conecten a nuestro gestor de bases de datos.
Para ello, en los ejemplos anteriores, sustituye el símbolo de porcentaje por dirección o direcciones de los equipos desde los que quieras permitir que se conecten:

• % Conexión desde cualquier IP
• localhost ó 127.0.0.1 Conexión unicamente desde el propio equipo en que se ejecuta el servicio de MariaDB/MySQL
• IP ó equipo Permitir la conexión desde la IP indicada o el nombre del equipo (debe ser resuelto mediante DNS o incluido en el /etc/hosts)
• 224.15.30.%1 Comodines en IPs. Valen para restringir el acceso todos los equipos que cumplan la condición. En este ejemplo los que su IP comience por 224.15.30. y termine en .1
• 172.85.34.0/25 ó 172.85.34.0/255.255.255.128 Se puede también definir como un rango de IPs ya sea mediante un CIDR o una mascara de red
• Formatos IPv6. También si tienes en uso IPv6, puedes usar este formato para filtrar los accesos a la base de datos.