Home » SQL & PL/SQL » SQL & PL/SQL » insufficient privs error
insufficient privs error [message #410222] Thu, 25 June 2009 14:00 Go to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member

create or replace PROCEDURE p_tst
IS
v_count number; 
BEGIN



EXECUTE IMMEDIATE 'CREATE TABLE tmp_tbl as select did from parent ';

dbms_output.put_line('deleting');


EXECUTE IMMEDIATE 'DELETE FROM ex WHERE ( did) IN
( select did from parent )';

v_count := SQL%ROWCOUNT;
COMMIT;
dbms_output.put_line(v_count);


dbms_output.put_line('dropping table ');
EXECUTE IMMEDIATE 'DROP TABLE ex CASCADE CONSTRAINTS';



END p_tst ;
/





the procedure and the tables are in my own schema

when i try to do this

begin
p_tst;
end;
/

i get insufficient privs on this line:

EXECUTE IMMEDIATE 'CREATE TABLE tmp_tbl as select did from parent ';
Re: insufficient privs error [message #410224 is a reply to message #410222] Thu, 25 June 2009 14:09 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
Until proven otherwise, I believe Oracle's error message.

Why EXECUTE IMMEDIATE DELETE .....?

DELETE is valid within PL/SQL.

It really is a bad idea to be creating objects from PL/SQL procedures.
Re: insufficient privs error [message #410225 is a reply to message #410222] Thu, 25 June 2009 14:11 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


Try to grant necessary permission from DBA account.

Thanks
Re: insufficient privs error [message #410255 is a reply to message #410222] Thu, 25 June 2009 21:44 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
There are several problems with your post:

1) you have not formatted your code. I am assuming you do not know there is a code formatter on the front page of OraFAQ. Use it. You did use the CODE tags so thanks for that.

CREATE OR REPLACE PROCEDURE P_tst 
IS 
  v_count  NUMBER; 
BEGIN 
  EXECUTE IMMEDIATE 'CREATE TABLE tmp_tbl  AS   SELECT did   FROM   PARENT '; 
   
  dbms_output.Put_line('deleting'); 
   
  EXECUTE IMMEDIATE 'DELETE FROM ex WHERE ( did) IN ( select did from parent )'; 
   
  v_count := SQL%ROWCOUNT; 
   
  COMMIT; 
   
  dbms_output.Put_line(v_count); 
   
  dbms_output.Put_line('dropping table '); 
   
  EXECUTE IMMEDIATE 'DROP TABLE ex CASCADE CONSTRAINTS '; 
END p_tst; 
/ 

2) you have not provided sufficient DDL for others to test your code. Since you are working with two other tables, you should have provided create table commands and insert sql as necessary to set up a test case. Shame on you.

To your credit, you did identify the line that is the problem.

Whenever you get strange results like this, you should start thinking as was suggested, that it is a privilege problem. Of course Oracle has said as much with the error message. The problem is related to how PLSQL figures necessary privileges in order to allow certain operations. For example, I believe roles are initially turned off when inside a plsql procedure so direct grants are needed to get many advanced things done. Please do not ask me to explain as I do not know the actual works and I suspect this is a bug.

One solution to your problem is to get the CREATE ANY TABLE system privilege. Then your code will work.

grant CREATE ANY TABLE to <yourusernamehere>;

then run your procedure.

I would report this as a bug. You will notice that if you comment out the CREATE TABLE line, your procedure will proceed to drop the table EX. Strange that using EXECUTE IMMEDIATE one cannot create tables without system privileges, but one can drop them. This is a bug by any reasonable standard and it needs to be reported to Oracle support. User your test case. I tested your code from a DBA account and got the same error as you at first till I did the grant so you are on the money there.

Good luck, Kevin
Re: insufficient privs error [message #410265 is a reply to message #410222] Thu, 25 June 2009 22:54 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
> I believe roles are initially turned off when inside a plsql procedure so direct grants are needed to get many advanced things done.

A more correct statement is as follows:

Inside plsql procedures direct grants are needed to get things done.

CREATE USER ....
After this SQL is executed, a new schema/user exists, but it has no privileges to do ANYTHING.
It can not even login to DB.
New schema can not CREATE any object.
New schema can not run any code.
New schema is a helpless & empty shell.
Without any privileges it is next to useless.

Bottom line is, privileges acquired via ROLE do not apply within PL/SQL procedure.
Re: insufficient privs error [message #410271 is a reply to message #410265] Thu, 25 June 2009 23:05 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
no BlackSwan, your statement is not more correct.

Kevin
Re: insufficient privs error [message #410277 is a reply to message #410271] Thu, 25 June 2009 23:31 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
Kevin Meade wrote on Thu, 25 June 2009 21:05
no BlackSwan, your statement is not more correct.

Kevin


Bottom line is, privileges acquired via ROLE do not apply within PL/SQL procedure.

GRANT CREATE ANY TABLE is a superset of GRANT CREATE TABLE which OP is lacking & why error occurs
Re: insufficient privs error [message #410352 is a reply to message #410255] Fri, 26 June 2009 08:29 Go to previous messageGo to next message
cookiemonster
Messages: 12408
Registered: September 2008
Location: Rainy Manchester
Senior Member
Kevin Meade wrote on Fri, 26 June 2009 03:44
For example, I believe roles are initially turned off when inside a plsql procedure so direct grants are needed to get many advanced things done. Please do not ask me to explain as I do not know the actual works and I suspect this is a bug.


That's documented behaviour:

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/subprograms.htm#LNPLS00809

You'll never get oracle to accept that it's a bug.

Kevin Meade wrote on Fri, 26 June 2009 03:44

I would report this as a bug. You will notice that if you comment out the CREATE TABLE line, your procedure will proceed to drop the table EX. Strange that using EXECUTE IMMEDIATE one cannot create tables without system privileges, but one can drop them. This is a bug by any reasonable standard and it needs to be reported to Oracle support. User your test case. I tested your code from a DBA account and got the same error as you at first till I did the grant so you are on the money there.



Again that's documented, for tables:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9003.htm#sthref8747

If you own it you can drop it without any privileges, otherwise you need the appropriate drop any privilege
Re: insufficient privs error [message #410354 is a reply to message #410352] Fri, 26 June 2009 08:43 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Yep, it is all documented. Thanks for the links.

That does not mean we have to accept it as correct. But I too agree, I doubt Oracle will accept it as a bug. It makes no sense to me that one is not able to CREATE tables but can DROP them under the circumstances noted in this thread.

Seems to me the behavior of both commands should be the same. DROP TABLE should generate an insufficient privs error too.

Thanks again for the details.

Kevin
Re: insufficient privs error [message #410357 is a reply to message #410222] Fri, 26 June 2009 09:01 Go to previous messageGo to next message
cookiemonster
Messages: 12408
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's inconsistent certainly but there's always the backwards compatability argument.
If you force a restriction on something which previously didn't have one a lot of people will scream.
Re: insufficient privs error [message #410361 is a reply to message #410357] Fri, 26 June 2009 09:20 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Yep, this is true too. Decisions decisions.
Re: insufficient privs error [message #410374 is a reply to message #410222] Fri, 26 June 2009 11:59 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
CREATE TABLE privilege implicitly allows the table owner to INSERT, UPDATE, DELETE, DROP & TRUNCATE against any table they own.

No additional GRANT is necessary to allow DML or DDL against own table.

Oracle is implemented to allow table owner unfettered access to its own tables.
If a schema owns any object, this user is allowed any valid operation against the object.

While some may consider this to be "asymmetrical", it is what it is.
Re: insufficient privs error [message #410376 is a reply to message #410374] Fri, 26 June 2009 12:19 Go to previous message
cookiemonster
Messages: 12408
Registered: September 2008
Location: Rainy Manchester
Senior Member
BlackSwan wrote on Fri, 26 June 2009 17:59
CREATE TABLE privilege implicitly allows the table owner to INSERT, UPDATE, DELETE, DROP & TRUNCATE against any table they own.

No additional GRANT is necessary to allow DML or DDL against own table.



That's not actually true, you don't even need CREATE TABLE to do those things to objects in your own schema.
Previous Topic: Reverse Engineer Schema
Next Topic: Date ,NVL Query
Goto Forum:
  


Current Time: Tue Dec 06 08:45:39 CST 2016

Total time taken to generate the page: 0.11215 seconds