GRANT en SQL: privilegios sobre tablas, esquemas y columnas con minimo privilegio

GRANT entrega el derecho a hacer algo: leer una tabla, insertar filas, usar un esquema. En PostgreSQL un rol normal no ve nada mas alla de lo que se le permitio de forma explicita, asi que un buen conjunto de sentencias GRANT es tu modelo de acceso. Si lo dejas demasiado amplio, un analista reescribe sin querer los datos de produccion.

Privilegios de tabla

La forma basica lista las acciones y el objeto, y luego el rol receptor. Tomemos las conocidas users, orders y employees.

CREATE ROLE analyst NOLOGIN;

GRANT SELECT, INSERT ON orders TO analyst;
GRANT SELECT ON users TO analyst;

Los privilegios de tabla son SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES y TRIGGER. La palabra ALL los concede todos de una vez:

GRANT ALL PRIVILEGES ON employees TO analyst;

Trampa: conceder SELECT sobre una tabla no basta si el analista no puede entrar al esquema. Para llegar a un objeto tambien necesitas USAGE sobre el esquema que lo contiene:

GRANT USAGE ON SCHEMA public TO analyst;

Sin USAGE la consulta falla con permission denied for schema, aunque el privilegio de tabla exista tecnicamente.

Columnas y esquemas completos

Los privilegios se pueden acotar a columnas individuales. Resulta util cuando employees.salary es sensible pero el nombre y el departamento puede leerlos cualquiera.

GRANT SELECT (id, name, dept) ON employees TO analyst;

Ahora SELECT id, name FROM employees funciona, pero SELECT salary FROM employees se rechaza. UPDATE tambien admite columnas: GRANT UPDATE (status) ON orders TO analyst permite cambiar solo el estado del pedido.

En sentido inverso, puedes conceder sobre todas las tablas de un esquema con un solo comando:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst;

Trampa importante: ALL TABLES IN SCHEMA solo afecta a las tablas que existen en el momento en que se ejecuta. Una tabla creada manana no recibe el privilegio. Para conceder de forma automatica en adelante, configura ALTER DEFAULT PRIVILEGES:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO analyst;

WITH GRANT OPTION y la cesion de derechos

Por defecto, quien recibe un privilegio no puede cederlo a otros. WITH GRANT OPTION cambia eso:

GRANT SELECT ON orders TO team_lead WITH GRANT OPTION;

Ahora team_lead puede ejecutar el GRANT SELECT ON orders TO ... por su cuenta. Es comodo para delegar, pero arriesgado: pierdes el control sobre quien acaba teniendo acceso. Otra trampa es que entonces REVOKE necesita CASCADE, o el revocado queda bloqueado por privilegios dependientes:

REVOKE SELECT ON orders FROM team_lead CASCADE;

CASCADE revoca tambien cada privilegio que team_lead haya repartido a partir de esa grant option.

Privilegios de objeto frente a pertenencia a rol

Son dos mecanismos distintos que es facil confundir.

Un privilegio de objeto es un derecho como SELECT ON orders, ligado a un objeto concreto.
La pertenencia a rol es GRANT role_a TO role_b, que convierte a role_b en miembro de role_a y le da todos los privilegios de role_a de golpe.

Los roles de agrupacion son la columna vertebral de un control de acceso manejable:

CREATE ROLE readonly NOLOGIN;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

CREATE ROLE alice LOGIN PASSWORD 'secret';
GRANT readonly TO alice;

Ahora alice lee todo a traves de su pertenencia a readonly. Cambia los privilegios de readonly y se aplican a todos los miembros a la vez, sin nombrar a nadie de forma individual.

Disenar con minimo privilegio

El principio de minimo privilegio es simple: un rol recibe exactamente lo que necesita para su trabajo y ni un byte mas.

No concedas derechos directamente a los usuarios; reunelos en roles de agrupacion (readonly, app_write, reporting).
Protege las columnas sensibles con grants a nivel de columna en lugar de SELECT sobre toda la tabla.
Recuerda que el propietario de la tabla y el superusuario tienen acceso total que esquiva por completo GRANT; REVOKE no los afecta.

Diferencias entre motores:

MySQL no tiene ALL TABLES IN SCHEMA; usa GRANT SELECT ON mydb.* para una base entera. Los grants por columna si existen: GRANT SELECT (name) ON employees TO 'analyst'@'%'.
ClickHouse soporta GRANT, roles y WITH GRANT OPTION, pero no tiene el concepto de USAGE de esquema; los derechos se conceden sobre la base y la tabla directamente.

La regla base: da USAGE sobre el esquema, los privilegios minimos sobre los objetos, agrupa mediante roles y manten WITH GRANT OPTION bajo control estricto.