El misterioso caso del error de "privilegios insuficientes" en la creación de vista materializada dentro de un procedimiento PL/SQL.

Recuerdo aquella vez que me pidieron crear un usuario para una aplicación en desarrollo. El usuario debía ser capaz de crear vistas y vistas materializadas, de modo que usé la siguiente sintaxis.

SQL> create user desarrollo identified by desarrollo;
User created.
SQL> grant connect, resource, create view, create materialized view to desarrollo;
Grant succeeded.


De este modo, creía yo, garantizaba que el usuario podría tener ese privilegio de forma explícita, y no mediante un rol, y así descartaba errores que podrían producirse por la no herencia de privilegios a través de roles en el uso de PL/SQL.

No obstante, el usuario vino a mi mesa a decirme: "No puedo crear vistas materializadas. Privilegios insuficientes".
 -¿Cómo es posible? - pregunté sorprendido. - Te aseguro que el usuario tiene privilegios para crearlas.

De modo que abrí una consola de sqlplus y ejecuté:


SQL> create materialized view test as select * from dual;
Materialized view created.

- ¿Ves? El usuario tiene privilegios.
- Ya, pero es que yo lo ejecuto dentro de un procedimiento PL/SQL, mediante el comando EXECUTE IMMEDIATE. - contestó.
- Vamos a probar.


SQL> drop materialized view test;
Materialized view dropped.
SQL> begin
  2      execute immediate 'create materialized view test as select * from dual';
  3  end;
  4  /
PL/SQL procedure successfully completed.
SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DUMMY                                              VARCHAR2(1)

- Pues no lo entiendo - argumentaba, sin salir de su asombro- mi procedimiento PL/SQL da error al ejecutarlo, y la sintaxis ejecutada en una consola de SQL no da errores.

¿qué podría ser? ¿por qué una vista materializada daba error en un procedimiento y no en un bloque anónimo?. Decidí hacer la prueba y comprobarlo por mi mismo. No podía creerlo.

SQL> drop materialized view test;

Materialized view dropped.

SQL> create procedure crea_mv_test as

  2  begin

  3      execute immediate 'create materialized view test as select * from dual';

  4  end;

  5  /

*

ERROR at line 1:

ORA-01031: insufficient privileges

ORA-06512: at "DESARROLLO.CREA_MV_TEST", line 3

ORA-06512: at line 1

El usuario tenía privilegios explícitos para crear una vista materializada, incluso desde un bloque anónimo, pero no tenía privilegios suficientes para crearla desde dentro de un procedimiento PL/SQL. 

No tenía sentido. El mismo usuario podía lanzar esa sintaxis desde la línea de comandos de sqlplus, dentro de un bloque anónimo, pero no en la creación de un procedimiento PL/SQL.

Entonces vi la respuesta, como una luz guiando mis dedos a añadir la cláusula authid current_user.

Así fue. La ejecución del código funcionaba cuando se especificaba claramente que los privilegios debían ser los del usuario conectado y no los del creador del procedimiento.

SQL> create or replace procedure crea_mv_test authid current_user as

  2  begin

  3         execute immediate 'create materialized view test as select * from dual';

  4  end;

  5  /

Procedure created.

SQL> exec crea_mv_test;

PL/SQL procedure successfully completed.

- Añade esta cláusula al procedure y vuelve a ejecutarlo.

- Funciona! - Exclamó. - ¿Cómo es posible?

- Porque la cláusula authid current_user está heredando los privilegios del usuario que ejecuta el procedimiento, y no los privilegios que tiene el usuario creador del procedimiento.

- Pero, ¡Si se trata del mismo usuario! ¿qué sentido tiene?

Yo ya no atendía a sus planteamientos. Había resuelto el problema. El código se compilaba y ejecutaba correctamente. Las vistas materializabas se creaban todas en sucesión, sin mostrar ningún error. Mi trabajo estaba hecho. Pasó el tiempo, y nunca sabré si lo sucedido aquel día respondía a, lo que se suele llamar en los círculos técnicos, un "expected behaviour".