Hi Everyone.
I am running EBS 11.5.10 on Oracle 10.2.0.3, and until recently all the bolt on's and custom applications were using the APPS user to run their daily jobs. We are working to correct this by giving them least privilege usernames, but do not want to force code changes. We decided to use synonyms to make the change transparent to the applications, but ran into some issues on only one app (which, unfortunately, is a big one). For some reason, when the java class uses a package body from the synonym, it returns errors.
Example Synonym creation:
CREATE OR REPLACE SYNONYM "OPS$TFNESERV"."GETS_B2B_102_INBOUND_PKG" FOR "APPS"."GETS_B2B_102_INBOUND_PKG";
{code}
In the database, the total objects related to this are:
{code:java}
select * from dba_objects where object_name = 'GETS_B2B_102_INBOUND_PKG'
OWNER OBJECT_NAME OBJECT_ID
---------------------------------------------------------------------------------
PUBLIC GETS_B2B_102_INBOUND_PKG SYNONYM
APPS GETS_B2B_102_INBOUND_PKG PACKAGE
APPS GETS_B2B_102_INBOUND_PKG PACKAGE
OPS$TFNESERV GETS_B2B_102_INBOUND_PKG SYNONYM
{code}
Grants include execute & debug on this object:
[code]
select * from dba_tab_privs where grantee like 'OPS$TFNESERV' AND table_name = 'GETS_B2B_102_INBOUND_PKG'
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE
-----------------------------------------------------------------------------------
OPS$TFNESERV APPS GETS_B2B_102_INBOUND_PKG APPS EXECUTE
OPS$TFNESERV APPS GETS_B2B_102_INBOUND_PKG APPS DEBUG
[/code]
The code is written without specifying the schemas, which shouldn't be an issue. Yet here are the various errors I see:
[code]
//Java command:
cstm = con.prepareCall("{call GETS_B2B_102_INBOUND_PKG.get_clob_message(?,?,?,?)}");
Gets_T1_T3_Inbound:**: SQL ERROR:T3:ORA-01031: insufficient privileges
//SQL
desc GETS_B2B_102_INBOUND_PKG;
ERROR:
------------------------------------------------------------------------------------
ERROR: object GETS_B2B_102_INBOUND_PKG:
APPS:GETS_B2B_102_INBOUND_PKG does not exist
desc apps.GETS_B2B_102_INBOUND_PKG;
PROCEDURE Argument Name Type
-----------------------------------------------------------------------------------------
//...data
[/code]
Am I missing some permission or definition? any help would be appreciated. Thanks!