This way, I guessed, I would ensure that user has these particular privileges explicitly, and not granted by a role. Then, any issues related to a non heritage of privileges through roles in use of PL/SQL would also be discarded.
But, to my surprise, the user came to my desk and told me: "I can't create materialized views. Insufficient privileges".
- how is it possible? - I asked absolutely astonished. - I ensure you the user has enough privileges for materialized views creation.
And so, I openen a sqlplus console and ran:
SQL> create materialized view test as select * from dual;Materialized view created.
- You can see it. The user has enough privileges-
- Well, but I'm executing it inside a PL/SQL procedure, using the EXECUTE IMMEDIATE command. - He answered.
- Well, let's try.
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)
- I don't understand what's happening here - he discussed, totally out of the blue- my PL/SQL procedure shows an insufficient privileges error when executing it, and so the syntax launched in the SQL console creates the views without any issues.
What can it be? Why a materialized view was created successfully in SQL console, or a anonymous PL/SQL block, but not inside a procedure? I decided to make the test and try it by myself.
And I couldn't believe it what I found.
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 privilegesORA-06512: at "DESARROLLO.CREA_MV_TEST", line 3ORA-06512: at line 1
- Just add this clause to the procedure creation and launch it again.
- It works! - he told. - what happened? what does this clause do?
- Well, authid current_user clause switch the use of invoker's rights instead of definer's rights.
- But, It's the same user! does it make any sense?
I was not listen to him anymore. I did solve the problem and the code was now working without without issues. Materialized views were being created fluently, and my job was done.
Time passed by, and I still wonder if what happened that day was what some experts use to call it "an expected behaviour".