Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Create sequence via DBMS SQL fails

Re: Create sequence via DBMS SQL fails

From: Barbara Kennedy <barbken_at_teleport.com>
Date: Tue, 21 Nov 2000 07:18:14 -0800
Message-ID: <zmwS5.10078$rs4.700936@nntp2.onemain.com>

Okay perhaps it isn't a bug. I still don't understand how what you say has to do with the behavior I ran into. Specifically, I log in as user test.
User test has the grant to create sequences but not with the admin option. User test can do:
create sequence ...
and it works just fine.
User test creates a pl/sql procedure that uses dbms_sql to create a sequence.
User test runs the above procedure.
The procedure fails with insufficient privildges. User test is granted create sequences with the admin option. User test runs the above procedure and it runs fine.

So while I agree whole heartedly with your statement below I don't understand how it applies in the scenerio above. It just seems odd that I have to be granted create sequence with admin instead of just granted create sequence? Maybe I am missing something here. A couple of us at work couldn't figure out the reason why either.

Anyway thanks for taking the time to respond. Cheers,
Jim

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:974791258.892.0.pluto.d4ee154e_at_news.demon.nl...
> 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 Tue Nov 21 2000 - 09:18:14 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US