3 Replies Latest reply: Oct 16, 2011 7:54 AM by Ahmed Shokry RSS

    FND_WEB_SEC.Validate_Login not working

      I need to grant the privilege on FND_WEB_SEC package of APPS schema to ITRACKER on one of my Apps11i instance dev1.ads.ad.
      Basically, I need to invoke the Validate_Login function which uses fnd_user table. Hence I had granted execute on APPS.FND_WEB_SEC to a user ITRACKER. Since this did not work, catching a look at the function made me realize that it weas intrinsically invoking FND_GLOBAL, FND_MESSAGE, FND_USER packages, and so i granted execute privilege on these packages too. Traversing through the functions called from within, I also granted execute privilege on FND_SESSION and FND_PROFILE packages. I also tried granting execute privs on sys.dbms_rls, but it does not help.
      Anyway, Im able to run this Validate_Login function from the Apps user in SQL Plus and it returns me the correct result. But trying to invoke the same from ITRACKER worker is not yielding me the desired result, its always returning N
      In contrast, we have a QUOTE user in another Apps11i instance dev01 and there its working fine, the custom application is able to invoke this Validate_Login function of FND_WEB_SEC, whereas this same is not working for ITRACKER in dev1. By the way, Ive granted all the sys privs accessible with QUOTE to the ITRACKER user and Ive ensured this by matching the privileges listed from user_role_privs as well as user_sys_privs.
      Will highly appreciate if You can let me know wot am I missing out exactly, or wot do i need to make FND_WEB_SEC.VALIDATE_LOGIN return me Y for any valid Applications user.
        • 1. Re: FND_WEB_SEC.Validate_Login not working
          The important thing to work out is why it is returning N - having a quick look at the function, there are at least half a dozen different reasons why it might return N. Before each "return 'N';" the function is setting a message (FND_Message.Set_Encoded...). As you have already granted execute on FND_Message, the following code will provide you with the reason why the answer is N (assuming you are running in SQL*Plus when logged on as the ITRACKER user):

          set serveroutput on
          v varchar2(1);
          v := apps.fnd_web_sec.validate_login('username', 'pwd');
          dbms_output.put_line('Valid? ' || v);
          dbms_output.put_line('Message : ' || apps.fnd_message.get);

          Given that this gave me:
          Valid? N
          Message : ORA-00942: table or view does not exist

          You will need to look at the tables and views that the QUOTE user has been granted access to (USER_TAB_PRIVS) that ITRACKER has not. If ITRACKER has all of the objects available, check what private synonyms QUOTE has that refer to Apps tables.
          • 2. Re: FND_WEB_SEC.Validate_Login not working
            Ahmed Shokry

            Same issue occurs with me !!

            Any suggested solutions will be highly appreciated.

            Best regards,
            Ahmed Shokry
            • 3. Re: FND_WEB_SEC.Validate_Login not working
              Ahmed Shokry
              Issue was solved by creating the following synonyms under the Required User :

              create synonym fnd_languages for apps.fnd_languages;
              create synonym fnd_user for apps.fnd_user;
              create synonym fnd_application for apps.fnd_application;
              create synonym fnd_lookup_types for apps.fnd_lookup_types;
              create synonym fnd_product_groups for apps.fnd_product_groups;
              create synonym fnd_web_sec for apps.fnd_web_sec;

              Best regards,
              Ahmed Shokry