Search code examples
plsqlprivileges

PLSQL : What privilege to create temporary table

I would like to create a temporary table in a procedure in a package depending to user A.

I write this : 

EXECUTE IMMEDIATE '
  CREATE GLOBAL TEMPORARY TABLE T (....)';

I have an error saying : 

ORA-01031 : Insufficient privilege

Selecting user_role_privs I can see that user A has this roles :

CONNECT
DBA
RESOURCE

What role, which is not contained in the previous ones, should I add to solve my problem?

Thanks

Solution

  • A role is no use in definer-rights PL/SQL. You will need create table privilege granted directly.

    Alternatively, do this in an invoker-rights package/procedure. However, invoker rights are best suited to 'generic utility' type code and not business data manipulation.

    Why do you need to create tables in procedures? Can't you just include all the tables you need in the build?