Solved

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

Posted on 2003-10-30
29
36,513 Views
Last Modified: 2011-10-03

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;
/
0
Comment
Question by:seazodiac
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 6
  • +7
29 Comments
 
LVL 20

Expert Comment

by:dsacker
ID: 9655135
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
 
LVL 2

Expert Comment

by:Kong
ID: 9655466
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
 
LVL 7

Expert Comment

by:grim_toaster
ID: 9656392
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 7

Expert Comment

by:Bigfam5
ID: 9657075
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
 
LVL 8

Expert Comment

by:Danielzt
ID: 9657817
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
 
LVL 23

Author Comment

by:seazodiac
ID: 9658737
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
 
LVL 20

Assisted Solution

by:dsacker
dsacker earned 50 total points
ID: 9659213
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
 
LVL 20

Expert Comment

by:dsacker
ID: 9659215
The double semi-colons aren't by accident, as I'm sure you're aware. :)
0
 
LVL 20

Expert Comment

by:dsacker
ID: 9659231
Correction:  ||index|| should be ||index_name||
0
 
LVL 23

Author Comment

by:seazodiac
ID: 9659259
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
 
LVL 20

Expert Comment

by:dsacker
ID: 9659337
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
 
LVL 23

Author Comment

by:seazodiac
ID: 9659367
the script that alter all the indexes (the syntax are exactly the same as it appears in pl/sql) are working just fine .
0
 
LVL 20

Expert Comment

by:dsacker
ID: 9659429
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
 
LVL 7

Expert Comment

by:Bigfam5
ID: 9659620
seazodiac,

Did you try to assign the system privilege  CREATE ANY TABLE


0
 
LVL 23

Author Comment

by:seazodiac
ID: 9659631
Yes.  i think I mention this account I used to run this rebuild index procedure is the schema owner.
0
 
LVL 2

Expert Comment

by:Kong
ID: 9661140
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
 
LVL 2

Expert Comment

by:Kong
ID: 9661156
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
 
LVL 2

Accepted Solution

by:
Kong earned 150 total points
ID: 9667452
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
 
LVL 7

Expert Comment

by:yoren
ID: 9668129
Maybe you're missing the "unlimited tablespace" privilege?
0
 
LVL 2

Expert Comment

by:Kong
ID: 9683541
Any luck? I'd be interested to hear how you went...

Yoren, "unlimited tablespace" is tagged onto the RESOURCE role.
0
 
LVL 7

Expert Comment

by:yoren
ID: 9683599
Kong - I suggested it because roles don't take effect in stored procedures.
0
 
LVL 2

Expert Comment

by:Kong
ID: 9683638
True, but then we're going down the definer vs invoker's rights path which seazodiac doesn't agree with...
0
 
LVL 23

Author Comment

by:seazodiac
ID: 9684137
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
 
LVL 7

Assisted Solution

by:yoren
yoren earned 50 total points
ID: 9684781
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
 
LVL 23

Author Comment

by:seazodiac
ID: 9689743
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
 

Expert Comment

by:NicholasSushkin
ID: 10653484
Most likely, the privilege you were missing was "GLOBAL QUERY REWRITE".
0
 

Expert Comment

by:dude4084
ID: 11497112
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
 

Expert Comment

by:marist89
ID: 32994507
Interestingly enough, it's actually the CREATE TABLE priv that was missing.
0
 

Expert Comment

by:marist89
ID: 32994557
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

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question