jueves, febrero 23, 2012

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

22 comentarios:

Joaquin Gonzalez dijo...

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.

Javier Morales dijo...

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

Javier Morales dijo...

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!!

Javier Morales dijo...

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.

Javier Morales dijo...

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

Anónimo dijo...

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


Saludos (Córdoba)

Anónimo dijo...

Excelente me funciono.. Gracias

Andrew Reid dijo...

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

Javier Morales dijo...

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

Anónimo dijo...

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.

German dijo...

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

Marisa Muñoz dijo...

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

Javier Morales dijo...

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!

Anónimo dijo...

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

Duque dijo...

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

Javier Morales dijo...

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!

Duque dijo...

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.

Javier Morales dijo...

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

Duque dijo...

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

Duque dijo...

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


Anónimo dijo...

Yo no intente replicar este error, si no que me esta pasando.. Hice 3 procedures diferentes con el mismo objetivo y los 3 los revise como 4 veces casi casi letra por letra jajaja.

No veía el error y el error de privilegios se me hacia solo una cortina de humo (de esas que aveces acostumbra oracle y te truenan la cabeza).

Me encontré con este post y probé... Solucionado los 3 procedures... No se a que deba, investigare mas a fondo después cuando tenga mas tiempo. Gracias.

Los procesos no son sobre vistas materializadas, son de truncar para jugar con las control carga.

Version:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

Javi García dijo...

Muy bueno, me ha ayudado a resolver el problema que tenía por el que no podia crear vistas, Muchas Gracias :D

Amazon