In order to be supported by Oracle Support one has to create an xxcus schema where custom code resides. How can one get all the tables used by an Oracle API or procedure in order to do grants for the xxcus schema. The tables have to be individually granted to the custom xxcus schema to follow audit guidelines.
Is there some way in Oracle to determine the tables used by a procedure. This would also have to take into account any sub-procedures called by that procedure, since the Oracle EBS APIs call a lot. Or are these tables documented by Oracle somewhere (seems unlikely).
Are there metaviews that record what tables are used by a procedure and what sub-procedures are called by a procedure?
The approach recommended by Oracle is explained in their developer guide: https://docs.oracle.com/cd/E26401_01/doc.122/e22961/T302934T458264.htm#devg_access_ebsdb
The custom schema only holds the custom data objects such as tables and indexes, whereas code such as packages and views are created in the apps schema. For the custom tables, there is a synonym in the apps schema to make them avaiable for a call from the EBS application (connecting as apps).
If you need to call your custom code in the apps schema from the custom user instead of apps, then grant execution rights from apps to the custom user. Defining the code with AUTHID DEFINER pragma ensures that all objects owned by apps are accessible even when called from the custom user.