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

Published on
16,139 Points
Last Modified:
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.

For more information, please refer to:
Oracle Documentation - Using Invoker's Rights Versus Definer's Rights (AUTHID Clause)

Featured Post

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Join & Write a Comment

Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to take different types of Oracle backups using RMAN.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month