ORA-01031: insufficient privileges when try to alter index in the stored procedure.


I have grappled with this nasty "NO-NO" for quite a while, just cannot seem to get it to work. My goal is simple, try to rebuild the index in a procedure, which requires me to embed the "ALTER INDEX" inside the dynamic SQL.

Basic info here: oracle9i database, LINUX AS 2.1 (i don't think it matters though).

What I have tried:
Log in as the SCHEMA OWNER.

1. I have tested by calling "alter index <index_name> rebuild online compute statistics" in the sqlplus window , it worked fine.
2. the roles and sys privs this accout have are:
ROLE: DBA, CONNECT, RESOURCE.
SYSTEM PRIVS: create any index, alter any index, create any indextype, alter any indextype.
3. Then everytime, I execute the "exec cmdb_util.rebuild_all_indexes" . it threw me off with the ORA-01031.

Don't explain to me that ROLE is disabled in the stored procedure because I have all index related system privileges directly assigned.

Anyone, please chime in.....


---------My code is below, it's pretty simple, you can test by just copying and pasting.
CREATE OR REPLACE PACKAGE cmdb_util
AS
TYPE g_ind_cursor_type IS REF CURSOR;
PROCEDURE rebuild_all_indexes;
END;
/


PROCEDURE rebuild_all_indexes
AS

l_ind_name VARCHAR2(30);
g_ind_cursor g_ind_cursor_type;
BEGIN
      OPEN g_ind_cursor FOR 'select index_name FROM user_indexes where index_name NOT LIKE '||chr(39)||'SYS_%'||chr(39);
      FETCH g_ind_cursor INTO l_ind_name;
      LOOP
            EXIT WHEN g_ind_cursor%NOTFOUND;      
            execute immediate 'ALTER INDEX '||l_ind_name||' REBUILD ONLINE COMPUTE STATISTICS';            
            FETCH g_ind_cursor INTO l_ind_name;
      END LOOP;
      CLOSE g_ind_cursor;
      
END;

END cmdb_util;
/
LVL 23
seazodiacAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsackerContract ERP Admin/ConsultantCommented:
You've got a good one ... here are a few admitted quirks which may or may not work for you ... not directly related to alter index, but something not unlike this happened to me on creating tables within a procedure:

*  Did you by chance have the 10032 event set? Yeah, I know ... sounds far-fetched ... but turning off the trace event sometimes helps.

*  If it works from an anonymous PL block, but not from a procedure, disconnect and reconnect, then try it again.  I'm sure you've done this, but am just going over a few "features" that have scratched the itch.

Just chiming in. :)
0
KongCommented:
Why not trap the exception and print out the index it is failing on and then try rebuilding that from your SQL*Plus login?

ie:

PROCEDURE
CURSOR c IS
   SELECT 'alter index ' || index_name || ' rebuild online ...' s
      FROM user_indexes
   WHERE ...;

stm VARCHAR2(2000);
BEGIN
   FOR r IN c
   LOOP
      stm := r.s;
      execute immediate stm;
   END LOOP;
EXCEPTION
   WHEN OTHERS THEN
      dbms_output.put_line('ERROR ...');
      dbms_output.put_line(stm);
END;

Just out of interest, why are you rebuilding all indexes? One would usually query index_stats table and build on an as required basis. You're putting your hand up for needless overhead & most likely inconsistent performance.

How many times have we heard: "This is taking a long time to run now and we haven't touched it...."

Just a bit of DBA gripe heh :-)

Good luck!

K
0
grim_toasterCommented:
Which user was the procedure created using?  If created under a different user to the one you are trying to run on, you will be accessing with the default definer rights access model, perhaps you would want to change this to invoker rights?  

PROCEDURE rebuild_all_indexes
AUTHID CURRENT_USER
AS
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Bigfam5Commented:
This might seem odd, but try

grant create any table to user;

As a side note  you could simply have the procedure

PROCEDURE rebuild_all_indexes  
AS
BEGIN
  for C_ind in (select index_name from user_indexes
                        where index_name NOT LIKE 'SYS_%' ) LOOP

      execute immediate 'ALTER INDEX '||C_Ind.Index_Name ||
                                  ' REBUILD ONLINE COMPUTE STATISTICS';
  END LOOP;
     
END;

0
DanielztCommented:
how may roles is the account in?
what's the default role for the account using for alter index?
did you use any proxy?
0
seazodiacAuthor Commented:
Sorry, I just got into my office ...

dsacker --->try both, but neither helped :-(
Kong ---> Believed, I have turned over every stoned I could spot, when I try to trapped which index(es) I bumped over, the results: None of them let me pass through. another fact is that the user account owns the tables as well as all indexes.

grim_toaster --->read my comment to Kong, then you know definer vs. invoker does not play a role here.

Bigfam5 --->Yes, I could do that (I will try that for sanity check and see if I could get around by that way). However, I would prefer to put all my procedures in one package since I have many more of them .

Danielzt: No, I am not using proxy , I directly log into the sqlplus window. if that's what you mean.


Any other ideas?

0
dsackerContract ERP Admin/ConsultantCommented:
seazodiac, build a .sql file then execute it from SQLPLUS, not unlike as follows:

/*  Obvious what these do   */

set newpage 0
set space 1
set linesize 5000
set pagesize 0
set echo off
set feedback off
set heading off
set termout off
set trimout on
set trimspool on

/*  Build the ALTER INDEXES .sql file   */

spool alter_indexes.sql;

prompt WHENEVER SQLERROR EXIT SQL.SQLCODE;;
prompt WHENEVER OSERROR  EXIT FAILURE;;

select 'ALTER INDEX '||index||' REBUILD ONLINE COMPUTE STATISTICS;'
from user_indexes
where index_name not like '||chr(39)||'SYS_%'||chr(39);

spool off;

/*  Turn back on which settings were turned off above  */

set blah, blah, blah ... on :)

/*  Execute the dynamically-built file and see if there's an index that's causing me to puke off  */

@alter_indexes
0
dsackerContract ERP Admin/ConsultantCommented:
The double semi-colons aren't by accident, as I'm sure you're aware. :)
0
dsackerContract ERP Admin/ConsultantCommented:
Correction:  ||index|| should be ||index_name||
0
seazodiacAuthor Commented:
dsacker:
thanks for the effort. It's my bad. I should point it out in the very beginning, that I need this to be done in stored procedure.
I used this script for the past.
0
dsackerContract ERP Admin/ConsultantCommented:
I suspected you did need it that way, as I sometimes need a stored procedure for things, too ... just wondered if it wasn't something beyond the 1031 error that perhaps would reveal itself in SQLPLUS, ie at the very table that could be the culprit or something like that.  Or did it execute flawlessly in your sqlplus .sql script in the past?
0
seazodiacAuthor Commented:
the script that alter all the indexes (the syntax are exactly the same as it appears in pl/sql) are working just fine .
0
dsackerContract ERP Admin/ConsultantCommented:
Another possibility, then I'm out of ideas :)

Could the stored procedure actually put an index in use while trying to rebuild it?  See some interesting comments at:

     http://www.dbazine.com/weeg16.shtml





0
Bigfam5Commented:
seazodiac,

Did you try to assign the system privilege  CREATE ANY TABLE


0
seazodiacAuthor Commented:
Yes.  i think I mention this account I used to run this rebuild index procedure is the schema owner.
0
KongCommented:
Hmmm maybe I missed something but did you grant create index explicitly to the user or was it inherited from a role. Try granting create index explicitly...

Can't figure out why else it wouldn't work good luck!
0
KongCommented:
Either grant explicitly the create index priv or run the procedure as invoker's rights:

procedure ...
authid current_user
IS
...

Roles are disabled in a definer rights (default) procedure
but they remain enabled in a invoker rights procedure.
0
KongCommented:
Ooops sorry, grim_toaster already mentioned invoker's rights...

Strange works for me on my 9i rel 2 databases:

CREATE TABLE foo (col1 NUMBER, col2 NUMBER);
CREATE INDEX foo_idx ON foo (col1);

CREATE OR REPLACE PACKAGE fob IS

  TYPE c_rt IS REF CURSOR;

  PROCEDURE do;

END fob;
/
CREATE OR REPLACE PACKAGE BODY fob IS

   
   PROCEDURE do IS
   
      c c_rt;
      n VARCHAR2(30);
   BEGIN
      OPEN c FOR 'SELECT index_name
                    FROM user_indexes
                   WHERE index_name = ''FOO_IDX''';
      FETCH c INTO n;
      EXECUTE IMMEDIATE 'ALTER INDEX ' || n || ' REBUILD ONLINE COMPUTE STATISTICS';
      CLOSE c;
   END;

END fob;
/
set serveroutput on lines 120
execute fob.do;
SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS') dttm
       ,TO_CHAR(last_analyzed, 'HH24:MI:SS') last_analyzed
  FROM user_indexes
 WHERE index_name = 'FOO_IDX';

drop table foo;
drop package fob;

--- Privileges granted to user:

select privilege from user_sys_privs;

select granted_role from user_role_privs;

Table created

Index created

Package created

Package body created

PL/SQL procedure successfully completed

DTTM     LAST_ANALYZED
-------- -------------
10:30:45 10:30:45

Table dropped

Package dropped

PRIVILEGE
----------------------------------------
CREATE VIEW
CREATE TABLE
ALTER SESSION
CREATE SESSION
CREATE SYNONYM
CREATE TRIGGER
CREATE ANY TYPE
CREATE SEQUENCE
CREATE SNAPSHOT
CREATE DIMENSION
CREATE INDEXTYPE
CREATE PROCEDURE
DROP ANY DIRECTORY
DROP PUBLIC SYNONYM
CREATE ANY DIRECTORY
CREATE DATABASE LINK
GLOBAL QUERY REWRITE
UNLIMITED TABLESPACE
CREATE PUBLIC SYNONYM
SELECT ANY DICTIONARY

GRANTED_ROLE
------------------------------
AQ_USER_ROLE
CONNECT
DW04AT_RO
RESOURCE
SELECT_CATALOG_ROLE

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
yorenCommented:
Maybe you're missing the "unlimited tablespace" privilege?
0
KongCommented:
Any luck? I'd be interested to hear how you went...

Yoren, "unlimited tablespace" is tagged onto the RESOURCE role.
0
yorenCommented:
Kong - I suggested it because roles don't take effect in stored procedures.
0
KongCommented:
True, but then we're going down the definer vs invoker's rights path which seazodiac doesn't agree with...
0
seazodiacAuthor Commented:
something really strange is going on here....

I think I exhaust almost all the means, but seems going no where...
I will give one last shot tomorrow, report back....
0
yorenCommented:
seazodiac, I think there is some weird behavior in this area. I tried a few tests on Oracle 8.1.7. It seems that no matter how you qualify the "alter index" statement, it actually tries to find the index in the schema of the procedure's DEFINER (weird, I know).

 The best workaround I can give you is to create this package in each schema you want to rebuild and then execute it as that user. Or, just keep doing what you're doing and run it from a script.
0
seazodiacAuthor Commented:
HI All:
I finally got it over with, but I took Kong's list of sys privs , just assigned them to the user account, and it worked. I am still puzzling what exaclty the sys privs I am missing here.

I split points between you guys....



0
NicholasSushkinCommented:
Most likely, the privilege you were missing was "GLOBAL QUERY REWRITE".
0
dude4084Commented:
I have to face the problem as well and searched it solution frm  the site below:

In summary:
CREATE ANY INDEX must be explicitly granted to your Oracle user, not via a role!



Site:  http://forums.oracle.com/forums/thread.jsp?forum=75&thread=249256&message=727594&q=20696e73756666696369656e742070726976696c65676573#727594
0
marist89Commented:
Interestingly enough, it's actually the CREATE TABLE priv that was missing.
0
marist89Commented:
Actually, it's both CREATE TABLE and UNLIMITED TABLESPACE that were missing.  I just did this procedure with RESOURCE role, CREATE SESSION, UNLIMITED TABLESPACE, and CREATE TABLE system privs.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.