1

I'm trying to create a temporary table in a procedure:

PROCEDURE pr_create_tmp_bp_table(fp_id NUMBER) IS
    tbl_name CONSTANT VARCHAR2(20) := 'BP_TO_DELETE';
BEGIN
    -- sanity checks removed for readablity

    EXECUTE IMMEDIATE 
        'CREATE GLOBAL TEMPORARY TABLE ' || tbl_name || ' ' ||
        'ON COMMIT PRESERVE ROWS AS ' ||
        'SELECT * FROM infop_stammdaten.bp';

END;

If I copy the BEGIN.._END block to a SQL worksheet everything works fine. So I think the user has the right to create a temporary table. If I execute the procedure from the same SQL worksheet I get

Fehlerbericht -
ORA-01031: Nicht ausreichende Berechtigungen
ORA-06512: in "INFOP_STAMMDATEN.PA_DELETE_FP", Zeile 16
ORA-06512: in Zeile 6
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a database operation without
           the necessary privileges.
*Action:   Ask your database administrator or designated security
           administrator to grant you the necessary privileges

Line 16 (Zeile 16) point to the EXECUTE IMMEDIATE statment that creates the temporary table.

To me this look like the user does not have the same rights in the sql work sheet and when it executes a procedure, also the procedure is in it's own schema.

Improve this question
1
  • You have to specify the following: 1. Do you run this with the same user, on the same database/container ? 2. If the user is the same - are you sure the statement passes and executes (in the worksheet)? 3. I see the error happening on line 16 of INFOP_STAMMDATEN.PA_DELETE_FP - is that the package that contains the procedure pr_create_tmp_bp_table ? 4. The most important - does your user have CRATE TABLE privilege? Commented May 25, 2018 at 6:18

1 Answer 1

Reset to default
5

The answer to your immediate question is that you get ORA-01031: insufficient privileges because your user has the CREATE TABLE privilege granted through a role: the Oracle security model enforces a rule that we can't use privileges granted through roles in PL/SQL. So you need your DBA to grant the CREATE TABLE privilege to your user directly.

Or do you?

Because what you are trying to do does not make sense in Oracle. In Oracle global temporary tables are permanent structures; it's just the data in them which is temporary. So, the correct solution is to build the table once with a normal DDL script, like any other database object. Then you can just insert into the global temporary table as you need to.

You are not the first person on this site to make this mistake (read this pertinent thread). Often it's because people are coming from another database such as SQL Server which has a construct called "temporary table" which is actually different from Oracle's global temporary tables. If that's your scenario then you will be interested in an Oracle 18c new feature called Private Temporary Tables. These are exactly analogous to SQL Server temporary tables. Find out more.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Ok, I tried GRANT CREATE TABLE TO INFOP_STAMMDATEN; as the shema user. Unfortunately I get ORA-01031: insufficient privileges again.
The user you are using doesn't have the WITH GRANT option so it's not allowed to pass on its CREATE TABLE privilege to other users. That's why I suggested you need to involve a DBA.
@BetaRide I'm not sure what you mean by GRANT CREATE TABLE TO INFOP_STAMMDATEN; as the schema user. Is the schema user INFOP_STAMMDATEN? You can't just grant system privileges to yourself, if that's what you're trying to do.
I have the same problem with truncating a table of another user. I granted DROP ANY TABLE to the user but it dint solve the problem. I still get insufficient privileges
@Amir - truncating a table which belongs to another user is a different problem. I suggest you ask a new question, providing as much detail as you can. Probably that question has already been answered on this site, but it's hard to tell without more information about exactly what you're trying to achieve.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.