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».

Comments

  1. Joaquin Gonzalez
    • 7 marzo, 2012
    • 10:16 am
    • Reply

    Hola Javier,

    He intentado reproducir el problema y no aparece. No necesito crear el procedimiento con authid current_user.

    Pureba:

    drop user test cascade;
    create user test identified by test;
    alter user test quota unlimited on users;
    grant create session to test;
    grant create table to test;
    grant create materialized view to test;
    grant create procedure to test;
    conn test/test@j1
    create or replace procedure crea_mv_test as
    begin
    execute immediate 'create materialized view test_mv as select * from dual';
    end;
    /
    exec crea_mv_test

    Log:

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – Production
    With the Partitioning, Automatic Storage Management and Real Application Testing options

    J1.sys>

    drop user test cascade;

    User dropped.

    J1.sys>

    create user test identified by test;

    User created.

    J1.sys>

    alter user test quota unlimited on users;

    User altered.

    J1.sys>

    grant create session to test;

    Grant succeeded.

    J1.sys>

    grant create table to test;

    Grant succeeded.

    J1.sys>

    grant create materialized view to test;

    Grant succeeded.

    J1.sys>

    grant create procedure to test;

    Grant succeeded.

    J1.sys>

    J1.sys>

    conn test/test@j1
    Connected.

    j1.test>

    j1.test>

    create or replace procedure crea_mv_test as
    2 begin
    3 execute immediate 'create materialized view test_mv as select * from dual';
    4 end;
    5 /

    Procedure created.

    j1.test>

    j1.test>

    exec crea_mv_test

    PL/SQL procedure successfully completed.

  2. Javier Morales
    • 7 marzo, 2012
    • 10:38 am
    • Reply

    Hola Joaquín,

    Muchas gracias por tu comentario. Vamos a ver:

    1.- Se trata de versión 11.2.0.1 (quizás influya)
    2.- Cuando reproduzco tu caso, me da privilegios insuficientes en tablespace SYSTEM.

    SQL> connect / as sysdba
    Connected.
    SQL> drop user test cascade;

    User dropped.

    SQL> create user test identified by test;

    User created.

    SQL> alter user test quota unlimited on users;

    User altered.

    SQL> grant create session to test;

    Grant succeeded.

    SQL> grant create table to test;

    Grant succeeded.

    SQL> grant create materialized view to test;

    Grant succeeded.

    SQL> grant create procedure to test;

    Grant succeeded.

    SQL> conn test/test
    Connected.
    SQL> create or replace procedure crea_mv_test as
    begin
    execute immediate 'create materialized view test_mv as select * from dual';
    end;
    / 2 3 4 5

    Procedure created.

    SQL> exec crea_mv_test
    BEGIN crea_mv_test; END;

    *
    ERROR at line 1:
    ORA-01950: no privileges on tablespace 'SYSTEM'
    ORA-06512: at "TEST.CREA_MV_TEST", line 3
    ORA-06512: at line 1

    Si doy cuota sobre SYSTEM al usuario test, se ejecuta sin problemas tal como indicas:

    SQL> connect / as sysdba
    Connected.
    SQL> alter user test quota unlimited on system;

    User altered.

    SQL> connect test/test
    Connected.
    SQL> exec crea_mv_test

    PL/SQL procedure successfully completed.

    Por lo que veo, la única diferencia entre tu test y el mío (con el error misterioso) está en la asignación de cuota sobre el usuario, que en mi caso es una cuota ilimitada por la herencia del rol RESOURCE.

    SQL> select * from dba_sys_privs where grantee='TEST';

    GRANTEE PRIVILEGE ADM
    —————————— —————————————- —
    TEST CREATE PROCEDURE NO
    TEST CREATE MATERIALIZED VIEW NO
    TEST CREATE SESSION NO
    TEST CREATE TABLE NO

    SQL> grant resource to test;

    Grant succeeded.

    SQL> select * from dba_sys_privs where grantee='TEST';

    GRANTEE PRIVILEGE ADM
    —————————— —————————————- —
    TEST CREATE PROCEDURE NO
    TEST CREATE MATERIALIZED VIEW NO
    TEST CREATE SESSION NO
    TEST CREATE TABLE NO
    TEST UNLIMITED TABLESPACE NO

    Voy a reproducir mi ejemplo con la diferencia de la asignación de quota sobre tablespace explícita, y con la asignación heredada del rol RESOURCE.

    Lo hago en otro comentario, que esto va siendo muy largo…

    MUCHISIMAS GRACIAS !!!!
    Javier

  3. Javier Morales
    • 7 marzo, 2012
    • 10:53 am
    • Reply

    Sorpresa!

    Yo tampoco puedo reproducir el error ahora.

    SQL> create user test_quota_expl identified by test;

    User created.

    SQL> create user test_rol_resource identified by test;

    User created.

    SQL> grant create session, create table, create materialized view, create procedure
    2 to test_quota_expl, test_rol_resource;

    Grant succeeded.

    SQL> grant resource to test_rol_resource;

    Grant succeeded.

    SQL> alter user test_quota_expl quota unlimited on users;

    User altered.

    SQL> alter user test_quota_expl default tablespace users;

    User altered.

    SQL> alter user test_rol_resource default tablespace users;

    User altered.

    SQL> connect test_rol_resource/test
    Connected.
    SQL> create or replace procedure crea_mv_test as
    2 begin
    3 execute immediate 'create materialized view test_mv as select * from dual';
    4 end;
    5 /

    Procedure created.

    SQL> exec crea_mv_test

    PL/SQL procedure successfully completed.

    SQL> connect test_quota_expl/test
    Connected.
    SQL> create or replace procedure crea_mv_test as
    2 begin
    3 execute immediate 'create materialized view test_mv as select * from dual';
    4 end;
    5 /

    Procedure created.

    SQL> exec crea_mv_test

    PL/SQL procedure successfully completed.

    … de hecho, éste debería ser el comportamiento esperado!!

  4. Javier Morales
    • 7 marzo, 2012
    • 10:58 am
    • Reply

    Bueno, ESTE es el caso del ejemplo:
    ¿puedes intentar reproducirlo?

    SQL> create user desarrollo identified by desarrollo;

    User created.

    SQL> grant connect, resource, create view, create materialized view to desarrollo;

    Grant succeeded.

    SQL> connect desarrollo/desarrollo
    Connected.
    SQL> create procedure crea_mv_test as
    2 begin
    3 execute immediate 'create materialized view test as select * from dual';
    4 end;
    5 /

    Procedure created.

    SQL> exec crea_mv_test
    BEGIN crea_mv_test; END;

    *
    ERROR at line 1:
    ORA-01031: insufficient privileges
    ORA-06512: at "DESARROLLO.CREA_MV_TEST", line 3
    ORA-06512: at line 1

    SQL> create procedure crea_mv_test_auth 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_auth

    PL/SQL procedure successfully completed.

    SQL> create procedure crea_mv_test_auth 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_auth

    PL/SQL procedure successfully completed.

  5. Javier Morales
    • 7 marzo, 2012
    • 11:07 am
    • Reply

    … y para más sorpresa, el "truco" parece estar en el priviegio "create table"

    SQL> connect desarrollo/desarrollo
    Connected.
    SQL> exec crea_mv_test
    BEGIN crea_mv_test; END;

    *
    ERROR at line 1:
    ORA-01031: insufficient privileges
    ORA-06512: at "DESARROLLO.CREA_MV_TEST", line 3
    ORA-06512: at line 1

    SQL> connect / as sysdba
    Connected.
    SQL> grant create table to desarrollo;

    Grant succeeded.

    SQL> connect desarrollo/desarrollo
    Connected.
    SQL> exec crea_mv_test

    PL/SQL procedure successfully completed.

  6. Anónimo
    • 15 abril, 2013
    • 8:24 am
    • Reply

    No existe el problema si se da grant directo al usuario, y no al role y este al usuario.

    Saludos (Córdoba)

  7. Anónimo
    • 30 diciembre, 2013
    • 8:52 pm
    • Reply

    Excelente me funciono.. Gracias

  8. Andrew Reid
    • 28 febrero, 2014
    • 7:53 pm
    • Reply

    Hola Javier,

    Espero que estas bien.

    Tal vez tengo una respuesta a este problema.

    Para crear un materialized view, es necesario tener los privilegios CREATE MATERIALIZED VIEW y CREATE TABLE. Acabo de poner un ejemplo en mi blog ingles sobre este asunto.

    En tu ejemplo, has dado CREATE MATERIALIZED VIEW al usuario pero has dado CREATE TABLE mediante el role RESOURCE.

    Un Saludo

    Andrew

  9. Javier Morales
    • 3 marzo, 2014
    • 11:58 am
    • Reply

    Hola Andrew!

    Si ves en comentarios, dos más arriba del tuyo comento precisamente esto que mencionas: si se da CREATE TABLE al usuario directamente no da ese error, pero lo "misterioso" del caso es que sin el CREATE TABLE concedido de forma directa, sí es posible crear vistas materializadas desde SQL y desde bloques anónimos PL/SQL y no desde un procedure.

    Curioso, ¿a que si?

    Muchas gracias por tu comentario!! Nos seguimos leyendo!
    Javier

  10. Anónimo
    • 22 julio, 2014
    • 3:05 pm
    • Reply

    Hola Javier,

    Intento hacer lo siguiente sin éxito:

    Al intentar ejecutar un procedimiento del usuario test que hace un llamado a una función de otro usuario genera el mensaje:

    ORA-06550: line 1, column 12:
    PLS-00905: object TEST.P_TEST_EMAIL is invalid
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

    Realizó la verificación y encuentro que usuario Test tiene asignado permisos de ejecución sobre la función del otro usuario a través de un rol.

    Pero al asignar el permiso de ejecución directamente al usuario Test y no a través de rol, el usuario Test puede ejecutar el procedimiento sin problemas.

    En conclusión el permiso de execute a través del rol no lo esta tomando, porqué ocurre esto?
    No me gusta asignar permisos directamente a los usuarios, todo a través de un rol.

    Gracias por tu respuesta y tiempo.

  11. German
    • 9 abril, 2015
    • 7:57 pm
    • Reply

    Hola, tengo el mismo problema pero al crear una tabla temporal…

  12. Marisa Muñoz
    • 2 julio, 2015
    • 9:44 am
    • Reply

    He tenido el mismo problema y con tu blog lo he solucionado. Gracias !!!

  13. Javier Morales
    • 2 julio, 2015
    • 11:15 am
    • Reply

    Hola Marisa! Me alegro muchísimo! La verdad es que ese comportamiento raro no lo tienen muy bien rematado. Gracias por comentarlo!

    Germán y anónimo… pues eso ocurre. Es necesario el create table de forma explícita y no mediante rol. En algunas notas de soporte hacen mención a ello, pero parece que es algo no muy bien cerrado.

    Gracias a todos por comentar!

  14. Anónimo
    • 13 agosto, 2015
    • 2:03 pm
    • Reply

    A mí también me daba el error de "privilegios insuficientes", pero en mi caso
    era con un:

    EXECUTE IMMEDIATE 'create table ….';

    Le agregué al procedure el "authid current_user" y funcionó OK!

    Estaba usando Oracle 11.2.0.4.0 y el usuario tenía los provilegios:
    CONNECT, RESOURCE

  15. Duque
    • 11 diciembre, 2015
    • 12:54 pm
    • Reply

    Buenos Dias Javier
    Quiero consultarte por si te haya pasado, capaz no sea el mismo problema de este post.
    No estoy pudiendo acceder con el usuario SYS a traves del pl/sql developer, tambien instale el Oracle SQL Developer, trate de acceder y me sale privilegios insuficientes, error ORA-01031.
    Accediendo directo por el sqlplus / as sysdba puedo acceder.
    Por si tuvieras un caso similar.. Muchas gracias y disculpe si no corresponde la consulta al post

    Julio Ovelar

  16. Javier Morales
    • 11 diciembre, 2015
    • 12:59 pm
    • Reply

    Hola Duque,

    Pero, cual es la versión, plataforma, etc?
    ten en cuenta que para conectarse como SYS hay que hacerlo como SYSDBA.

    🙂 van por ahí los tiros?

    Gracias!

  17. Duque
    • 11 diciembre, 2015
    • 1:11 pm
    • Reply

    Gracias por responder.. la version version es un Oracle 10g, estoy quiero conectarme desde un equipo con windows 7 64 bits, donde tengo instalado el pl/sql. Probando directamente desde el equipo donde esta corriendo la base que es un Linux Red hat, puedo acceder por el sqlplus.
    Si, trato de acceder como sysdba.

  18. Javier Morales
    • 11 diciembre, 2015
    • 1:34 pm
    • Reply

    Es posible que sea por la versión de estos productos, que usen un cliente para 11g/12c

    Ten en cuenta que la versión 10g está fuera de soporte, y lo que cuentas es un problema de error de producto.

    Siento no poder ayudarte… Un saludo,
    Javier

  19. Duque
    • 11 diciembre, 2015
    • 2:03 pm
    • Reply

    Entiendo Javier.. dale, tranqui.. muchas gracias igualmente.

  20. Duque
    • 11 diciembre, 2015
    • 7:36 pm
    • Reply

    Buenas..
    ya encontre la solucion, y queria compartirlo
    Al tratar de dar permisos sysdba a otro usuario para probar, me salio el siguiente error

    grant sysdba to usuario;
    y me dice
    ERROR at line 1:
    ORA-01994: GRANT failed: password file missing or disabled

    La solucion es recrear el ORAPWD

    orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=password force=y

    orapwd file=$ORACLE_HOME/dbs/orapwIPPROT password=password entries=30 force=y

  21. yo
    • 8 noviembre, 2018
    • 7:34 pm
    • Reply

    Me has salvado de perder mil años buscando el error…

    En mi caso el creador y el dueño de Procedure es el mismo, asi que no tiene sentido.
    Pero funciona con tu sugerencia..
    Muchas gracias…

    • admin
      • 9 noviembre, 2018
      • 12:02 pm
      • Reply

      Hola!
      Me alegro de que te haya servido! De verdad que es un poco «expediente X», verdad?
      Un saludo,
      Javier

Responder a Andrew Reid

Responder a Andrew Reid Cancelar la respuesta

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.