Skip to Main Content
  • Questions
  • get ORA-01031: insufficient privileges when execute procedure

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Stephen.

Asked: July 13, 2016 - 7:59 am UTC

Last updated: September 05, 2022 - 5:36 am UTC

Version: oracle 11.2.0.4

Viewed 100K+ times! This question is

You Asked

Hello there:

I met the "ORA-01031: insufficient privileges" error, when rebuild index with online option in a procedure.

I know the role cannot be used in procedure, so grant some privileges to system(its DBA'

user:system
privilege:
-- 2 Roles for SYSTEM
GRANT AQ_ADMINISTRATOR_ROLE TO SYSTEM WITH ADMIN OPTION;
GRANT DBA TO SYSTEM WITH ADMIN OPTION;
ALTER USER SYSTEM DEFAULT ROLE ALL;

GRANT ALTER ANY INDEX TO SYSTEM;
GRANT ANALYZE ANY TO SYSTEM;
GRANT CREATE ANY TABLE TO SYSTEM;
GRANT CREATE MATERIALIZED VIEW TO SYSTEM;
GRANT CREATE SESSION TO SYSTEM;
GRANT CREATE TABLE TO SYSTEM;
GRANT DROP ANY TABLE TO SYSTEM;
GRANT EXECUTE ANY PROCEDURE TO SYSTEM;
GRANT EXECUTE ANY PROGRAM TO SYSTEM;
GRANT GLOBAL QUERY REWRITE TO SYSTEM;
GRANT SELECT ANY DICTIONARY TO SYSTEM;
GRANT SELECT ANY TABLE TO SYSTEM;
GRANT UNDER ANY TABLE TO SYSTEM;
GRANT UNLIMITED TABLESPACE TO SYSTEM WITH ADMIN OPTION;
-- 41 Object Privileges for SYSTEM
GRANT SELECT ON <owner.tablename> TO SYSTEM;


my procedure:
CREATE OR REPLACE PROCEDURE SYSTEM.RebuildIndexes IS
BEGIN
execute immediate 'alter index <owner.index> rebuild online';
END GatherIndexStats;
/

I can rebuild the index in sqlplus using system. but when I run the following:
SQL> BEGIN
2 SYSTEM.REBUILDINDEXES;
3 COMMIT;
4 END;
5 /
BEGIN
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYSTEM.REBUILDINDEXES", line 32
ORA-06512: at line 2


SQL>

I searched a lot, but still don't know what kind of privilege needed to grant to system.

any one can help?

thanks in advance.



and Chris said...

Don't create objects in system!

If you want an admin user to do maintenance operations like this, create your own. Then grant it the privileges the privileges you need it to have.

Anyway, on to your question. To execute commands in PL/SQL, you need to have the privileges granted directly. Not via a role.

So the owner of your procedure needs the "alter any index" privilege (or to own the index):

SQL> create user u identified by u;

User created.

SQL> grant connect to u;

Grant succeeded.

SQL> create table u.t (x int);

Table created.

SQL> create index u.i on u.t(x);

Index created.

SQL>
SQL> create or replace procedure p is
  2  begin
  3     execute immediate 'alter index u.i rebuild';
  4  end p;
  5  /

Procedure created.

SQL>
SQL> exec p;
BEGIN p; END;

*
ERROR at line 1:
ORA-01418: specified index does not exist
ORA-06512: at "CHRIS.P", line 3
ORA-06512: at line 1


SQL> alter index u.i rebuild;

Index altered.

SQL> conn sys/xxxx as sysdba
Connected.
SQL> grant alter any index to chris;

Grant succeeded.

SQL> conn chris/xxxx
Connected.
SQL> exec p;

PL/SQL procedure successfully completed.

SQL> drop user u cascade;

User dropped.


Also, it's worth asking. Why are you building a procedure to do this? Is rebuilding indexes really necessary?

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1860329900346402725
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:2913600659112

Rating

  (11 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Stephen Yang, July 13, 2016 - 9:44 am UTC


Stephen Yang, July 13, 2016 - 9:54 am UTC

thanks so much for your prompt response.

but I had granted 'alter any index to system'. it still did not work.


about rebuilding index, I only do that when the index size is large than table size. but I am under great pressure from project team. they ask me to rebuild indexes weekly. it is a shame for me to knuckle under.

I will try to create my owner user to do a test.
Chris Saxon
July 13, 2016 - 12:24 pm UTC

Point the project team to the links above ;)

Concept of Least Privilege

Rajeshwaran, Jeyabal, July 13, 2016 - 12:57 pm UTC

SQL> grant alter any index to chris;

Grant succeeded.

SQL> conn chris/xxxx
Connected.
SQL> exec p;

PL/SQL procedure successfully completed.


Once we grant 'ALTER ANY INDEX' to "chris" then "chris" gains the privilege to rebuild any index in all schema.

why not just grant the execute privilege on that procedure "P" to user "chirs" - that way user "chris" has only the privilege to execute the procedure ( and that is supposed to do , what is put inside it).

demo@ORA11G> create table t as select * from all_users;

Table created.

demo@ORA11G> create index t_idx on t(user_id);

Index created.

demo@ORA11G> create or replace procedure rebuild_index as
  2  begin
  3     execute immediate ' alter index t_idx rebuild ';
  4  end;
  5  /

Procedure created.

demo@ORA11G> grant execute on rebuild_index to scott;

Grant succeeded.

demo@ORA11G> conn scott/tiger@ora11g
Connected.
scott@ORA11G> alter index demo.t_idx rebuild ;
alter index demo.t_idx rebuild
*
ERROR at line 1:
ORA-01031: insufficient privileges


scott@ORA11G> exec demo.rebuild_index;

PL/SQL procedure successfully completed.

scott@ORA11G>

Chris Saxon
July 13, 2016 - 1:58 pm UTC

Once we grant 'ALTER ANY INDEX' to "chris" then "chris" gains the privilege to rebuild any index in all schema.

Yes. The poster was running this in SYSTEM, so I'm guessing that's what they wanted to be able to do!

For only granting privileges on the procedure to work, the procedure must be in the same schema as the index. Admittedly this is a better approach than using system!

it didn't work for the owner itself in Oracle 12c

Martin Goebbels, May 04, 2022 - 7:41 pm UTC

the mission was to create a table using a procedure

CREATE or replace PROCEDURE MYTEST
       
      IS
         strSQL varchar2(2000);
      BEGIN
          strSQL := 'CREATE TABLE X (X1 VARCHAR2(10), X2 NUMBER)';
                execute immediate strSQL ;
     END MYTEST;

BEGIN
    MYTEST;
END;

Error report -
ORA-01031: insufficient privileges
ORA-06512: at "MG67610.MYTEST", line 7
ORA-06512: at line 2
01031. 00000 -  "insufficient privileges"


the resulrt was the same insuficient privileges, although the user having create table rights and being the owner
PRIVILEGE                               
----------------------------------------
SET CONTAINER
CREATE TYPE
CREATE TRIGGER
CREATE PROCEDURE
CREATE SEQUENCE
CREATE VIEW
CREATE SYNONYM
CREATE TABLE
CREATE SESSION




Chris Saxon
May 09, 2022 - 2:33 pm UTC

How is the CREATE TABLE privilege gained? The user needs to be granted the privilege directly, not via a role.

ORA-01031: insufficient privileges

TAJAY, September 04, 2022 - 7:44 pm UTC

SQL> CREATE TABLE employees
2 ( employee_id number(10) NOT NULL,
3 employee_name varchar2(50) NOT NULL,
4 city varchar2(50),
5 CONSTRAINT employees_pk PRIMARY KEY (employee_id)
6 );
CREATE TABLE employees
*
ERROR at line 1:
ORA-01031: insufficient privileges
Connor McDonald
September 05, 2022 - 5:36 am UTC

grant CREATE TABLE to my_user;
alter user my_user quota 1G on users;

should get you going


HOW TO SET PRIVILEGES TO NEW USER

TAJAY, September 04, 2022 - 7:48 pm UTC

SQL> create table dept(
2 deptno number(2,0),
3 dname varchar2(14),
4 loc varchar2(13),
5 constraint pk_dept primary key (deptno)
6 );

ERROR at line 1:
ORA-01031: insufficient privileges

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library