My suggestion to create a script to fetch package source from all_sources table and written in once file after that drop package from apps schema now create package in custom schema using file and create synonym.
What is the policy? Only your DBA's should have access to the APPS schema in Production so that should be no security issue, and in development, any sensitive data should be redacted as part of the clone process. Whilst theoretically any customisation is not supported on EBS, you are "less supported" if you do things away from the standards.
The policy is no custom code in APPS schema. All code has to be in the custom schema with appropriate synonyms in APPS.
According to coding standards, all custom objects except custom packages are installed in custom schema. Custom packages are installed in APPS. My question is since this is not allowed, packages also should be installed in custom schema - this means that a synonym is created in APPS. Are there any other privileges / grants that APPS needs for these custom packages to work fine.
What is that policy based on though? If the coding standards are that custom packages should reside in the APPS schema, then the policy should be "No custom code in the APPS schema apart from Packages". It sounds like the policy has been created to try and enforce the Apps standards, but whoever wrote it didn't realise that packages (and materialized views) should live in APPS.
You can create them in other schemas and grant/synonym to Apps - however it's not a supported method
I agree with you on the policy and that :
You can create them in other schemas and grant/synonym to Apps - however it's not a supported method.
My question is:
what are the grants that should be given to APPS for these custom packages installed in custom schema. Could you please list them out.
Appreciate the help.
The problem you're going to have is that your custom schema isn't going to have select permissions on any of the tables, execute permissions on any apps packages etc etc. So you have to then grant permissions on the tables (depending on what you want to do - will probably be just select), execute on the packages, then create synonyms in your custom schema pointing to the apps synonyms (or prefix everything with apps.table_name). then you'd have to compile the package and grant execute on it to apps.
Personally I'd say it was a security risk at best, and a disaster waiting to happen at worse.