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 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..

----------------------------------------------------------------------
Previous Topic: Executing a procedure at sql prompt
Next Topic: Trigger - help!!!
Goto Forum:
  


Current Time: Thu Jun 04 00:19:34 CDT 2020