Re: Please help! ORA-01031: insufficient privileges

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