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

Javier MoralesOwner
CERTIFIED EXPERT
Oracle DBA teacher and consultant for almost 20 years.
Oracle Certified Professional. Writer of “Optimización SQL en Oracle".
Published:
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
11,799 Views
Javier MoralesOwner
CERTIFIED EXPERT
Oracle DBA teacher and consultant for almost 20 years.
Oracle Certified Professional. Writer of “Optimización SQL en Oracle".

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.