6 Replies Latest reply on May 3, 2020 10:55 AM by Andy Haack

    Ownership and access to custom objects and integrations in R12.2


      Hi, we are upgrading to R12.2.9 from R12.1.3. Everything went smooth, and tommorow we will be testing the various "integration points" which involve custom schema. While I am somewhat familiar with R12.2 and the upgrade process itself, having done it before (consultants did the first upgrade here, before I joined the company), I am a bit confused on the integration part. So I know that upgrading involves registering your custom schema within EBS, and that you run readiness reports to find out what those custom shemas are. I understand that after the upgrade is complete, your custom schema are now also editioned (just like anything in APPS), and this way you can apply patches using adop, etc and you won't get into trouble.


      What I am confused about is - who owns the objects in those custom schemas  - after all is said and done? It seems like the object owner could still be (say) XXROW for an object like a table called XXROW.HRSUPPLEMENTALDATA. In that case, the upgrade involves giving APPS all privelidges to that ojbect so it can edition it - save for owning the object downright itself, correct? On the other hand, I was reading the Oracle R12.2 customization guide and it showed them creating a custom table, and it was owned by APPLSYS.


      Where this is leading is- tommorow, when we let some of our devleopers in, they are going to look at and/or test our interfaces. From getting emails of what they need, it sounds like they are used to operating on the APPS schemas directly - eg they want passwords for GL, INV,  PO, as well as passwords for their own custom schemas (e.g XXROW, etc). I also need to figure out if they should have the APPS pasword. I discussed this with my manager and I guess he hates giving out the apps password (this is a development environment, our first run of R12.2.9) - I guess in the past they have had problems, and he told me he would much rather give them passwords for particular schemas and we keep the apps password to ourself. However, I said I think in fact with R12.2.9 we may need to do the very opposite: ban these guys from accessing any schemas directly (e.g XXROW) and PARTICULARLY any base schemas in EBS like GL. We should just give out the APPS password and nothing else. Perhaps even create a read-only APPS schema also and whererver these guys can us that, have them use that. From my understanding, going through APPS is so important that these guys may even need to REDEVELOP the customizations for R12.2.9 whereby they query the object FROM APPS (e.g. select * from APPS..HRSUPPLEMENTALDATA) so that apps hits the custom table through the proper edition, with synonyms. Or am I wrong, could we continue to give them passwords to log into (at the very least) these custom schemas like XXROW and keep the apsp password secret.


      Thanks in advance for your insight and/or pointers. Marvin

        • 1. Re: Ownership and access to custom objects and integrations in R12.2

          As a developer and a Manager, regardless of being in 12.2.  It is much easier for the developer to have the apps password in your test environment.  Through our experience we have found deploying database objects such as packages, functions, and procedures is best done under the Apps schema rather than your custom schema.  We do create tables as the custom schema owner.  Deploying packages, procedures, and functions as your custom schema results in a lot of headaches getting the security sorted out plus accessing Oracle EBS's views and other data objects.(especially with editioning being a factor now).


          It seems like other documents I've read from Oracle support this methodology.


          Sure, keep your production apps password secure...and this might cause some headaches when you stand-up a new test instance...but it will make for happier and more productive developers, which is a good thing!

          • 2. Re: Ownership and access to custom objects and integrations in R12.2

            Thank you! Makes sense. So we should probably give the developers the apps password and ask them to do everything as apps and probably query and refer to the tables as apps.tablename (even re-write their queries to do this), correct? So we would only use the custom schema itself for creating the objects? And I am thinking they also have to be very careful to operate on what edition they are working on, right? It sounds like they could work on the run filesystem, and then I do adop phase=fs_clone to make sure the patch system gets updated. Are there any other times they would use the custom schema itself ?

            • 3. Re: Ownership and access to custom objects and integrations in R12.2

              We haven't ran into any issues with editions for our custom objects.  I'm a little confused about your reference to tables.  We *do* have the tables owned by our custom schema.  Sure, you can make a public synonym so you don't have to specify the schema name in any pl/sql objects (packages, procedures, functions) you create under the apps schema.  And those tables the apps schema has IDUS privileges.


              One side note...we are using ORDS to call Oracle from some custom php screens we are building.  For that set-up, we have enabled ORDS on our custom schema (we will not enable ORDS for the apps schema).  So for those pl/sql objects, they are owned by our custom schema but we have to compile and create them as apps (we use toad and sql developer for our pl/sql development)...again we haven't found editioning to be an issue.


              Do not take my word as Gospel...just letting you know our 'lay of the land' which has made development simpler and have not ran into editioning issues.

              • 4. Re: Ownership and access to custom objects and integrations in R12.2
                Sylvain Martel

                Hello 4129264,


                I will try to answer your first question in this post, even if dmillerp had offer some lights for you already.


                Based on my experience on EBS since 2001 and working with multiple APEX development on both R12.1.x and R12.2.x, here are a few random thoughts for you.

                • Regardless of the EBS version, you should always have custom tables, triggers and sequences on the custom schema and PL/SQL and views in APPS, as per Oracle long time development standards.  APPLSYS has nothing to do with custom development.
                • Developers should have access to the APPS password for development purpose in the DEV instance(s) (nowhere else!).  Having access to db user APPS, they would be able to create their objects in the custom schema(s) they need.  That's common pratice in the induustry and is supported by Oracle.
                • Prior to R12.2.x, you should have granted all privileges to custom tables to APPS with a private synonym in APPS to access the object without having to prefix all the time.  Public synonyms were a bad idea.

                Since R12.2.x:

                • Public synonyms are not allowed anymore.
                • Custom tables should be creating editioned views by using the ad_zd_table.upgrade API which will create the editioned view, grant priviliges to APPS and create a private synonym to APPS.
                • Custom triggers should be on the editioned view and not the base table
                • Use synonyms or views in APPS and nothing else to access data.  Not going through synonym is bypassing MOAC and a violation of data security.
                • In your DEV instances, you don't have to worry too much about the edition you are in (RUN or PATCH), since there is no need to install a patch as you are developing it!.  Being always RUN in DEV is the way to go.  Maybe the DBA will have a different opinion.  Deplying to your solution in TEST and QA instances will required to start a patching cycle.


                Hoping these thoughts will help you!

                • 5. Re: Ownership and access to custom objects and integrations in R12.2

                  Thank you for your most comprehensive answer!  I has become clear to me now after reading many of those docs and also speaking to current and former Oracle consultants that it is OK, and in fact RECOMMENDED by Oracle to have APPS be the owner of custom objects. With the caveat being - that they need to start with XX - so they can be distinguished from true or "genuine" or "OEM" code (to use a car parts analogy).


                  What I have come to learn, and why this has been a confusing process, is that some of our custom objects are not named starting with XX. Because in R12.1.3 all our custom objects resided in and were owned by  these shemas eg XXCOMPANYNAME, we could identify them as such. So we had an object named XXCOMPANYNAME.PO_PROCEDURE1. Now that it is moved to APPS, it is called APPS.PO_PROCEDURE1 and there is no way to tell that it "came from" XXCOMPANYNAME schema anymore. I think we need to rename it so that it is APPS.XXCOMPANYNAME_PROCEDURE1 .. but our developers don't want to do that. They do not want to change anything, just go live with "minimal" changes... arghgh

                  • 6. Re: Ownership and access to custom objects and integrations in R12.2
                    Andy Haack

                    As Silvain mentioned, having code in apps and tables, indexes, sequences in the xxcustom schema with private APPS synonyms to them is the recommeded way.

                    With regards to creation of editioning views: The 12.1.3 -> 12.2.x upgrade process automatically seems to call the ad_zd_table api and create editioning views ending with # and apps synonym for custom tables.

                    Even though Oracle recommends this approach, the editioning view layer introduces additional complexity for development and updates, e.g. if there are table definition changes, the editioning view needs to be updated as well. If you don't require different editions for your custom data, you might as well just keep the 'old' style of having an apps synonym pointing directly to a table in the custom schema (by removing automatically created editioning# views and their apps synonyms for custom tables again).


                    If you create a read only user for developer access to your prod system, you should also create a logon trigger to switch the current schema to APPS for this user. This allows running queries on EBS and custom tables without having to prefix table names with apps or the schema name (a prefix with schema name would be problematic anyway in case of editioned data).


                    create user apps_query identified by apps_query;


                    create or replace trigger apps.apps_query_logon_trg

                    after logon on apps_query.schema


                      execute immediate 'alter session set current_schema=apps';




                    grant connect to apps_query;

                    grant select any table to apps_query;

                    grant select any dictionary to apps_query;

                    grant select_catalog_role to apps_query;



                    for c in (select null from dba_objects do where do.owner='APPS' and do.object_name='XXEN_UTIL' and do.object_type='PACKAGE') loop

                      execute immediate 'grant execute on apps.xxen_util to apps_query';

                    end loop;