Home » SQL & PL/SQL » SQL & PL/SQL » Insufficient privileges?
Insufficient privileges? [message #36305] Thu, 15 November 2001 05:25 Go to next message
Nicola Farina
Messages: 63
Registered: October 2001
Member
Hi all,

I am trying to issue a DDL statement from PL/SQL, so I use the DBMS_SQL built in package.
The problem is: "I got the error ORA-01031 Insufficient privileges" on a CREATE TABLE statement.
I am logged with the user who OWNS this table.
Interactively (logged with the same user) I am able to issue the same statement without any problem.
What am I missing here??
TIA
Nicola
PS
Oops.. I forgot to say that I am using Oracle 7.3.4

----------------------------------------------------------------------
Re: Insufficient privileges? [message #36306 is a reply to message #36305] Thu, 15 November 2001 05:44 Go to previous messageGo to next message
Rob Baillie
Messages: 33
Registered: November 2001
Member
Can you issue any other SQL statements using DBMS_SQL?

If not, you may not have the correct privileges to use DBMS_SQL itself... it's unlikely it was installed under your user (unless you're logged in as SYS)

Rob

----------------------------------------------------------------------
Re: Insufficient privileges? [message #36308 is a reply to message #36305] Thu, 15 November 2001 07:50 Go to previous messageGo to next message
Hans
Messages: 42
Registered: September 2000
Member
You have to grant the "create table" privilege direct.
It's not sufficient that's this privilege is included within the "connect" role.
Please look at the script below.

connect test/test
 
-- example procedure from PL/SQL Programming, Oracle Press
create or replace procedure recreatetemptable (
  p_description in varchar2) is
 
  v_cursor        number;
  v_createstring  varchar2(100);
  v_dropstring    varchar2(100);
  v_numrows       integer; 
begin
  v_cursor := dbms_sql.open_cursor;
 
  v_dropstring := 'drop table temp_table';
 
  begin
    dbms_sql.parse(v_cursor, v_dropstring, dbms_sql.v7);
    v_numrows := dbms_sql.execute(v_cursor);
  exception
    when others then
      if sqlcode != -942 then
        raise;
      end if;
  end;
 
  v_createstring := 'create table temp_table ' || p_description;
  dbms_sql.parse(v_cursor, v_createstring, dbms_sql.v7);
  v_numrows := dbms_sql.execute(v_cursor);
 
  dbms_sql.close_cursor(v_cursor);
exception
  when others then
    dbms_sql.close_cursor(v_cursor);
    raise;
end recreatetemptable;
/
 
execute recreatetemptable('(id number,name varchar2(30)) ');
 
<b>
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "TEST.RECREATETEMPTABLE", line 31
ORA-06512: at line 1
</b>
 
 
 
connect system/manager
 
grant create table to test;
grant drop any table to test;
 
 
 
connect test/test
 
execute recreatetemptable('(id number,name varchar2(30)) ');
<b>
PL/SQL procedure successfully completed.
</b>



----------------------------------------------------------------------
Re: Insufficient privileges? [message #36314 is a reply to message #36305] Thu, 15 November 2001 23:02 Go to previous messageGo to next message
Nicola Farina
Messages: 63
Registered: October 2001
Member
Thank you very much for your answer!
I understand the problem, but my concern is about modify the SYS privileges.
It doesn't seem to me a very good idea to modify standard user privileges only for running a DBMS_SQL function.. (each time I deploy this program do I need to modify SYS grants of the customer???)
May be this is a problem of *my* Oracle server setup..

----------------------------------------------------------------------
Re: Insufficient privileges? [message #683235 is a reply to message #36308] Fri, 18 December 2020 00:25 Go to previous messageGo to next message
starmalidu
Messages: 1
Registered: December 2020
Junior Member
Thank you very much. This is the correct answer, I have faced the issue while creating function and Resource user privilege is not enough. I gave the grant "create procedure to <user>" privilege too.
Re: Insufficient privileges? [message #683236 is a reply to message #683235] Fri, 18 December 2020 01:25 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

RESOURCE is NOT a privilege it is a role kept ONLY for backward compatibility and you should NEVER grant it to anyone.

Previous Topic: Mix of Cross and Full Outer Join
Next Topic: ORA 08103 - object no longer exists referring a temporary table
Goto Forum:
  


Current Time: Thu Mar 28 14:49:11 CDT 2024