Solved

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

Posted on 2003-10-30
29
36,014 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
  • 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

759 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now