Permisos y transacciones

El control de acceso se reduce a dos sentencias. GRANT concede uno o varios privilegios sobre un objeto a un usuario o a un rol, y REVOKE se los retira. La estructura es simétrica y conviene fijarse en la preposición, porque es lo que las distingue de un vistazo: se concede a alguien y se retira de alguien.

GRANT SELECT, INSERT ON intento TO profesor;

-- El privilegio puede acotarse a columnas concretas
GRANT UPDATE (modalidad) ON alumno TO soporte;

-- EXECUTE es el privilegio de los procedimientos
GRANT EXECUTE ON PROCEDURE registrar_intento TO aplicacion;

-- Con delegación: quien lo recibe puede concedérselo a otros
GRANT ALL ON tema TO coordinador WITH GRANT OPTION;

REVOKE INSERT ON intento FROM profesor;

• SELECT, INSERT, UPDATE y DELETE: las operaciones del DML sobre el objeto. Los de actualización e inserción admiten además una lista de columnas, de modo que el permiso no alcance a toda la fila.
• EXECUTE: ejecutar un procedimiento o una función.
• USAGE: utilizar un objeto que no contiene filas, como una secuencia.
• ALL: todos los privilegios aplicables al objeto de una vez.

WITH GRANT OPTION es la cláusula que hay que mirar con cuidado: no añade ningún permiso sobre los datos, añade la capacidad de repartirlo. Quien recibe un privilegio con esa cláusula puede concedérselo a su vez a terceros, con lo que el control deja de estar en un solo sitio.

El rol es un conjunto de privilegios con nombre. Conceder al rol y asignar el rol a las personas evita tener que repetir la misma lista de GRANT para cada usuario nuevo.

Una transacción es un conjunto de sentencias SQL que tienen que ejecutarse todas o ninguna. No hay término medio: o el trabajo queda entero o la base de datos se queda como estaba antes de empezar. Por eso se dice que es un proceso atómico, en el sentido de indivisible.

El ejemplo de manual es el traspaso entre dos cuentas, con su cargo y su abono, pero vale cualquier operación que toque más de una tabla: registrar un intento y actualizar el resumen del tema tiene que pasar de golpe, porque un resumen actualizado sin su intento, o al revés, es una base de datos incoherente.

Las sentencias del control de transacciones marcan el principio y el final de esa unidad de trabajo. COMMIT confirma los cambios y los hace permanentes y visibles para los demás; ROLLBACK los deshace y devuelve la base al estado en que estaba al empezar. Mientras no haya COMMIT, nada de lo hecho es definitivo.

START TRANSACTION;

  INSERT INTO intento (id_intento, id_alumno, id_tema, aciertos, fallos, realizado)
  VALUES (5001, 1, 7, 18, 2, CURRENT_TIMESTAMP);

  SAVEPOINT antes_del_resumen;

  UPDATE resumen_tema SET intentos = intentos + 1 WHERE id_tema = 7;

  -- Se descarta solo el UPDATE; el INSERT sigue en pie
  ROLLBACK TO SAVEPOINT antes_del_resumen;

COMMIT;

• START TRANSACTION abre la transacción y END TRANSACTION la cierra. Muchos gestores la abren solos con la primera sentencia si no se hace expresamente.
• SAVEPOINT crea un punto de salvaguarda intermedio con nombre.
• ROLLBACK TO SAVEPOINT vuelve a ese punto y descarta solo lo hecho desde entonces, dejando en pie lo anterior. La transacción sigue abierta.
• RELEASE SAVEPOINT elimina un punto de salvaguarda que ya no hace falta.
• SET TRANSACTION configura la transacción antes de empezar, y es donde se fija el nivel de aislamiento.

Un ROLLBACK a secas deshace la transacción entera; un ROLLBACK a un savepoint deshace solo un tramo y deja la transacción viva.

Cuando varias transacciones trabajan a la vez sobre los mismos datos aparecen tres anomalías clásicas. Hay que saber distinguirlas porque son las que definen los niveles de aislamiento, y se distinguen por lo que ve una transacción que lee dos veces.

• Lectura sucia: una transacción lee datos que otra ha modificado y todavía no ha confirmado. Si esa otra acaba deshaciendo su trabajo, lo leído nunca llegó a existir.
• Lectura no repetible: una transacción lee una fila, otra la modifica y confirma, y al volver a leerla la primera encuentra valores distintos de los que había visto.
• Lectura fantasma: una transacción consulta un conjunto de filas con una condición, otra inserta una fila nueva que también la cumple y confirma, y al repetir la consulta aparece una fila que antes no estaba.

La diferencia entre las dos últimas es la que se pregunta, y está en qué cambia. En la no repetible cambia el valor de una fila que ya se había leído; en la fantasma no cambia ninguna fila conocida, sino que aparece una nueva dentro del rango consultado. De ahí el nombre: la fila no estaba y se ha materializado.

El nivel de aislamiento se fija con SET TRANSACTION y es la palanca con la que se decide cuánta concurrencia se tolera. Cada escalón elimina una anomalía más y, a cambio, obliga al gestor a bloquear más y a dejar pasar menos trabajo a la vez.

READ COMMITTED es el nivel que aplican por defecto la mayoría de gestores, y es el punto de equilibrio razonable: no se leen datos sin confirmar, pero se admite que lo leído cambie a lo largo de la transacción. En el extremo opuesto, SERIALIZABLE deja el resultado igual que si las transacciones se hubieran ejecutado una detrás de otra, y es el único que llega a bloquear rangos enteros y no solo las filas concretas ya leídas. Es el más seguro y el que menos rendimiento da.

Los cuatro niveles van en escala: cada uno evita todo lo que evita el anterior y una anomalía más. Cuanto más aislamiento, menos concurrencia.