6 Replies Latest reply: Nov 15, 2012 1:25 PM by 775972 RSS

    insufficient privileges upon executing stored procedure

    775972
      Hello Gurus,
      i am new to plsql, working on a stored procedure.
      basically, trying to create a temporary table using dynamic sql .. below is my code,upon executing , i am encoutering insufficient privileges error. not sure where i am going wrong. any help is highly appreciated, thanks

      ---------code - successfully compiled----------
      SQL> CREATE OR REPLACE procedure stp_temp (i_table in varchar2)
      2 is
      3 table_creation_stmt varchar2(4000):='';
      4 begin
      5
      6 table_creation_stmt := 'CREATE GLOBAL TEMPORARY TABLE '||i_table|| ' AS select * from TABLE_STGG';
      7 dbms_output.put_line ('Query is :'||table_creation_stmt);
      8 execute immediate table_creation_stmt;
      9 end;
      10 /

      Procedure created.

      SQL> exec stp_temp('table123');
      Query is :CREATE GLOBAL TEMPORARY TABLE table123 AS select * from TABLE_STGG
      BEGIN stp_temp('table123'); END;

      *
      ERROR at line 1:
      ORA-01031: insufficient privileges
      ORA-06512: at "E3US9T.STP_TEMP", line 8
      ORA-06512: at line 1


      ------table gets created upon copy/paste from above output ( query is)

      SQL> CREATE GLOBAL TEMPORARY TABLE table123 AS select * from TABLE_STGG;

      Table created.
        • 1. Re: insufficient privileges upon executing stored procedure
          sb92075
          Sandeep Thakur wrote:
          Hello Gurus,
          i am new to plsql, working on a stored procedure.
          basically, trying to create a temporary table using dynamic sql .. below is my code,upon executing , i am encoutering insufficient privileges error. not sure where i am going wrong. any help is highly appreciated, thanks

          ---------code - successfully compiled----------
          SQL> CREATE OR REPLACE procedure stp_temp (i_table in varchar2)
          2 is
          3 table_creation_stmt varchar2(4000):='';
          4 begin
          5
          6 table_creation_stmt := 'CREATE GLOBAL TEMPORARY TABLE '||i_table|| ' AS select * from TABLE_STGG';
          7 dbms_output.put_line ('Query is :'||table_creation_stmt);
          8 execute immediate table_creation_stmt;
          9 end;
          10 /

          Procedure created.

          SQL> exec stp_temp('table123');
          Query is :CREATE GLOBAL TEMPORARY TABLE table123 AS select * from TABLE_STGG
          BEGIN stp_temp('table123'); END;

          *
          ERROR at line 1:
          ORA-01031: insufficient privileges
          ORA-06512: at "E3US9T.STP_TEMP", line 8
          ORA-06512: at line 1


          ------table gets created upon copy/paste from above output ( query is)

          SQL> CREATE GLOBAL TEMPORARY TABLE table123 AS select * from TABLE_STGG;

          Table created.
          privilege acquired via ROLE does NOT apply within named PL/SQL procedure

          direct GRANT is required.

          BTW - It is bad approach to dynamically create objects.
          All application objects should be static between software version releases & created from SQL maintained under version control repository.
          • 2. Re: insufficient privileges upon executing stored procedure
            SomeoneElse
            Why on earth are you creating a GTT inside a stored procedure?

            Create it once and be done with it.

            To answer your question, your create table privilege was granted to you via a role. Roles are not considered in pl/sql, you need the privilege granted to you directly.
            • 3. Re: insufficient privileges upon executing stored procedure
              millest99
              Is the privilege create table granted directly to the owner of the procedure or to a role granted to the owner?

              If it is granted to a role, try granting that privilege directly to the owner of the procedure.

              Stephen
              • 4. Re: insufficient privileges upon executing stored procedure
                rp0428
                Welcome to the forum!

                Whenever you post provide your 4 digit Oracle version
                >
                ------table gets created upon copy/paste from above output ( query is)
                >
                Then your problem of how to create the GTT is solved; create it using DDL.

                Your question has been ask many times in the past on the forum and it is usually ask by people with a sql server background where it is common and often necessary to create and use temporary tables.

                In Oracle it is seldom necessary to use a temporary table; a temp table is typically only needed and used in special circumstances. That is, use of a GTT is the exception, not the rule.

                If you have a problem query that needs tuned and you are trying to use a temp table to tune it you are likely taking the wrong approach. For a tuning request you should post the relevant information, query and DDL to a new thread in the sql and pl/sql forum.
                PL/SQL
                • 5. Re: insufficient privileges upon executing stored procedure
                  Mark Williams-Oracle
                  Sandeep Thakur wrote:
                  Hello Gurus,
                  i am new to plsql, working on a stored procedure.
                  basically, trying to create a temporary table using dynamic sql .. below is my code,upon executing , i am encoutering insufficient privileges error. not sure where i am going wrong. any help is highly appreciated, thanks

                  ---------code - successfully compiled----------
                  SQL> CREATE OR REPLACE procedure stp_temp (i_table in varchar2)
                  2 is
                  3 table_creation_stmt varchar2(4000):='';
                  4 begin
                  5
                  6 table_creation_stmt := 'CREATE GLOBAL TEMPORARY TABLE '||i_table|| ' AS select * from TABLE_STGG';
                  7 dbms_output.put_line ('Query is :'||table_creation_stmt);
                  8 execute immediate table_creation_stmt;
                  9 end;
                  10 /

                  Procedure created.

                  SQL> exec stp_temp('table123');
                  Query is :CREATE GLOBAL TEMPORARY TABLE table123 AS select * from TABLE_STGG
                  BEGIN stp_temp('table123'); END;

                  *
                  ERROR at line 1:
                  ORA-01031: insufficient privileges
                  ORA-06512: at "E3US9T.STP_TEMP", line 8
                  ORA-06512: at line 1


                  ------table gets created upon copy/paste from above output ( query is)

                  SQL> CREATE GLOBAL TEMPORARY TABLE table123 AS select * from TABLE_STGG;

                  Table created.
                  In addition to what others have posted you may also want to research SQL Injection.

                  For example what do you think will happen if I invoke your procedure as follows:
                  begin
                    stp_temp('bad_table on commit preserve rows as select * from all_users --');
                  end;
                  /
                  Is it what you want to have happen?

                  This is one reason why doing DDL in a stored procedure is a bad idea. Pay particular attention to what SomeoneElse and RP have to say too.

                  And, for the record, the SQL and PL/SQL forum is PL/SQL.
                  • 6. Re: insufficient privileges upon executing stored procedure
                    775972
                    Thank you all for your valuable inputs. after direct "create table" access my procedure works fine.
                    i agree its a bad idea to create GTT or have DDL in proc, but well, i failed to convince my folks and had to go through this route.

                    thanks again.
                    ST