Re: Please help! ORA-01031: insufficient privileges

OOPS! I forgot the SET ROLE NONE after RE-connecting in my previous post. It still does not work in SQLPLUS even after GRANTing ALTER ANY INDEX to tremblay.

What is the privilege I need? How to find out (for next time) as the message does not name the privilege required and the obvious thing is not what is required?

Thanks!

--

Syltrem
http://pages.infinit.net/syltrem (OpenVMS related web site - en français)
To reply to myself directly, remove .spammenot from my address

"Syltrem" <syltrem_at_videotron.spammenot.ca> a écrit dans le message news:
xiaR7.3311$Q06.23449_at_tor-nn1.netcom.ca...


> Thanks. It says there:

> "If you can do it in plus with no roles you can do it in a procedure.  "

>

> Seems like it's not always true.

> I tried SET ROLE NONE and indeed, I can't do it. I gave myself ALTER ANY

> INDEX and it works in SQLPLUS but not in the procedure.

>

> What privilege am I missing?

>

>

> SQL> connect tremblay

> Enter password:

> Connected.

> SQL> set role none;

>

> Role set.

>

> SQL>  alter index tremblay.idx_rebuild_info rebuild online;

>  alter index tremblay.idx_rebuild_info rebuild online

> *

> ERROR at line 1:

> ORA-00604: error occurred at recursive SQL level 1

> ORA-01031: insufficient privileges

> MITRCS Mon 10-DEC-2001 16:56:08 HELIOS::DGSI_A1:[TREMBLAY.ORACLE]

> ORACLE

>

>

> SQL> alter

> grant alter any index to tremblay;

>

> Grant succeeded.

>

> SQL> alter index tremblay.idx_rebuild_info rebuild online;

> alter index tremblay.idx_rebuild_info rebuild online

> *

> ERROR at line 1:

> ORA-00604: error occurred at recursive SQL level 1

> ORA-01031: insufficient privileges

>

> --> I figure I must reconnect.

>

> SQL> connect tremblay

> Enter password:

> Connected.

> SQL> alter index tremblay.idx_rebuild_info rebuild online;

>

> Index altered.

>

> SQL>

>

> Now with the procedure:

> SQL> exec idx_rebuild ('TREMBLAY');

> BEGIN idx_rebuild ('TREMBLAY'); END;

>

> *

> ERROR at line 1:

> ORA-01031: insufficient privileges

> ORA-06512: at "TREMBLAY.IDX_REBUILD", line 32

> ORA-06512: at line 1

>

> Still does not work.

>

> Help please!

>

>

> --

>

> Syltrem

> http://pages.infinit.net/syltrem (OpenVMS related web site - en français)

> To reply to myself directly, remove .spammenot from my address

>

> "Sybrand Bakker" <oradba_at_sybrandb.demon.nl> a écrit dans le message news:

> h3ba1ugnl0rr381p10il4vvmef6fq2sefg_at_4ax.com...

> > On Mon, 10 Dec 2001 15:55:50 -0500, "Syltrem"

> > <syltrem_at_videotron.spammenot.ca> wrote:

> >

> > >In a PL/SQL procedure created under user ABC I do:

> > >execute immediate 'alter index ABC.MY_INDEX rebuild online';

> > >And I get this:

> > >ERROR at line 1:

> > >ORA-01031: insufficient privileges

> > >ORA-06512: at "ABC.MY_PROCEDURE", line 33

> > >

> > >What is the problem? The table and the index are owned by ABC. I can do

> it

> > >at the SQL> prompt when connected as ABC or SYS.

> > >I know there is this problem about having privileges set by a role

> instead

> > >of a direct grant, but in this case the object belong to ABC and ABC

> > >executes the procedure. I have tried:

> > >grant all on idx_rebuild_info to sys;

> > >but that does not help either.

> > >

> > >What am I doing wrong?

> > >

> > >Thanks!

> >

> >

> > Please visit

> > http://osi.oracle.com/~tkyte/Misc/RolesAndProcedures.html

> >

> > Hth

> >

> >

> > Sybrand Bakker, Senior Oracle DBA

> >

> > To reply remove -verwijderdit from my e-mail address

>

>