• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 860
  • Last Modified:

Insufficient privileges to create table in PL/SQL

Hi,

I have the issue with creating a table from pl/sql stored procedure  (Oracle 9i).

When I am trying to create a table from a procedure I get the following error :

exec RecInitialize ( 'TableName' );

ORA-01031: insufficient privileges
ORA-06512: at "POSINT.RECINITIALIZE", line 82
ORA-06512: at line 1

But If I run the same statement from TOAD directly it works without any issues:

begin        
    execute immediate   'drop table TableName';
   
       
    execute immediate   'create table TableName
                        (
                            BUSINESS_DATE   date               null,
                            STORE_NUMBER    number  ( 22, 5  ) null,
                            ACCOUNT_TYPE    varchar2( 32     ) null,
                            GL_CODE         varchar2( 6      ) null,
                            LINE_NUMBER     varchar2( 32     ) null,
                            ACCOUNT_CODE    varchar2( 32     ) null,
                            AMOUNT          number  ( 22, 10 ) null          
                        )';
end;

The same login.

Thanks for any help.
0
GoodName
Asked:
GoodName
  • 2
1 Solution
 
YANN0SCommented:
Hello,
try adding
authid current_user in the procedure's declaration.
0
 
YANN0SCommented:
Actually a good explanation can be found in:
http://asktom.oracle.com/tkyte/Misc/RolesAndProcedures.html

If you have the required privileges through a role, (such as resource for example) you need to use invokers rights (i.e. AUTHID CURRENT_USER). The other option is to grant system privs (i.e CREATE TABLE or CREATE ANY TABLE) directly and not through a role.
0
 
GoodNameAuthor Commented:
Thanks! With authid current_user it works fine.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now