Tutorial SQL: Creación y manejo de usuarios y roles

Gestión de usuarios

La gestión de usuarios es un componente fundamental en la administración de bases de datos SQL. Un sistema de base de datos bien diseñado debe controlar quién tiene acceso a los datos y qué operaciones puede realizar cada usuario. Tanto MySQL como PostgreSQL ofrecen mecanismos robustos para crear, modificar y eliminar usuarios, estableciendo así la primera capa de seguridad en nuestras bases de datos.

Creación de usuarios

Para crear usuarios en nuestros sistemas de bases de datos, utilizamos la sentencia CREATE USER. La sintaxis básica varía ligeramente entre MySQL y PostgreSQL:

En MySQL:

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

El parámetro host especifica desde qué ubicación puede conectarse el usuario. Puede ser una dirección IP específica, un nombre de host o '%' para permitir conexiones desde cualquier host.

En PostgreSQL:

CREATE USER nombre_usuario WITH PASSWORD 'contraseña';

Ejemplos prácticos:

MySQL - Crear un usuario que se conecte desde cualquier host:

CREATE USER 'analista'@'%' IDENTIFIED BY 'Cl4v3_S3gur4!';

MySQL - Crear un usuario que solo pueda conectarse desde localhost:

CREATE USER 'admin_local'@'localhost' IDENTIFIED BY 'Adm1n_L0c4l!';

PostgreSQL - Crear un usuario básico:

CREATE USER reportes WITH PASSWORD 'R3p0rt3s_2023';

PostgreSQL - Crear un usuario con atributos adicionales:

CREATE USER admin_db WITH 
  PASSWORD 'Adm1n_DB!' 
  CREATEDB 
  VALID UNTIL '2024-12-31';

En este último ejemplo, estamos creando un usuario con la capacidad de crear bases de datos (CREATEDB) y estableciendo una fecha de caducidad para la cuenta.

Modificación de usuarios

Con el tiempo, es posible que necesitemos modificar los atributos o credenciales de los usuarios existentes.

En MySQL:

Para cambiar la contraseña:

ALTER USER 'nombre_usuario'@'host' IDENTIFIED BY 'nueva_contraseña';

Para renombrar un usuario:

RENAME USER 'nombre_antiguo'@'host' TO 'nombre_nuevo'@'host';

En PostgreSQL:

Para cambiar la contraseña:

ALTER USER nombre_usuario WITH PASSWORD 'nueva_contraseña';

Para modificar atributos:

ALTER USER nombre_usuario WITH CREATEDB NOCREATEROLE;

Ejemplos prácticos:

MySQL - Cambiar contraseña:

ALTER USER 'analista'@'%' IDENTIFIED BY 'Nu3v4_Cl4v3!';

MySQL - Renombrar usuario:

RENAME USER 'analista'@'%' TO 'analista_senior'@'%';

PostgreSQL - Cambiar contraseña y añadir atributos:

ALTER USER reportes WITH PASSWORD 'R3p0rt3s_2024' CREATEDB;

Eliminación de usuarios

Cuando un usuario ya no es necesario, podemos eliminarlo del sistema.

En MySQL:

DROP USER 'nombre_usuario'@'host';

En PostgreSQL:

DROP USER nombre_usuario;

Ejemplos:

MySQL:

DROP USER 'analista_temp'@'%';

PostgreSQL:

DROP USER usuario_temporal;

Bloqueo y desbloqueo de cuentas

En ocasiones, en lugar de eliminar un usuario, puede ser preferible bloquear temporalmente su acceso.

En MySQL:

Para bloquear:

ALTER USER 'nombre_usuario'@'host' ACCOUNT LOCK;

Para desbloquear:

ALTER USER 'nombre_usuario'@'host' ACCOUNT UNLOCK;

En PostgreSQL:

PostgreSQL no tiene un comando directo para bloquear/desbloquear, pero podemos usar:

-- Para "bloquear" (invalidando la contraseña)
ALTER USER nombre_usuario WITH PASSWORD NULL;

-- Para "desbloquear" (restaurando una contraseña)
ALTER USER nombre_usuario WITH PASSWORD 'nueva_contraseña';

Otra opción en PostgreSQL es establecer una fecha de expiración en el pasado:

-- Bloquear estableciendo fecha de expiración en el pasado
ALTER USER nombre_usuario VALID UNTIL '2000-01-01';

-- Desbloquear quitando la restricción de fecha
ALTER USER nombre_usuario VALID UNTIL 'infinity';

Visualización de usuarios existentes

Para administrar eficientemente los usuarios, necesitamos poder ver qué usuarios existen en el sistema.

En MySQL:

SELECT User, Host FROM mysql.user;

En PostgreSQL:

SELECT usename, usecreatedb, usesuper, valuntil 
FROM pg_catalog.pg_user;

Gestión de contraseñas y políticas de seguridad

Ambos sistemas de bases de datos permiten implementar políticas de seguridad para las contraseñas.

En MySQL 8.0+:

Podemos configurar la política de caducidad de contraseñas:

ALTER USER 'nombre_usuario'@'host' PASSWORD EXPIRE INTERVAL 90 DAY;

También podemos requerir que la próxima vez que el usuario inicie sesión, deba cambiar su contraseña:

ALTER USER 'nombre_usuario'@'host' PASSWORD EXPIRE;

En PostgreSQL:

PostgreSQL maneja esto principalmente a través de la configuración de VALID UNTIL:

ALTER USER nombre_usuario VALID UNTIL '2023-12-31';

Usuarios con privilegios administrativos

Tanto MySQL como PostgreSQL permiten crear usuarios con capacidades administrativas especiales.

En MySQL:

CREATE USER 'super_admin'@'localhost' IDENTIFIED BY 'S3cr3t0!';
GRANT ALL PRIVILEGES ON *.* TO 'super_admin'@'localhost' WITH GRANT OPTION;

En PostgreSQL:

CREATE USER super_admin WITH PASSWORD 'S3cr3t0!' SUPERUSER;

El atributo SUPERUSER en PostgreSQL otorga todos los privilegios posibles, similar a lo que hace GRANT ALL PRIVILEGES en MySQL.

Buenas prácticas en la gestión de usuarios

Al gestionar usuarios en bases de datos SQL, es importante seguir estas recomendaciones:

• Principio de mínimo privilegio: Otorga a cada usuario solo los permisos que necesita para realizar sus tareas, nada más.

• Rotación de contraseñas: Implementa políticas que requieran cambios periódicos de contraseñas.

• Contraseñas robustas: Exige contraseñas que combinen letras mayúsculas, minúsculas, números y caracteres especiales.

• Auditoría regular: Revisa periódicamente la lista de usuarios y sus privilegios para detectar cuentas innecesarias o con exceso de permisos.

• Evita usuarios compartidos: Cada persona debe tener su propio usuario para mantener la trazabilidad de las acciones.

• Documentación: Mantén un registro de los usuarios creados, su propósito y quién los autorizó.

• Usuarios específicos para aplicaciones: Crea usuarios dedicados para cada aplicación que se conecte a la base de datos.

La gestión adecuada de usuarios es el primer paso para establecer un sistema de seguridad robusto en nuestras bases de datos. En la siguiente sección, exploraremos cómo organizar estos usuarios en roles y grupos para facilitar la administración de permisos a gran escala.

Roles y grupos

Los roles y grupos en sistemas de bases de datos SQL proporcionan un mecanismo eficiente para administrar permisos a múltiples usuarios simultáneamente. En lugar de asignar permisos individualmente a cada usuario, podemos agruparlos bajo roles comunes según sus funciones o responsabilidades. Esta aproximación simplifica enormemente la administración de seguridad, especialmente en entornos con muchos usuarios.

Roles en PostgreSQL

PostgreSQL implementa un sistema de roles muy flexible que difumina la distinción entre usuarios y grupos. En PostgreSQL, un "rol" puede representar tanto a un usuario individual como a un grupo de usuarios.

Creación de roles

Para crear un rol básico en PostgreSQL:

CREATE ROLE nombre_rol;

Para crear un rol que pueda iniciar sesión (equivalente a un usuario):

CREATE ROLE nombre_rol WITH LOGIN PASSWORD 'contraseña';

También podemos crear roles con atributos específicos:

CREATE ROLE administradores 
  NOLOGIN 
  CREATEDB 
  CREATEROLE;

En este ejemplo, creamos un rol que no puede iniciar sesión directamente (NOLOGIN), pero puede crear bases de datos (CREATEDB) y otros roles (CREATEROLE).

Asignación de usuarios a roles

En PostgreSQL, asignamos usuarios a roles mediante el comando GRANT:

GRANT nombre_rol TO nombre_usuario;

Por ejemplo, para añadir un usuario al rol de administradores:

GRANT administradores TO ana_admin;

Un usuario puede pertenecer a múltiples roles:

GRANT administradores TO carlos_tech;
GRANT desarrolladores TO carlos_tech;

Herencia de roles

Una característica potente de PostgreSQL es la herencia de roles. Por defecto, un usuario hereda todos los privilegios de los roles a los que pertenece:

-- Crear un rol para desarrolladores
CREATE ROLE desarrolladores NOLOGIN;

-- Crear un rol para desarrolladores senior que hereda de desarrolladores
CREATE ROLE desarrolladores_senior NOLOGIN;
GRANT desarrolladores TO desarrolladores_senior;

-- Asignar un usuario al rol senior
GRANT desarrolladores_senior TO maria_dev;

En este ejemplo, maria_dev heredará automáticamente todos los privilegios asignados tanto al rol desarrolladores_senior como al rol desarrolladores.

Si queremos crear un rol sin herencia automática:

CREATE ROLE auditores NOLOGIN NOINHERIT;

Los usuarios asignados a este rol necesitarán activarlo explícitamente con SET ROLE para usar sus privilegios.

Activación de roles

Un usuario puede cambiar temporalmente al contexto de un rol al que pertenece:

SET ROLE nombre_rol;

Para volver a su rol original:

RESET ROLE;

Grupos en MySQL

MySQL maneja los grupos de manera diferente a PostgreSQL. En MySQL, utilizamos el concepto de roles a partir de la versión 8.0.

Creación de roles en MySQL

CREATE ROLE 'nombre_rol';

Por ejemplo:

CREATE ROLE 'app_desarrolladores';
CREATE ROLE 'app_lectores';
CREATE ROLE 'app_administradores';

Asignación de privilegios a roles

Una vez creado el rol, podemos asignarle privilegios:

GRANT SELECT, INSERT, UPDATE ON aplicacion.* TO 'app_desarrolladores';
GRANT SELECT ON aplicacion.* TO 'app_lectores';
GRANT ALL ON aplicacion.* TO 'app_administradores';

Asignación de roles a usuarios

Para asignar un rol a un usuario:

GRANT 'nombre_rol' TO 'nombre_usuario'@'host';

Por ejemplo:

GRANT 'app_desarrolladores' TO 'dev_juan'@'%';
GRANT 'app_lectores' TO 'analista_maria'@'%';
GRANT 'app_administradores' TO 'admin_pedro'@'localhost';

Activación de roles

A diferencia de PostgreSQL, en MySQL los roles asignados no se activan automáticamente. El usuario debe activarlos explícitamente:

SET ROLE 'nombre_rol';

O activar todos los roles asignados:

SET ROLE ALL;

Para configurar que los roles se activen automáticamente al iniciar sesión:

SET DEFAULT ROLE ALL TO 'nombre_usuario'@'host';

Por ejemplo:

SET DEFAULT ROLE 'app_desarrolladores' TO 'dev_juan'@'%';

También podemos establecer múltiples roles predeterminados:

SET DEFAULT ROLE 'app_desarrolladores', 'app_lectores' TO 'dev_senior'@'%';

Jerarquía de roles

Tanto en PostgreSQL como en MySQL podemos crear jerarquías de roles, aunque con enfoques ligeramente diferentes.

En PostgreSQL, usamos la herencia directa:

-- Crear roles base
CREATE ROLE empleados NOLOGIN;
CREATE ROLE tecnicos NOLOGIN;

-- Crear jerarquía
CREATE ROLE desarrolladores NOLOGIN;
GRANT empleados TO desarrolladores;
GRANT tecnicos TO desarrolladores;

-- Asignar usuarios
GRANT desarrolladores TO 'dev_ana';

En MySQL, podemos simular jerarquías asignando roles a otros roles:

CREATE ROLE 'empleados';
CREATE ROLE 'tecnicos';
CREATE ROLE 'desarrolladores';

GRANT 'empleados' TO 'desarrolladores';
GRANT 'tecnicos' TO 'desarrolladores';

GRANT 'desarrolladores' TO 'dev_ana'@'%';

Gestión de roles existentes

Para ver los roles disponibles:

En PostgreSQL:

SELECT rolname, rolsuper, rolinherit 
FROM pg_roles;

En MySQL:

SELECT * FROM mysql.role;

Para ver los roles asignados a un usuario:

En PostgreSQL:

SELECT r.rolname, u.rolname AS username
FROM pg_roles r
JOIN pg_auth_members m ON m.roleid = r.oid
JOIN pg_roles u ON m.member = u.oid;

En MySQL:

SELECT * FROM mysql.role_edges;

Revocación de roles

Para revocar un rol de un usuario:

En PostgreSQL:

REVOKE nombre_rol FROM nombre_usuario;

En MySQL:

REVOKE 'nombre_rol' FROM 'nombre_usuario'@'host';

Eliminación de roles

Cuando un rol ya no es necesario:

En PostgreSQL:

DROP ROLE nombre_rol;

Si el rol tiene dependencias, primero debemos reasignarlas:

REASSIGN OWNED BY nombre_rol TO otro_rol;
DROP OWNED BY nombre_rol;
DROP ROLE nombre_rol;

En MySQL:

DROP ROLE 'nombre_rol';

Estrategias efectivas para roles y grupos

Al implementar roles y grupos en entornos de producción, considera estas estrategias:

• Roles basados en funciones: Crea roles que reflejen las funciones laborales reales (analistas, desarrolladores, administradores).

• Roles basados en niveles de acceso: Implementa roles como "solo lectura", "lectura/escritura" y "administrador".

• Roles por aplicación o módulo: Define roles específicos para diferentes aplicaciones o módulos del sistema.

• Minimiza la asignación directa de privilegios: Asigna privilegios a roles, no directamente a usuarios.

• Documenta la estructura de roles: Mantén un diagrama o documento que muestre la jerarquía de roles y sus privilegios.

• Auditoría regular: Revisa periódicamente la estructura de roles para eliminar roles obsoletos o ajustar privilegios.

• Separación de responsabilidades: Implementa roles que respeten el principio de separación de responsabilidades (por ejemplo, quien aprueba no debería ser quien ejecuta).

Ejemplo práctico: Implementación de roles para una aplicación de comercio electrónico

Veamos cómo implementar una estructura de roles para una aplicación de comercio electrónico:

En PostgreSQL:

-- Roles base por nivel de acceso
CREATE ROLE app_readonly NOLOGIN;
CREATE ROLE app_readwrite NOLOGIN;
CREATE ROLE app_admin NOLOGIN;

-- Roles por área funcional
CREATE ROLE ventas NOLOGIN;
CREATE ROLE inventario NOLOGIN;
CREATE ROLE marketing NOLOGIN;
CREATE ROLE finanzas NOLOGIN;

-- Combinaciones funcionales
CREATE ROLE ventas_readonly NOLOGIN;
GRANT app_readonly TO ventas_readonly;
GRANT ventas TO ventas_readonly;

CREATE ROLE inventario_readwrite NOLOGIN;
GRANT app_readwrite TO inventario_readwrite;
GRANT inventario TO inventario_readwrite;

-- Asignar a usuarios
CREATE ROLE ana_ventas WITH LOGIN PASSWORD 'segura123';
GRANT ventas_readonly TO ana_ventas;

CREATE ROLE juan_inventario WITH LOGIN PASSWORD 'segura456';
GRANT inventario_readwrite TO juan_inventario;

En MySQL:

-- Roles base por nivel de acceso
CREATE ROLE 'app_readonly';
CREATE ROLE 'app_readwrite';
CREATE ROLE 'app_admin';

-- Roles por área funcional
CREATE ROLE 'ventas';
CREATE ROLE 'inventario';
CREATE ROLE 'marketing';
CREATE ROLE 'finanzas';

-- Asignar privilegios a roles base
GRANT SELECT ON ecommerce.* TO 'app_readonly';
GRANT SELECT, INSERT, UPDATE ON ecommerce.* TO 'app_readwrite';
GRANT ALL ON ecommerce.* TO 'app_admin';

-- Asignar privilegios específicos a áreas
GRANT SELECT ON ecommerce.clientes TO 'ventas';
GRANT SELECT, UPDATE ON ecommerce.productos TO 'inventario';

-- Asignar roles a usuarios
CREATE USER 'ana_ventas'@'%' IDENTIFIED BY 'segura123';
GRANT 'app_readonly', 'ventas' TO 'ana_ventas'@'%';
SET DEFAULT ROLE 'app_readonly', 'ventas' TO 'ana_ventas'@'%';

CREATE USER 'juan_inventario'@'%' IDENTIFIED BY 'segura456';
GRANT 'app_readwrite', 'inventario' TO 'juan_inventario'@'%';
SET DEFAULT ROLE ALL TO 'juan_inventario'@'%';

La implementación adecuada de roles y grupos proporciona una capa de abstracción que simplifica enormemente la gestión de permisos en bases de datos con múltiples usuarios. Al estructurar correctamente los roles, podemos mantener un control granular sobre los permisos mientras reducimos significativamente la complejidad administrativa.