Tutorial SQL: Asignación y gestión de permisos

Niveles de privilegios

En los sistemas de gestión de bases de datos como MySQL y PostgreSQL, los privilegios representan permisos específicos que determinan qué acciones pueden realizar los usuarios sobre los objetos de la base de datos. Estos privilegios están organizados en diferentes niveles jerárquicos, lo que permite implementar un control de acceso granular y aplicar el principio de mínimo privilegio.

Jerarquía de privilegios

Los privilegios en SQL se estructuran en varios niveles, desde los más amplios hasta los más específicos:

• Nivel global: Afectan a todas las bases de datos del servidor
• Nivel de base de datos: Se aplican a todos los objetos dentro de una base de datos específica
• Nivel de tabla: Controlan el acceso a tablas individuales
• Nivel de columna: Permiten o restringen el acceso a columnas específicas dentro de una tabla
• Nivel de rutina: Aplican a procedimientos almacenados y funciones

Esta estructura jerárquica permite implementar políticas de seguridad detalladas, otorgando a los usuarios exactamente los permisos que necesitan para realizar sus tareas, sin excesos.

Privilegios a nivel global

Los privilegios globales afectan a todas las bases de datos en el servidor. Estos son algunos de los más importantes:

• ALL PRIVILEGES: Otorga todos los privilegios disponibles (excepto GRANT OPTION)
• CREATE USER: Permite crear, modificar y eliminar usuarios
• SHUTDOWN: Permite detener el servidor de base de datos
• SUPER: Otorga privilegios administrativos avanzados (en MySQL)
• REPLICATION CLIENT/SLAVE: Controla la replicación de datos

En MySQL, estos privilegios se almacenan en la tabla mysql.user. Ejemplo de asignación:

-- Otorgar privilegios globales a un administrador
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';

Privilegios a nivel de base de datos

Estos privilegios se aplican a todos los objetos dentro de una base de datos específica:

• CREATE: Permite crear nuevas tablas o bases de datos
• DROP: Permite eliminar tablas o bases de datos
• EVENT: Permite crear, modificar y eliminar eventos
• LOCK TABLES: Permite bloquear tablas para las que el usuario tiene privilegio SELECT

Ejemplo de asignación en PostgreSQL:

-- Otorgar privilegios a nivel de base de datos
GRANT CREATE, CONNECT ON DATABASE inventario TO 'analista';

Privilegios a nivel de tabla

Controlan las operaciones que pueden realizarse sobre tablas específicas:

• SELECT: Permite leer datos de una tabla
• INSERT: Permite añadir nuevos registros
• UPDATE: Permite modificar registros existentes
• DELETE: Permite eliminar registros
• TRUNCATE: Permite vaciar completamente una tabla
• REFERENCES: Permite crear claves foráneas que referencian a la tabla
• ALTER: Permite modificar la estructura de la tabla

Ejemplo en MySQL:

-- Otorgar privilegios de lectura y escritura en una tabla específica
GRANT SELECT, INSERT, UPDATE ON tienda.productos TO 'vendedor'@'localhost';

Privilegios a nivel de columna

Permiten un control aún más granular, restringiendo el acceso a columnas específicas dentro de una tabla:

• SELECT: Permite leer datos de columnas específicas
• INSERT: Permite insertar datos en columnas específicas
• UPDATE: Permite modificar datos en columnas específicas

Ejemplo en MySQL:

-- Permitir actualizar solo ciertas columnas
GRANT UPDATE (precio, stock) ON tienda.productos TO 'inventario'@'localhost';

En PostgreSQL, la sintaxis es ligeramente diferente:

-- Permitir actualizar solo ciertas columnas en PostgreSQL
GRANT UPDATE (precio, stock) ON productos TO inventario;

Privilegios a nivel de rutina

Estos privilegios controlan la ejecución y gestión de procedimientos almacenados y funciones:

• EXECUTE: Permite ejecutar procedimientos almacenados y funciones
• ALTER ROUTINE: Permite modificar o eliminar rutinas
• CREATE ROUTINE: Permite crear nuevas rutinas

Ejemplo en MySQL:

-- Otorgar permiso para ejecutar un procedimiento específico
GRANT EXECUTE ON PROCEDURE ventas.calcular_total TO 'contador'@'localhost';

Privilegios especiales

Existen algunos privilegios especiales que merecen atención particular:

• GRANT OPTION: Permite al usuario otorgar sus propios privilegios a otros usuarios
• USAGE: Es el privilegio mínimo, no otorga permisos específicos pero permite conectarse
• WITH ADMIN OPTION: En PostgreSQL, similar a GRANT OPTION pero para roles

Ejemplo del uso de GRANT OPTION:

-- Otorgar privilegios con la capacidad de transferirlos a otros
GRANT SELECT ON tienda.* TO 'gerente'@'localhost' WITH GRANT OPTION;

Implementación del principio de mínimo privilegio

El principio de mínimo privilegio es una práctica fundamental de seguridad que consiste en otorgar a los usuarios únicamente los permisos necesarios para realizar sus tareas específicas. Para implementarlo correctamente:

• Analizar las necesidades reales de cada rol o usuario
• Evitar otorgar privilegios ALL PRIVILEGES excepto cuando sea absolutamente necesario
• Preferir privilegios a nivel de tabla o columna sobre privilegios a nivel de base de datos
• Revisar periódicamente los privilegios asignados
• Utilizar roles para agrupar privilegios relacionados

Ejemplo de implementación en PostgreSQL:

-- Crear un rol con privilegios específicos
CREATE ROLE analistas;
GRANT CONNECT ON DATABASE empresa TO analistas;
GRANT SELECT ON ventas, clientes TO analistas;
GRANT SELECT (nombre, email) ON empleados TO analistas;

-- Asignar el rol a usuarios
GRANT analistas TO maria, juan;

Diferencias entre MySQL y PostgreSQL

Aunque los conceptos fundamentales son similares, existen algunas diferencias importantes:

• Roles: PostgreSQL tiene un sistema de roles más avanzado que MySQL
• Sintaxis: Existen diferencias en la sintaxis de los comandos GRANT
• Privilegios disponibles: Algunos privilegios son específicos de cada sistema

En PostgreSQL, los roles son centrales para la gestión de permisos:

-- Gestión de roles en PostgreSQL
CREATE ROLE lectura_ventas;
GRANT SELECT ON ALL TABLES IN SCHEMA ventas TO lectura_ventas;
GRANT lectura_ventas TO usuario1, usuario2;

En MySQL, aunque existe el concepto de roles desde la versión 8.0, tradicionalmente se ha trabajado más con usuarios y privilegios directos:

-- Enfoque tradicional en MySQL
CREATE USER 'usuario1'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT ON ventas.* TO 'usuario1'@'localhost';

La comprensión de estos diferentes niveles de privilegios es fundamental para implementar una estrategia de seguridad efectiva en bases de datos SQL, permitiendo un control preciso sobre quién puede acceder a qué datos y qué operaciones puede realizar con ellos.

Comando GRANT

El comando GRANT es una instrucción fundamental en SQL que permite asignar privilegios específicos a usuarios o roles dentro del sistema de gestión de bases de datos. Este comando es esencial para implementar políticas de seguridad efectivas y controlar el acceso a los datos de manera precisa.

La sintaxis básica del comando GRANT varía ligeramente entre MySQL y PostgreSQL, pero mantiene una estructura conceptual similar:

GRANT privilegio(s) ON objeto TO usuario/rol [WITH opción];

Sintaxis detallada en MySQL

En MySQL, el comando GRANT presenta la siguiente estructura:

GRANT tipo_privilegio [(columnas)]
ON [objeto_tipo] objeto
TO usuario [IDENTIFIED BY 'contraseña']
[WITH opción];

Donde:

• tipo_privilegio: Especifica qué acciones puede realizar el usuario (SELECT, INSERT, etc.)
• columnas: Opcional, limita el privilegio a columnas específicas
• objeto_tipo: Puede ser TABLE, FUNCTION, PROCEDURE, etc.
• objeto: Identifica la base de datos, tabla o rutina específica
• usuario: El usuario al que se otorgan los privilegios
• opción: Generalmente GRANT OPTION, que permite al usuario transferir sus privilegios

Sintaxis detallada en PostgreSQL

PostgreSQL utiliza una sintaxis similar con algunas diferencias:

GRANT privilegio(s)
ON objeto_tipo objeto
TO rol/usuario
[WITH GRANT OPTION];

PostgreSQL utiliza el concepto de roles de manera más central que MySQL, y la opción WITH ADMIN OPTION cuando se conceden roles a otros roles.

Ejemplos prácticos del comando GRANT

Otorgar privilegios básicos sobre tablas

Para dar permisos de lectura a un usuario sobre una tabla específica:

-- En MySQL
GRANT SELECT ON tienda.productos TO 'analista'@'localhost';

-- En PostgreSQL
GRANT SELECT ON productos TO analista;

Para otorgar múltiples privilegios en una sola sentencia:

-- Permisos de lectura y modificación
GRANT SELECT, UPDATE, INSERT ON inventario.stock TO 'gestor_inventario'@'localhost';

Privilegios sobre columnas específicas

El comando GRANT permite restringir los privilegios a nivel de columnas individuales:

-- Permitir actualizar solo el precio y stock, pero no otras columnas
GRANT UPDATE (precio, stock) ON tienda.productos TO 'asistente'@'localhost';

-- Permitir ver solo ciertas columnas de información de clientes
GRANT SELECT (nombre, email, telefono) ON clientes TO 'servicio_cliente';

Este nivel de granularidad es especialmente útil para proteger datos sensibles como información personal o financiera.

Privilegios sobre múltiples objetos

Se pueden otorgar privilegios sobre todas las tablas de una base de datos:

-- En MySQL
GRANT SELECT ON tienda.* TO 'reportes'@'localhost';

-- En PostgreSQL
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reportes;

PostgreSQL ofrece opciones adicionales para aplicar privilegios a grupos de objetos:

-- Aplicar a todas las secuencias en un esquema
GRANT USAGE ON ALL SEQUENCES IN SCHEMA ventas TO aplicacion;

-- Aplicar a todas las funciones en un esquema
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA utilidades TO desarrollador;

Uso de comodines y patrones

MySQL permite utilizar comodines en los nombres de bases de datos y tablas:

-- Otorgar acceso a todas las bases de datos que comienzan con 'test_'
GRANT SELECT ON `test\_%`.* TO 'tester'@'localhost';

Otorgar privilegios con GRANT OPTION

Para permitir que un usuario pueda a su vez otorgar sus privilegios a otros:

-- En MySQL
GRANT SELECT ON ventas.* TO 'gerente'@'localhost' WITH GRANT OPTION;

-- En PostgreSQL
GRANT SELECT ON ALL TABLES IN SCHEMA ventas TO gerente WITH GRANT OPTION;

Este privilegio debe usarse con precaución, ya que permite la propagación de permisos.

Privilegios para procedimientos y funciones

Para otorgar permisos de ejecución sobre rutinas almacenadas:

-- En MySQL
GRANT EXECUTE ON PROCEDURE ventas.calcular_comision TO 'vendedor'@'localhost';

-- En PostgreSQL
GRANT EXECUTE ON FUNCTION calcular_comision(numeric) TO vendedor;

Privilegios para roles en PostgreSQL

PostgreSQL implementa un sistema de roles más avanzado:

-- Crear un rol y asignar privilegios al rol
CREATE ROLE analistas;
GRANT CONNECT ON DATABASE empresa TO analistas;
GRANT SELECT ON ALL TABLES IN SCHEMA estadisticas TO analistas;

-- Asignar usuarios a ese rol
GRANT analistas TO maria, juan, pedro;

Privilegios para roles en MySQL (versión 8.0+)

MySQL incorporó el soporte para roles a partir de la versión 8.0:

-- Crear un rol
CREATE ROLE 'app_read';

-- Asignar privilegios al rol
GRANT SELECT ON aplicacion.* TO 'app_read';

-- Asignar el rol a usuarios
GRANT 'app_read' TO 'usuario1'@'localhost', 'usuario2'@'localhost';

-- Activar el rol para la sesión actual
SET ROLE 'app_read';

Privilegios dinámicos

MySQL 8.0 introdujo privilegios dinámicos que permiten un control más fino sobre operaciones administrativas:

-- Otorgar privilegio para crear usuarios pero no otros privilegios administrativos
GRANT CREATE USER ON *.* TO 'admin_usuarios'@'localhost';

-- Privilegio para gestionar roles
GRANT ROLE_ADMIN ON *.* TO 'admin_roles'@'localhost';

Consideraciones de seguridad al usar GRANT

Al utilizar el comando GRANT, es importante seguir estas prácticas recomendadas:

• Especificidad: Otorgar solo los privilegios necesarios para cada tarea
• Revisión periódica: Auditar regularmente los privilegios concedidos
• Uso de roles: Agrupar privilegios en roles para facilitar la gestión
• Evitar privilegios globales: Limitar el uso de comodines como *.*
• Documentación: Mantener documentación actualizada de la política de permisos

-- Ejemplo de privilegio demasiado amplio (evitar)
GRANT ALL PRIVILEGES ON *.* TO 'usuario'@'localhost';

-- Mejor enfoque: privilegios específicos
GRANT SELECT, INSERT, UPDATE ON aplicacion.clientes TO 'servicio_cliente'@'localhost';
GRANT SELECT ON aplicacion.productos TO 'servicio_cliente'@'localhost';

Verificación de privilegios concedidos

Para revisar qué privilegios se han otorgado, se pueden utilizar las siguientes consultas:

En MySQL:

-- Ver privilegios de un usuario específico
SHOW GRANTS FOR 'usuario'@'localhost';

-- Consultar la tabla de privilegios
SELECT * FROM information_schema.user_privileges 
WHERE grantee LIKE "'usuario'%";

En PostgreSQL:

-- Ver privilegios sobre tablas
SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.table_privileges
WHERE grantee = 'usuario';

-- Ver roles asignados
SELECT r.rolname, m.member, m.grantor, m.admin_option
FROM pg_auth_members m
JOIN pg_roles r ON m.roleid = r.oid
JOIN pg_roles m2 ON m.member = m2.oid;

El comando GRANT es una herramienta esencial para implementar una estrategia de seguridad efectiva en bases de datos SQL. Su uso adecuado permite establecer un control de acceso preciso y aplicar el principio de mínimo privilegio, garantizando que cada usuario tenga exactamente los permisos necesarios para realizar sus tareas, ni más ni menos.

Comando REVOKE

El comando REVOKE es el complemento natural del comando GRANT en los sistemas de gestión de bases de datos SQL. Mientras GRANT otorga privilegios, REVOKE los elimina, permitiendo revocar permisos previamente concedidos a usuarios o roles. Esta capacidad es fundamental para mantener un control de acceso dinámico y adaptable a las necesidades cambiantes de seguridad.

La sintaxis básica del comando REVOKE sigue una estructura similar en MySQL y PostgreSQL:

REVOKE privilegio(s) ON objeto FROM usuario/rol;

Sintaxis detallada en MySQL

En MySQL, el comando REVOKE presenta la siguiente estructura:

REVOKE tipo_privilegio [(columnas)]
ON [objeto_tipo] objeto
FROM usuario;

Donde los parámetros son equivalentes a los del comando GRANT, pero con la dirección inversa (FROM en lugar de TO).

Sintaxis detallada en PostgreSQL

En PostgreSQL, la sintaxis es similar:

REVOKE [GRANT OPTION FOR] privilegio(s)
ON objeto_tipo objeto
FROM rol/usuario [CASCADE | RESTRICT];

PostgreSQL añade opciones adicionales como CASCADE (que revoca también los privilegios dependientes) y RESTRICT (que falla si existen privilegios dependientes).

Ejemplos prácticos del comando REVOKE

Revocar privilegios básicos

Para eliminar permisos de lectura a un usuario sobre una tabla específica:

-- En MySQL
REVOKE SELECT ON tienda.productos FROM 'analista'@'localhost';

-- En PostgreSQL
REVOKE SELECT ON productos FROM analista;

Para revocar múltiples privilegios en una sola sentencia:

-- Revocar permisos de modificación pero mantener lectura
REVOKE UPDATE, INSERT, DELETE ON inventario.stock FROM 'gestor_inventario'@'localhost';

Revocar privilegios sobre columnas específicas

Al igual que con GRANT, se pueden revocar privilegios a nivel de columnas individuales:

-- Revocar permiso para actualizar columnas específicas
REVOKE UPDATE (precio, descuento) ON tienda.productos FROM 'asistente'@'localhost';

Revocar privilegios sobre múltiples objetos

Se pueden revocar privilegios sobre todas las tablas de una base de datos:

-- En MySQL
REVOKE SELECT ON tienda.* FROM 'reportes'@'localhost';

-- En PostgreSQL
REVOKE SELECT ON ALL TABLES IN SCHEMA public FROM reportes;

PostgreSQL ofrece opciones para revocar privilegios a grupos de objetos:

-- Revocar de todas las secuencias en un esquema
REVOKE USAGE ON ALL SEQUENCES IN SCHEMA ventas FROM aplicacion;

-- Revocar de todas las funciones en un esquema
REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA utilidades FROM desarrollador;

Revocar la opción GRANT OPTION

Es posible revocar solo la capacidad de transferir privilegios sin eliminar los privilegios en sí:

-- En MySQL
REVOKE GRANT OPTION ON ventas.* FROM 'gerente'@'localhost';

-- En PostgreSQL
REVOKE GRANT OPTION FOR SELECT ON ALL TABLES IN SCHEMA ventas FROM gerente;

Esto es útil cuando queremos que un usuario mantenga sus privilegios pero no pueda otorgarlos a otros.

Revocar todos los privilegios

Para eliminar todos los privilegios de un usuario sobre un objeto específico:

-- En MySQL
REVOKE ALL PRIVILEGES ON tienda.productos FROM 'usuario'@'localhost';

-- En PostgreSQL
REVOKE ALL PRIVILEGES ON productos FROM usuario;

Para revocar todos los privilegios globales en MySQL:

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'usuario'@'localhost';

Comportamiento en cascada

En PostgreSQL, la opción CASCADE permite revocar privilegios en cascada:

-- Revocar con cascada
REVOKE SELECT ON clientes FROM gerente CASCADE;

Esto revocará no solo los privilegios del gerente, sino también los privilegios que el gerente haya otorgado a otros usuarios utilizando su GRANT OPTION.

La opción RESTRICT (predeterminada) evita la revocación si existen privilegios dependientes:

-- Revocar solo si no hay dependencias
REVOKE SELECT ON clientes FROM gerente RESTRICT;

Revocar roles

En PostgreSQL, se pueden revocar roles asignados a usuarios:

-- Revocar un rol de un usuario
REVOKE analistas FROM maria;

En MySQL 8.0+:

-- Revocar un rol de un usuario
REVOKE 'app_read' FROM 'usuario1'@'localhost';

Revocar privilegios dinámicos en MySQL

Para los privilegios dinámicos introducidos en MySQL 8.0:

-- Revocar privilegio administrativo específico
REVOKE CREATE USER ON *.* FROM 'admin_usuarios'@'localhost';

Estrategias efectivas para usar REVOKE

El uso adecuado del comando REVOKE es crucial para mantener un entorno de base de datos seguro:

• Auditoría y limpieza periódica: Revisar y revocar privilegios innecesarios regularmente

-- Ejemplo de script de auditoría en MySQL
SELECT user, host, privilege_type, is_grantable 
FROM information_schema.user_privileges 
WHERE user NOT IN ('mysql.sys', 'root') 
ORDER BY user, privilege_type;

• Cambios de rol: Revocar privilegios cuando un usuario cambia de responsabilidades

-- Revocar privilegios antiguos al cambiar de departamento
REVOKE ALL PRIVILEGES ON ventas.* FROM 'usuario'@'localhost';
GRANT SELECT, INSERT, UPDATE ON marketing.* TO 'usuario'@'localhost';

• Respuesta a incidentes: Revocar temporalmente privilegios durante investigaciones de seguridad

-- Respuesta rápida a un posible compromiso
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'cuenta_sospechosa'@'%';

• Gestión de acceso temporal: Otorgar y luego revocar privilegios para tareas específicas

-- Acceso temporal para mantenimiento
GRANT CREATE, ALTER, DROP ON tienda.* TO 'mantenimiento'@'localhost';
-- Después de completar la tarea
REVOKE CREATE, ALTER, DROP ON tienda.* FROM 'mantenimiento'@'localhost';

Verificación de privilegios revocados

Para confirmar que los privilegios se han revocado correctamente:

En MySQL:

-- Verificar privilegios actuales
SHOW GRANTS FOR 'usuario'@'localhost';

En PostgreSQL:

-- Verificar privilegios sobre tablas
SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.table_privileges
WHERE grantee = 'usuario';

Diferencias clave entre MySQL y PostgreSQL

Existen algunas diferencias importantes en el comportamiento de REVOKE entre estos sistemas:

• Comportamiento en cascada: PostgreSQL ofrece opciones CASCADE/RESTRICT explícitas
• Revocación implícita: En MySQL, revocar un privilegio de nivel superior puede afectar implícitamente a privilegios de nivel inferior
• Sintaxis de roles: PostgreSQL tiene una gestión de roles más avanzada

Escenarios comunes de uso de REVOKE

• Desactivación de cuentas: Revocar todos los privilegios sin eliminar la cuenta

-- Desactivar temporalmente una cuenta
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'usuario'@'localhost';

• Ajuste fino de permisos: Revocar privilegios específicos para implementar el principio de mínimo privilegio

-- Reducir privilegios excesivos
REVOKE DROP, TRUNCATE ON *.* FROM 'desarrollador'@'localhost';

• Transición de entornos: Revocar privilegios al pasar de desarrollo a producción

-- Ajustar privilegios para entorno de producción
REVOKE CREATE, DROP, ALTER ON aplicacion.* FROM 'app_user'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON aplicacion.* TO 'app_user'@'%';

Buenas prácticas de seguridad con REVOKE

• Documentación: Mantener un registro de los privilegios revocados y los motivos
• Automatización: Implementar scripts para auditar y revocar privilegios innecesarios periódicamente
• Pruebas: Verificar que las aplicaciones siguen funcionando después de revocar privilegios
• Principio de mínimo privilegio: Usar REVOKE proactivamente para ajustar permisos excesivos

-- Ejemplo de ajuste proactivo de privilegios
REVOKE DELETE ON clientes FROM 'servicio_cliente';
REVOKE UPDATE (limite_credito, nivel_riesgo) ON clientes FROM 'vendedor';

El comando REVOKE es una herramienta esencial en la gestión de seguridad de bases de datos SQL, permitiendo implementar políticas de acceso dinámicas y adaptables. Su uso adecuado, junto con GRANT, proporciona el control necesario para mantener un equilibrio entre la funcionalidad y la seguridad en entornos de bases de datos.