insufficient privs error [message #410222] |
Thu, 25 June 2009 14:00  |
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 #410352 is a reply to message #410255] |
Fri, 26 June 2009 08:29   |
cookiemonster
Messages: 13963 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 #410357 is a reply to message #410222] |
Fri, 26 June 2009 09:01   |
cookiemonster
Messages: 13963 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 #410376 is a reply to message #410374] |
Fri, 26 June 2009 12:19  |
cookiemonster
Messages: 13963 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.
|
|
|