Monday, February 23, 2009

APEX SQL Workshop Insufficient Privileges Error

An APEX user was attempting to create a table in the SQL Workshop and received the "ORA-01031 insufficient privileges" error message. I went into SQL Developer and was able to create the table. So why did it not work in APEX SQL Workshop?

After some "data dictionary" digging comparing this schema to one that works, I realized that the CREATE TABLE privilege was granted to the schema via a role and not a direct grant. Since APEX runs within PL/SQL you have to have the CREATE TABLE grant issued directly to the schema to be able to create a table in APEX. You cannot grant the privilege via a database role.

I just thought that I would post this in case someone else runs into this problem and so I will have the answer when the problem comes up again.

2 comments:

Christian Rokitta said...

Hi Michael,

Most of the code put into APEX is executed using dynamic SQL. The Oracle security model requires all privileges (both system and object) for the code implemented using dynamic SQL to be granted explicitly. This cannot be done with roles.

Christian

Fury said...

Hi,

I'm newbie and I have exactly this issue.

Do I have to grant privilegies to the FLOWS_030100 schema? How exactly do I grant that privilegies?

Thank you very much,

Fury