Re: Create sequence via DBMS SQL fails

Sorry to say so, but this behavior is definitely *NOT* a bug. It was implemented this way because roles are *volatile* and stored procedures are compile at *creation* time and not at *run* time. Evidently, as roles can change in the mean time, you can't guarantee a procedure compiled at date x will still run at date y.

I definitely wouldn't use the route you outline, you will end up with an unprotected, unsecured database.

Regards,

Sybrand Bakker, Oracle DBA

"Barbara Kennedy" <barbken_at_teleport.com> wrote in message news:9fbS5.7970$pZ1.418613_at_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 - 16:36:14 CST