5 Replies Latest reply on Oct 3, 2016 11:22 AM by John_K

    Synonym vs grant

    User478636

      This is a very common scenario. I'd like to know how this needs to be done properly or how everybody does and what is the Oracle recommended procedure. I have a couple of questions regarding Oracle applications R12 environment.

       

      I have a custom table in a custom schema. Do I need to create a synonym for that to use from APPS schema or just give grants? Can anyone tell me which of the following statements need to be executed? Which schema I need to run the grant statement from, if it needs to be run?

      CREATE TABLE cus.cus_table

      (   acc_id   NUMBER);

      CREATE OR REPLACE SYNONYM cus_table FOR cus.cus_table;

      GRANT ALL ON cus_table TO apps;

       

      How many custom schemas can be created to segregate the tables and access all of them from APPS? We have several integrations with different systems from Oracle Apps and want to limit the access to tables and separate the projects. Until now we have only one custom schema other than the seeded ones.

       

      Thank you so much for any suggestions.

        • 1. Re: Synonym vs grant
          Ssukhija-Oracle

          Hi

           

          You have to do both , create synonym as well as grant privileges.

           

          1)Grant all privilege from each custom data object to the APPS schema.

          For example : logged in as custom  user

           

           

          grant all privileges on <custom object> to apps;

           

          also

           

          Create a synonym in APPS for each custom data object

          For example : logged in as APPS user

           

           

          create synonym abc for customuser.abc;

           

          hope it clarifies

           

          thanks

          Shalini

          1 person found this helpful
          • 2. Re: Synonym vs grant
            1035002

            You may have to refer this note, which I believe is applicable to R12 as well, to integrate applications with ebs.

             

             

             

            Thanks!

            1 person found this helpful
            • 3. Re: Synonym vs grant
              John_K

              Your grant to apps should be with grant option then apps is used to grant any further privileges that are needed.

               

              Bear in mind that this changes in R12.2 to support online patching. I wrote up an example here (Table configuration in E-Business Suite R12.2 to support Online Patching - Beyond Blog ) a while back which explains (and gives examples of) how it's done prior to 12.2 and how it's done in R12.2 onwards. I.e.

               

              Before R12.2

               

              -- As your custom application user, i.e. XXJK.
              Create Table xxjk_demo (
                id Number,
                val Varchar2(100),
                a_field Varchar2(10)
              );
              
              Grant All On xxjk_demo To Apps With Grant Option;
              
              -- As Apps
              Create Synonym xxjk_demo For xxjk.xxjk_demo;
              -- Apps grants out any further privileges required.
              

               

              R12.2 onwards

               

              -- As your custom application user, i.e. XXJK.
              Create Table xxjk_demo (
                id Number,
                val Varchar2(100),
                a_field Varchar2(10)
              );
              
              Grant All On xxjk_demo To Apps With Grant Option;
              
              -- As Apps
              exec ad_zd_table.upgrade(x_table_owner=>'XXJK',x_table_name=>'XXDEMO')
              
              1 person found this helpful
              • 4. Re: Synonym vs grant
                User478636

                Thank you all for the replies. They are very helpful. Can anyone please answer my second question? How many custom schemas can be created to segregate the tables and access all of them from APPS? We have several integrations with different systems from Oracle Apps and want to limit the access to tables and separate the projects. Until now we have only one custom schema other than the seeded ones.

                Thanks you!

                • 5. Re: Synonym vs grant
                  John_K

                  You can create as many as you want. You do the same as above - grant/synonym. Some customers have one custom schema, some have one custom schema per application area (HR, GL etc), others different.

                  1 person found this helpful