<

Go Premium for a chance to win a PS4. Enter to Win

x

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

Published on
15,433 Points
8,933 Views
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.


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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Join & Write a Comment

This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month