Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2004
    Posts
    40

    Unanswered: Issue in executing EXECUTE IMMEDIATE statement

    Hi all,

    On executing the below given procedure, INSUFFICIENT PRIVILEGES error is thrown.

    CREATE OR REPLACE PROCEDURE EXECUTE_IMMEDIATE_1
    AS

    sql_stmt VARCHAR2(200);
    plsql_block VARCHAR2(500);
    emp_id NUMBER(4) := 1;
    salary NUMBER(7,2);
    dept_id NUMBER(2) := 50;
    dept_name VARCHAR2(14) := 'PERSONNEL';
    location VARCHAR2(13) := 'DALLAS';
    emp_rec emp%ROWTYPE;

    BEGIN
    EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';

    sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
    EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;

    sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
    EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;

    plsql_block := 'BEGIN emp_pkg.raise_salary(:id, :amt); END;';
    EXECUTE IMMEDIATE plsql_block USING 7788, 500;

    sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1
    RETURNING sal INTO :2';
    EXECUTE IMMEDIATE sql_stmt USING emp_id ;--RETURNING INTO salary;

    EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num'
    USING dept_id;

    EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';

    END;
    /

    The error is:

    BEGIN Execute_Immediate_1; END;

    *
    ERROR at line 1:
    ORA-01031: insufficient privileges
    ORA-06512: at "TESTDB.EXECUTE_IMMEDIATE_1", line 12
    ORA-06512: at line 1

    What is the issue in this script?

    Thanks,
    Kamesh.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Do you have CREATE TABLE privilege?

  3. #3
    Join Date
    Jul 2004
    Posts
    40
    Hi Adrew,

    CREATE TABLE PRIVILEGE has been created.

    cheers,
    Kamesh.

  4. #4
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137
    I don't think it's a good idea to create tables dinamically... at least use a temporary global table.

    Saludos.

  5. #5
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    CREATE TABLE PRIVILEGE has been created.
    Yes but has it been granted to the account, directly and not via a role?

    A good test is to disable all roles using SET ROLE NONE. This recreates the environment that will exist within your procedure.
    Code:
    SQL> CREATE TABLE bonus (id NUMBER, amt NUMBER);
    
    Table created.
    
    SQL> drop table bonus;
    
    Table dropped.
    
    SQL> SET ROLE NONE;
    
    Role set.
    
    SQL> CREATE TABLE bonus (id NUMBER, amt NUMBER);
    CREATE TABLE bonus (id NUMBER, amt NUMBER)
    *
    ERROR at line 1:
    ORA-01031: insufficient privileges

  6. #6
    Join Date
    Jul 2004
    Posts
    40
    Quote Originally Posted by WilliamR
    Yes but has it been granted to the account, directly and not via a role?

    A good test is to disable all roles using SET ROLE NONE. This recreates the environment that will exist within your procedure.
    Code:
    SQL> CREATE TABLE bonus (id NUMBER, amt NUMBER);
    
    Table created.
    
    SQL> drop table bonus;
    
    Table dropped.
    
    SQL> SET ROLE NONE;
    
    Role set.
    
    SQL> CREATE TABLE bonus (id NUMBER, amt NUMBER);
    CREATE TABLE bonus (id NUMBER, amt NUMBER)
    *
    ERROR at line 1:
    ORA-01031: insufficient privileges
    Hi Willam,

    It was granted to the user directly and not through a role.

    Cheers,
    Kartik.

  7. #7
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    If you try the same test (SQL*Plus, SET ROLE NONE, CREATE TABLE), what is the result?

  8. #8
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Have you ensured that the owner of the procedure has 'create table' privilege granted ? .. If not, something like this could have happened..
    Code:
    SQL@8i> create user x identified by x
      2  default tablespace users
      3  temporary tablespace users
      4  quota unlimited on users
      5  /
    
    User created.
    
    SQL@8i> grant create session, create procedure to x;
    
    Grant succeeded.
    
    SQL@8i> conn x/x
    Connected.
    SQL@8i> create or replace procedure xxx as
      2  begin
      3    execute immediate 'create table t ( a number )';
      4  end;
      5  /
    
    Procedure created.
    
    SQL@8i> exec xxx;
    BEGIN xxx; END;
    
    *
    ERROR at line 1:
    ORA-01031: insufficient privileges
    ORA-06512: at "X.XXX", line 3
    ORA-06512: at line 1
    
    
    SQL@8i> grant execute on xxx to jmartinez;
    
    Grant succeeded.
    
    SQL@8i> conn jmartinez
    Enter password: ******
    Connected.
    SQL@8i> exec x.xxx;
    BEGIN x.xxx; END;
    
    *
    ERROR at line 1:
    ORA-01031: insufficient privileges
    ORA-06512: at "X.XXX", line 3
    ORA-06512: at line 1
    Allthough my user ( jmartinez ) has create table privilege assigned to it, executing this procedure of x failed, since the procedure executes with the definer's rights.. you can change this instructing the procedure to execute with the privileges of the current_user.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •