Recently we implemented a third party software for Mobile users called Ventureforth Mi2k Server. After doing the install which creates a custom schema and creates packages which call Oracle API to insert, update, delete data into APPS, we got this error when they executed a test call:
ORA-01031: insufficient privileges
We had a tough time figuring out which object didn't have the grant. After a lot of trials through Toad debugging and tracing through DBMS_SYSTEM, I recalled that we could trap any error message in alert log and get more details about the error by using this command:
alter system set events '1031 trace name errorstack level 3';
Here 1031 is ORA-01031. You can put any error code in this command by removing the 0 prefix from the error code. Once you execute this command as SYS/SYSTEM, anytime ORA-1031 is issued by the database, the alert log will record this error and a trace file will be generated to give more details about the error.
Once we did this, we immediately came to know that it was failing when the API was calling UPDATE APPS.JTF_TASK_ALL_RESPONSIBILITIES which was a synonym to JTF.JTF_TASK_RESPONSIBILITIES. The custom schema had rights on JTF.JTF_TASK_RESPONSIBILITIES but not on JTF_TASK_ALL_RESPONSIBILITIES synonym. So we granted the rights:
grant all on APPS.JTF_TASK_ALL_RESPONSIBILITIES TO I2K;
After this the error disappeared.
Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps