5 Replies Latest reply on Jun 14, 2015 2:37 PM by Bashar.

    New database user that reads apps views

    Shareef Khaleel

      Dear All,

       

      For business needs; we created user xx_1 on our production database.

      Then using the apps user we created all required views needed to that user:

       

      create or replace view xx_customer_info as

      select hz.CUSTOMER_id, hz.CUSTOMER_NUMBER, hz.CUSTOMER_NAME

      from hz_customer_party_find_v hz

      where hz.customer_id > 0

      and hz.status = 'A'

      order by customer_id

       

      then grant select for the views from apps schema to that user:

      grant select on xx_customer_info to xx_1

       

      But user xx_1 can't read some views that based on tables with privilege security:

      select * from apps.xx_customer_info

      return 0 rows.

       

      As you know apps user needs to run the

       

      begin

      fnd_global.APPS_INITIALIZE(0,0,0);

      mo_global.init('AR');

      end;

       

      in order to view table rows...

       

      But for the new user; it is not applicable to run the above script.

       

      Needs your advise...

       

      Thanks in advance,

       

      Regards,

      Shareef

        • 1. Re: New database user that reads apps views
          Bashar.

          Hi,

           

          You must grant execute privilege on all underlying views and tables accessed by the fnd_global and mo_global packages and their dependencies, too.

           

          By the way, this discussion should have been posted in a different forum.

           

          Regards,

          Bashar

          • 2. Re: New database user that reads apps views
            Shareef Khaleel

            Dear Bashar,

            Thanks.

            1. Can you redirect this thread to it's appropriate forum.

            2. Can you advise me regarding how to do that; i made the following:

            grant execute on fnd_global to xx_1

            Grant execute on mo_global to xx_1

             

            It completed successfully, but when login in xx_1 user and tried to:

            begin

            fnd_global.APPS_INITIALIZE(0,0,0);

            mo_global.init('AR');

            end;

             

            the following errors are appeared:

            ERROR at line 2:

            ORA-06550: line 2, column 1:

            PLS-00201: identifier 'FND_GLOBAL.APPS_INITIALIZE' must be declared

            ORA-06550: line 2, column 1:

            PL/SQL: Statement ignored

            ORA-06550: line 3, column 1:

            PLS-00201: identifier 'MO_GLOBAL.INIT' must be declared

            ORA-06550: line 3, column 1:

            PL/SQL: Statement ignored.

             

            Checking oracle community, i found this thread:

            fnd_global.apps_initialize mo_global.set_org_context fail non APPS user

             

            and i can understand from reading Doc ID: 822225.1 that there is no way to achieve that in 12.1.2 and above release.

             

             

            Regards,

            Shareef

            • 3. Re: New database user that reads apps views

              privileges acquired via ROLE do NOT apply within named PL/SQL procedures.

               

              direct GRANT to object must be issued.

              • 4. Re: New database user that reads apps views
                Bashar.

                Hi,

                 

                Did you create synonyms for the packages in the xx_1 user?!

                 

                The MOS document you referenced is not very accurate. It handles the case from Oracle's point of view in order to be able to properly support you and make sure that everything is working fine.

                 

                Again, you must grant read (and possibly write) permissions on all tables/views accessed by the procedures used in those packages. I meant read/write, not execute in my first post!

                Likewise, if the procedures you are using call other procedures/functions in other packages then you must grant execute privilege on those packages and the underlying objects.

                 

                We got this working for non-APPS users by following the above method.

                 

                Regards,

                Bashar

                • 5. Re: New database user that reads apps views
                  Bashar.

                  By the way, I cannot move this discussion.

                  A moderator can do that but you can do it too if you open the discussion in a new tab/window.