Re: Create sequence via DBMS SQL fails

From: Barbara Kennedy <barbken_at_teleport.com>
Date: Mon, 20 Nov 2000 07:17:25 -0800
Message-ID: <9fbS5.7970$pZ1.418613@nntp3.onemain.com>

I ran into this very problem and you also might give the user running the procedure the
create any sequence with admin.
I know it sounds stupid; you should not have to do that, but I ran into the same problem and that fixed it for me.

I ran into:

1. log in as myself.
2. issue a create sequence command.->works
3. write a pl/sql similar to below-> it compiled -> it would get a run time

error insufficient privs.
4. grant myself with admin on creating sequences. 5. run my pl/sql routine.-> works like a champ.

Yes, Sybrand is correct about the rights issue, but also see if this fixes it. I consider the behavior a bug.
Jim
<sybrandb_at_my-deja.com> wrote in message news:8vatb1$vnc$1_at_nnrp1.deja.com...
> In article <Sh6S5.41$p86.1890_at_news.get2net.dk>,
> "Jan Lund" <JANL_at_Lundbeck.Com> wrote:
> > Dear all.
> >
> > I have a problem using DBMS_SQL. I try to Drop a sequence, which
 works fine,
> > but when i try to re-create it, it fails with an "Insufficent
 Privleges"
> > error.
> >
> > What am i doing wrong ?
> >
> > Function1 works :
> > CID:=DBMS_SQL.OPEN_CURSOR;
> > DBMS_SQL.PARSE(CID,'DROP SEQUENCE PHARM_DB.TESTSEQ',
 DBMS_SQL.NATIVE);
> > RET:=DBMS_SQL.EXECUTE(CID);
> >
> > Function2 fails with an "Insufficent Privleges" error :
> >
> > CID:=DBMS_SQL.OPEN_CURSOR;
> > DBMS_SQL.PARSE(CID, 'CREATE SEQUENCE PHARM_DB.TESTSEQ
> > START WITH 0
> > INCREMENT BY 1
> > NOMAXVALUE
> > MINVALUE 0
> > NOCYCLE
> > NOCACHE
> > ORDER',DBMS_SQL.NATIVE);
> > RET:=DBMS_SQL.EXECUTE(CID);
> >
> > Rgds
> > Jan
> >
> >
> This is a FAQ. When you get the 'insufficient privileges' message, you
> have to be aware roles are ignored during compilation of a stored
> procedure (verify this by running the same code using an anonymous
> block and you will see it will work).
> You either need to grant create sequence or create any sequence
> privilege to the owner of the sp *directly*, or you need to run it (8i
> and higher) with invokers rights instead of definers rights (which is
> the default).
>
> Hth,
>
> --
> Sybrand Bakker, Oracle DBA
>
> All standard disclaimers apply
> ------------------------------------------------------------------------
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Mon Nov 20 2000 - 09:17:25 CST