Please help! ORA-01031: insufficient privileges

[Syltrem]

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!
--

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]

On Mon, 10 Dec 2001 15:55:50 -0500, "Syltrem"
<syl...@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

[Syltrem]

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" <ora...@sybrandb.demon.nl> a écrit dans le message news:
h3ba1ugnl0rr381p1...@4ax.com...

[Syltrem]

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" <syl...@videotron.spammenot.ca> a écrit dans le message news:
xiaR7.3311$Q06....@tor-nn1.netcom.ca...

[Lubomir Petrov]

Hi,

Think for a while:
1) If you USE ALTER ANY INDEX privilege and USE roles you can
rebuild the index online.
2) If you USE ALTER ANY INDEX privilege and DO NOT USE roles you
CAN'T rebuild the index online.

Therefore, you need one of the privileges that is granted to this
roles. Where you can find them? In the dictionary, of course. Check
ROLE_SYS_PRIVS and ROLE_TAB_PRIVS to see which privileges are granted
to which roles. Now it is easy to find the answer...

Regards
Lyubomir Petrov

[Syltrem]

For my ALTER INDEX to work, I found that I have to GRANT CREATE ANY TABLE.

Now what sense does that make?

For every such command, the documentation should state the required
privileges. More so if they are not intuitive. Does such a documentation
exist? I could not find it.

Thanks!

--

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

"Lubomir Petrov" <lpe...@yahoo.com> a écrit dans le message news:
75805ad9.01121...@posting.google.com...