<

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

Published on
15,981 Points
9,481 Views
Last Modified:
Approved
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

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Join & Write a Comment

Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month