<

The strange case of the insufficient privileges error when creating a materialized view using execute immediate inside a PL/SQL procedure.

Published on
16,599 Points
10,099 Views
Last Modified:
Approved
Javier Morales
Oracle DBA teacher and consultant for almost 20 years.
Oracle Certified Professional. Writer of “Optimización SQL en Oracle".
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax:

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

Open in new window


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.

Open in new window


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

Open in new window


- 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 privileges
ORA-06512: at "DESARROLLO.CREA_MV_TEST", line 3
ORA-06512: at line 1

Open in new window


User has privileges to create views, even in a anonymous PL/SQL block, but not launched inside a PL/SQL procedure. It has no sense.

Then, I saw the answer, like a light guiding my fingers to type the authid current_user clause to the definition of the PL/SQL procedure.

I hit the key. It worked. The execution ran when the privileges heritage was defined as the runner of the code, and not the owner of the procedure.

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.

Open in new window


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

The end.


Epilogue.
For more information, please refer to:
Oracle Documentation - Using Invoker's Rights Versus Definer's Rights (AUTHID Clause)
0
Enjoy this complimentary article view.

Get unlimited access to our entire library of technical procedures, guides, and tutorials written by certified industry professionals.

Get 7 days free
Click here to view the full article

Using this article for work? Experts Exchange can benefit your whole team.

Learn More
COLLABORATE WITH CERTIFIED PROFESSIONALS
Experts Exchange is a tech solutions provider where users receive personalized tech help from vetted certified professionals. These industry professionals also write and publish relevant articles on our site.
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Learn from the best.