Skip to Main Content
  • Questions

  • HELP REQUIRED with ORA-1031 in a procedure

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, PERTISTH.

Asked: May 26, 2000 - 9:19 am UTC

Last updated: April 11, 2002 - 8:35 am UTC

Version: 8i

Viewed 1000+ times

You Asked

Dear Sir
(1) Question related Dynamic Sql
----------------------------------------------------------
procedure create or replace create_mytable is
begin
execute immediate
'create table bonus (name number)';
end if;
----------------------------------------------------------
When I execute above procedure, then it gives the error
'ORA : 1031 insufficient system previlage'.

I am Creating and executing this procedute from system/manager user.What steps I have to perform to call this procedure without any errors.


(2) How can I call oracle database procedure/functions from D2K.

(Pertisth Mankotia)



and Tom said...

Either see
</code> http://asktom.oracle.com/Misc/RolesAndProcedures.html <code>

(roles are not enabled during the execution of a typical procedure)

or create the procedure with invokers rights....

Here is an example that shows hows this all works:


tkyte@OSI1.WORLD> create or replace procedure create_mytable
2 is
3 begin
4 execute immediate 'create table bonus (name number)';
5 end;
6 /
Procedure created.
That is your procedure as you wrote it basically...

tkyte@OSI1.WORLD> create table bonus (name number);
Table created.

tkyte@OSI1.WORLD> drop table bonus;
Table dropped.

So, the above shows I can in fact create a table directly in SQL

tkyte@OSI1.WORLD> exec create_myTable
BEGIN create_myTable; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "TKYTE.CREATE_MYTABLE", line 4
ORA-06512: at line 1

But apparently NOT in plsql. I could get myself granted "CREATE TABLE" (not via a role) to fix that (me, the owner of the procedure)


tkyte@OSI1.WORLD> set role none;
Role set.

tkyte@OSI1.WORLD> create table bonus (name number);
create table bonus (name number)
*
ERROR at line 1:
ORA-01031: insufficient privileges

That just proves that this is a role issue -- without roles, I cannot create a table

tkyte@OSI1.WORLD> set role all;
Role set.

tkyte@OSI1.WORLD> create or replace procedure create_mytable
2 AUTHID CURRENT_USER
3 is
4 begin
5 execute immediate 'create table bonus (name number)';
6 end;
7 /
Procedure created.

So, now we use invokers rights, thats the AUTHID CURRENT_USER statment above. Now the procedure will run with the privs of the user executing the query PLUS any and all roles they have

tkyte@OSI1.WORLD> exec create_myTable
PL/SQL procedure successfully completed.

and it works...




Rating

  (6 ratings)

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

Comments

1031 Solution Pin Pointed Exactly

Amol Joshi, May 07, 2001 - 2:44 am UTC

Thanks a lot for the solution with explanation :-)



How should I overcome this error

Benny, October 01, 2001 - 2:50 pm UTC

I have 2 schemas

1.test
2.test1

I have an object called daily_stock_price in test, and I have a synonym called test1_daily_stock_price , to access that object from test schema.

when Iam running describe or sql on test1_daily_stock_price , I am having no problems. But now
I want to use test1_daily_stock_price in one of my functions which Iam creating under schema test.

In this function Iam having a select on test1_daily_stock_price like

select distinct col1.col2 from
test1_daily_stock_price
where col3='1234567'

but Iam getting the pls-00201 identifier test1.daily_stock_price must be declared.

then I added the clause authid current_user to the function
but it did not make any difference to, it is still returning the same error.

Create or replace function fn_function(var number)
returning varchar2 is
authid current_user


var2 VARCHAR2(10);
var3 VARCHAR2(10);
var4 VARCHAR2(10);


BEGIN

select distinct col1.col2 INTO VAR2, VAR3 from
test1_daily_stock_price
where col3='1234567'

---

SOME PROCESSING

--

RETURN VAR4;

END;

How should I overcome this error.



Tom Kyte
October 01, 2001 - 2:56 pm UTC

I have a large chapter on this topic (invoker/definer rights) in my book if interested.

What I believe is happening here is that you are calling this function from a DEFINER rights routine. Have you tried calling it directly (without calling it from another Definer rights routine?) That should work.

The issue you are hitting is that as soon as you entered that definer rights routine, the effective userid changed and roles went away. Until you EXIT out of that definer rights procedure -- no roles. You are back at the beginning again.

Your solution -- GRANT SELECT on that base table to the owner of the function.

The function is not even compiling- why

Benny, October 01, 2001 - 3:04 pm UTC

test has select privileges on this test1 table, but still when I give authid current_user , the function is not compiling in test schema.

[1]: (Warning) PLS-00201: identifier 'test1.daily_stock_price
must be declared

Tom Kyte
October 01, 2001 - 8:28 pm UTC 

I can only suggest you typed something in WRONG somewhere.  Here is an example showing this works as expected:

ops$tkyte@ORA9I.WORLD> drop user test cascade;

User dropped.

ops$tkyte@ORA9I.WORLD> drop user test1 cascade;

User dropped.

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> create user test identified by test;

User created.

ops$tkyte@ORA9I.WORLD> grant create session, create table to test;

Grant succeeded.

ops$tkyte@ORA9I.WORLD> alter user test quota unlimited on users default tablespace users;

User altered.

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> create user test1 identified by test1;

User created.

ops$tkyte@ORA9I.WORLD> grant create session, create synonym, create procedure to test1;

Grant succeeded.

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> @connect test/test
ops$tkyte@ORA9I.WORLD> set termout off
test@ORA9I.WORLD> set termout on
test@ORA9I.WORLD> 
test@ORA9I.WORLD> create table daily_stock_price ( x int );

Table created.

test@ORA9I.WORLD> grant select on daily_stock_price to test1;

Grant succeeded.

test@ORA9I.WORLD> 
test@ORA9I.WORLD> @connect test1/test1
test@ORA9I.WORLD> set termout off
test1@ORA9I.WORLD> set termout on
test1@ORA9I.WORLD> 
test1@ORA9I.WORLD> create synonym test1_daily_stock_price for test.daily_stock_price;

Synonym created.

test1@ORA9I.WORLD> 
test1@ORA9I.WORLD> create or replace procedure p
  2  AUTHID CURRENT_USER
  3  as
  4  begin
  5          for x in ( select distinct * from test1_daily_stock_price )
  6          loop
  7                  null;
  8          end loop;
  9  end;
 10  /

Procedure created.

Try that in your own system. (hint: use my advice to "set role none" before describing the object and tell me what happens then)

Reader

Reader, October 01, 2001 - 3:51 pm UTC 

When I perform
sqlplus internal or sql>connect internal as sysdba

and
SQL>select * from session_roles
I get no rows
SQL>select granted_role from dba_role_privs where
    grantee = 'SYS';
I  get 14 roles
SQL>set role all
SQL>select * from session_roles;
I get 14 roles

Are the roles belonging to 'SYS' user automatically assigned
to the session when logged in as 'SYS'

Thanks

Tom Kyte
October 01, 2001 - 8:29 pm UTC

You are internal or sysdba at that point. It is special, its magical, its mystical. It has all powers, it needs no roles -- it has every privelege.

This one had me stumped

Roger Williams, April 11, 2002 - 8:35 am UTC

But this fixed the problem.
You are a god!

Wow!

Amit Nair, November 06, 2008 - 6:35 am UTC

I searched for about 1 hour before I got this...and I didn't have to search anymore!!!
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

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