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”.
Javier Morales llevo más de 15 años dedicados a formación y consultoría senior de entornos Oracle.
También le apasiona el buen cine y la buena música. Viaja mucho menos de lo que me gustaría, pero a cambio intento descubrir a fondo los lugares interesantes que me rodean.
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 /
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…
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 /
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.
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
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.
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.
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
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!!
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.
… 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.
No existe el problema si se da grant directo al usuario, y no al role y este al usuario.
Saludos (Córdoba)
Excelente me funciono.. Gracias
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
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
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.