Asignación de permisos (ROLES) a los programas PLSQL (mejoras versión 12c)

Con anterioridad a la versión 12c de la base de datos Oracle, un procedimiento PLSQL o unidad de programa con privilegios definidos a través de la cláusula AUTHID DEFINER siempre se ejecutaba con los privilegios del propietario del programa. Por otro lado, cuando la misma unidad de programa se definía utilizando la cláusula AUTHID CURRENT_USER, dicho programa siempre se ejecutaba con los privilegios del usuario que lo ejecutaba.

Disponer de solo estas dos formas de utilización de la cláusula AUTHID limitaba bastante la funcionalidad de las bases de datos Oracle en cuestión de seguridad, ya que cuando un usuario necesitaba ejecutar un programa PL/SQL determinado, dicho usuario tenía que tener los mismos privilegios que el propietario del programa. El problema de seguridad se agravaba mucho más cuando eran todos los usuarios los que necesitan tener acceso a dicho programa.

En la versión 12c de la base de datos Oracle es posible asignar privilegios y permisos a los propios paquetes, procedimientos y funciones PLSQL. El poder asignar permisos a este nivel permite a los programadores definir, de una manera mucho más adecuada desde una perspectiva de seguridad, los permisos de los programas que ellos mismos crean.

La versión 12c permite definir un programa PL/SQL para que utilice los permisos del usuario que lo ejecuta y complementar dichos privilegios con permisos adicionales específicos (ROLES).

Como en otras ocasiones lo mejor es comprobar a través de un ejemplo la forma en la que la versión 12c permite asignar permisos o roles a las unidades de programa y el impacto que dicha asignación puede tener.

Supongamos que el esquema COMPRAS de nuestra base de datos contiene dos tablas denominadas compradores y proveedores, tablas que definiremos y poblaremos a continuación:

CREATE TABLE compradores
(
  comprador_id INTEGER,
  nombre       VARCHAR2 (100),
  apellidos    VARCHAR2 (150)
)
/

BEGIN
  INSERT INTO compradores
    VALUES   (1, 'Pepe', 'López');
  INSERT INTO compradores
    VALUES   (2, 'Juan', 'Pérez');
  COMMIT;
END;
/

CREATE TABLE proveedores 
(
  proveedor_id INTEGER,
  comprador_id INTEGER,
  nombre       VARCHAR2 (100)
)
/

BEGIN
  INSERT INTO proveedores
    VALUES   (10, 1, 'Oracle');
  INSERT INTO proveedores
    VALUES   (11, 1, 'Microsoft');
  INSERT INTO proveedores
    VALUES   (12, 1, 'IBM');
  INSERT INTO proveedores
    VALUES   (13, 2, 'Dell');
  COMMIT;
END;/

Ahora supondremos que el esquema FINANZAS dispone solo de una tabla denominada proveedores como la siguiente:

CREATE TABLE proveedores 
(
  proveedor_id INTEGER,
  comprador_id INTEGER,
  nombre       VARCHAR2 (100)
)
/

BEGIN
  INSERT INTO proveedores
    VALUES   (14, 1, 'Carrefour');
  INSERT INTO proveedores
    VALUES   (15, 1, 'Alcampo');
  INSERT INTO proveedores
    VALUES   (16, 1, 'Día');
  INSERT INTO proveedores
    VALUES   (13, 2, 'Dell');
  COMMIT;
END;
/

Además, el esquema COMPRAS también dispone de un procedimiento PLSQL denominado borrar_proveedores que borra todos los proveedores asignados a un comprador específico, siempre y cuando el comprador no se apellide Pérez. Inicialmente crearemos el procedimiento con permisos de AUTHID DEFINER.

CREATE OR REPLACE PROCEDURE borrar_proveedores
(
  p_comprador_id IN proveedores.comprador_id%TYPE)
  AUTHID DEFINER
IS
  l_apellidos   compradores.apellidos%TYPE;
BEGIN
  SELECT   apellidos
    INTO   l_apellidos
    FROM   compras.compradores
   WHERE   comprador_id = p_comprador_id;

  IF l_apellidos != 'Pérez'
  THEN
    DELETE FROM   proveedores
          WHERE   comprador_id = p_comprador_id;
  END IF;
END;
/

Y ahora damos permisos de ejecución al esquema FINANZAS:

GRANT EXECUTE
  ON borrar_proveedores
  TO finanzas
/

Cuando FINANZAS ejecute el procedimiento utilizando el comando que podéis ver debajo, se borrarán tres registros, pero de la tabla COMPRAS.proveedores, ya que el procedimiento se ha definido con permisos AUTHID DEFINER (los mismos que los del esquema propietario del procedimiento, es decir, COMPRAS).

BEGIN
  compras.borrar_proveedores (1);
END;
/

Ahora supongamos que queremos cambiar el procedimiento PL/SQL para que lo que haga sea borrar los datos de la tabla FINANZAS.proveedores en lugar de COMPRAS.proveedores. Esto es lo que precisamente ocurriría si hubiéramos utilizado la cláusula AUTHID CURRENT_USER al crear el procedimiento. El problema es que si cambiamos la cláusula AUTHID DEFINER del procedimiento por la AUTHID CURRENT_USER y ejecutamos el procedimiento de nuevo, obtendremos el siguiente mensaje de error:

BEGIN
*
ERROR en línea 1:
ORA-00942: la tabla o vista no existe
ORA-06512: en "COMPRAS.BORRAR_PROVEEDORES", línea 8
ORA-06512: en línea 2

El problema es que la base de datos Oracle está utilizando los permisos de FINANZAS para resolver la referencia a las dos tablas utilizadas en el procedimiento PLSQL borrar_proveedores, es decir, COMPRAS.compradores y FINANZAS.proveedores, y el esquema FINANZAS no tiene permisos sobre la primera de ellas ya que pertenece a un esquema diferente, por lo que la base de datos Oracle genera el error ORA-00942.

Con anterioridad a la versión 12c los DBA, para solucionar este problema, tenían que asignar al esquema FINANZAS los privilegios necesarios sobre la tabla COMPRAS.compradores. Con la versión 12c, los DBA pueden solucionar el problema de una manera mucho más eficaz desde el punto de vista de la seguridad:

Primero desde un esquema con los permisos adecuados tienen que crear una nueva ROLE que asignarán al esquema COMPRAS.

CREATE ROLE compras_compradores
/

GRANT compras_compradores TO compras
/

Posteriormente conectados como COMPRAS, asignarán los permisos deseados a la ROLE recientemente creada y también asignarán dicha ROLE al procedimiento borrar_proveedores:

GRANT SELECT
  ON compradores
  TO compras_compradores
/

GRANT compras_compradores 
  TO PROCEDURE borrar_proveedores
/

Y ahora al ejecutar las siguientes sentencias SQL desde el esquema FINANZAS, los registros correspondientes serán borrados sin problemas de la tabla FINANZAS.proveedores.

SELECT   COUNT (*)
  FROM   proveedores
 WHERE   comprador_id = 1
/

COUNT(*)
----------
3

BEGIN
  compras.borrar_proveedores (1);
END;
/

SELECT   COUNT (*)
  FROM   proveedores
 WHERE   comprador_id = 1
/

COUNT(*)
----------
0

Como podéis ver, los permisos asignados a una unidad de programa PL/SQL no afectan al proceso de compilación del mismo (distinto de lo que ocurre con las cláusulas AUTHID). Sin embargo sí que afectan a la comprobación de los permisos sobre las sentencias SQL ejecutadas por dicho programa durante el tiempo de ejecución. Por lo tanto, el procedimiento o la función correspondiente se ejecuta con ambos permisos, los autorizados por la cláusula AUTHID y los de las propias ROLES asignadas al programa.

Como podréis deducir fácilmente, esta nueva funcionalidad tiene especial utilidad para aquellos programas que se ejecuten con permisos AUTHID CURRENT_USER.

Otras mejoras de la versión 12c:
Cláusula ACCESSIBLE BY.
Cláusula WITH FUNCTION.