Showing results for 
Search instead for 
Do you mean 

GeoMedia, DBMS_WM.BEGINDDL returns error ORA-02224: EXECUTE privilege not allowed for tables

by Technical Evangelist on ‎02-18-2016 11:41 AM - edited on ‎04-06-2016 06:19 PM by Moderator (1,094 Views)

Symptoms

When attempting to make an alteration to a secured table using DBMS_WM.BeginDLL, running this Workspace Manager command returns the following error:

 

ERROR at line 1:
ORA-02224: EXECUTE privilege not allowed for tables
ORA-06512: at "WMSYS.LT", line 12136
ORA-06512: at line 1

 

Grant Execute is not used anywhere and a trace does not show any specific errors related to EXECUTE privilege.

Diagnosis

This is a bug in Oracle's Workspace Manager.  If you grant the ability to edit a table to a satellite user:

 

GRANT INSERT,UPDATE, DELETE ON POINTS_TABLE TO GTM_SAT;

 

Workspace Manager will automatically grant a number of other privileges to this user as well.  One of these is MERGE VIEW.   This privilege improves performance on spatial tables for user that do not own the table but the drawback is that BeginDLL will no longer function.  

Solution

To fix the problem, you need to revoked the MERGE VIEW privilege from the table:

 

REVOKE MERGE VIEW ON GTM_TEST.POINTS_TABLE FROM GTM_SAT;

 

This will allow BeginDLL to work correctly. 

 

When the BeginDLL process is completed (CommitDDL or DiscardDDL), you need to re-enable the MERGE VIEW privilege.

 

GRANT MERGE VIEW ON GTM_TEST.POINTS_TABLE FROM GTM_SAT;

 

Note: This was fixed in Oracle 11.2 and later.

Contributors