| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: privileges and EXECUTE IMMEDIATE 'CREATE...'
A copy of this was sent to veraon_at_hotmail.com
(if that email address didn't require changing)
On Fri, 20 Aug 1999 01:58:22 GMT, you wrote:
>Hi, all!:
>I use SQL*Plus.
>In the same session I can create sequence if it is stand alone
>statement. But if I execute it dinamically in the procedure I get an
>answer that there is not enough privileges. You can see it from
>fragment of SQL*plus session below.
>Thank you for any idea.
>Vera
>
when using definers rights procedures (the only method in 8.0 and before and the default method in Oracle8i, release 8.1) roles are NEVER enabled during the execute a a stored procedure.
try this:
SQL> set role none;
Role set.
SQL> create sequence foo;
create sequence foo
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> set role all;
Role set.
SQL> create sequence foo;
Sequence created.
that shows my ability to create a sequence is coming from some role. I can either GRANT myself the ability to create sequence directly (and then you example will work) or I can:
SQL> create or replace procedure demo_proc
2 authid current_user <--------- ONLY FOR 8i, release 8.13 as
5 execute immediate 'create sequence foo'; 6 dbms_output.put_line( 'Sequence Created...' );7 end;
SQL>
SQL> exec demo_proc
Sequence Created...
PL/SQL procedure successfully completed.
SQL>
SQL> drop sequence foo;
Sequence dropped.
SQL>
SQL> set role none;
Role set.
SQL>
SQL> exec demo_proc
BEGIN demo_proc; END;
*
ERROR at line 1:
ORA-01031: insufficient privileges ORA-06512: at "TKYTE.DEMO_PROC", line 5 ORA-06512: at line 1
when this procedure executes, it executes with the privs of the USER invoking the procedure. When I run it -- if I have the ability to create a sequence -- it'll work. If I don't -- it won't. If someone ELSE other then me runs it -- It will only work if THEY can create a sequence (my privs are not used)...
>SQL> create sequence seq minvalue 1 maxvalue
>1000000000000000000000000000 increment by 1 start with 1;
>
>if i remove line #9 it works fine.
>
>SQL> edit
>Wrote file afiedt.buf
>
> 1 create or replace procedure seq_test
> 2 is
> 3 v_T integer;
> 4 begin
> 5 select count(1) into v_T from user_sequences where sequence_name
>= 'SEQ';
> 6 if v_T = 1 then
> 7 /* lock table THETABLE in exclusive mode;*/
> 8 EXECUTE IMMEDIATE 'drop sequence seq';
> 9 EXECUTE IMMEDIATE 'create sequence seq minvalue 1 maxvalue
>1000000000000000000000000000 increment by 1 start with 10;
> 10 commit;
> 11 else
> 12 dbms_output.put_line ('There is no sequence');
> 13 end if;
> 14* end;
>SQL> /
>
>Procedure created.
>
>SQL> exec seq_test
>begin seq_test; end;
>
>*
>ERROR at line 1:
>ORA-01031: insufficient privileges
>ORA-06512: at "RENTAGENT.SEQ_TEST", line 9
>ORA-06512: at line 1
>
>
>SQL> create sequence seq minvalue 1 maxvalue
>1000000000000000000000000000 increment by 1 start with 8;
>
>Sequence created.
>
>SQL>
>
>I removed line #9:
>
>SQL> edit
>Wrote file afiedt.buf
>
> 1 create or replace procedure seq_test
> 2 is
> 3 v_T integer;
> 4 begin
> 5 select count(1) into v_T from user_sequences where sequence_name
>= 'SEQ';
> 6 if v_T = 1 then
> 7 /*lock table THETABLE in exclusive mode;*/
> 8 EXECUTE IMMEDIATE 'drop sequence seq';
> 9 commit;
> 10 else
> 11 dbms_output.put_line ('There is no sequence');
> 12 end if;
> 13* end;
> 14 /
>
>Procedure created.
>
>SQL> exec seq_test
>
>PL/SQL procedure successfully completed.
>
>SQL>
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.
--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Aug 20 1999 - 07:23:46 CDT
 |
 |