1 2 3 4 Previous Next 47 Replies Latest reply: Dec 21, 2011 11:29 AM by Patrick Wolf-Oracle Go to original post RSS
      • 30. Re: 4.1 POST_LOGIN question
        Christian Neumueller-Oracle
        Hi Vikas,

        I have good news and bad news, the good news first. We were extremely busy with cloud stuff, this delayed the one-off patch, but Patrick is reviewing my changes right now.

        The cause for this new behaviour you reported above puzzled me first, but I know what's going on now. As debug shows, APEX executes this call for the session sentry:
        begin
          declare
            function sentry_729125010984229125 return boolean is
            begin
              return page_sentry('IP'); return false;
            end;
            procedure post_auth_729125010984229125 is
            begin
              :G_POST_AUTH := 'Yes';
              :G_APP_USER := :APP_USER;
            end;
          begin
            wwv_flow.g_boolean := sentry_729125010984229125;
          end;
        end;
        After the call, the engine compares all bind variables' in value with their current memory value and tries to save session state if anything changed. Although the post_auth procedure never gets called, APEX binds the items it uses. Of course, :APP_USER changed during the call to the sentry function, so it tries to write it's value back to session state. However, this is not a real session item, but a convenient and read-only way to have access to apex_application.g_user. That's why the write back code fails.

        I'm sorry, you found another bug.

        APEX had this bind and write back behaviour for a long time. The 4.1 change of consolidating all authentication code in a big pl/sql block triggered it here and caused the bug.

        The obvious work around is to use apex_application.g_user or v('APP_USER'), as you mentioned. We are also thinking about changing the engine to never write back APP_USER (and a few other pseudo items), but this will still need a bit of research.

        Regards,
        Christian
        • 31. Re: 4.1 POST_LOGIN question
          VANJ
          Arie - Like I said, the code in question is in the Post Authentication block in the Custom Auth scheme and is exactly like I posted i.e. it reads :APP_USER and assigns it to an application-level item. Changing :APP_USER to v('APP_USER') indeed works but, like I said, I am not looking forward to changing this in dozens of applications. The earlier bug that Christian mentioned (change apex_040100.wwv_flow_authentications.attribute_03 from -BUILTIN- to NULL where SGID id is not 10) is something that, at my own risk, I am comfortable changing with a SQL DML statement. But this is a little more painful, involves manipualting a CLOB, hence my exasperation.
          • 32. Re: 4.1 POST_LOGIN question
            VANJ
            APEX had this bind and write back behaviour for a long time. The 4.1 change of consolidating all authentication code in a big pl/sql block triggered it here and caused the bug
            Yes, that's what I thought, thanks for the confirmation. Let me know when the patch is available.
            Although the post_auth procedure never gets called
            The Post Authentication Function attribute refers to a procedure, this could be a program unit in the database or, as is the case here, defined in the PL/SQL code in the authentication scheme. In either case, the post auth procedure is called. So I am not sure I understand this statement. Could you please clarify?

            I asked our DBA to run the following code after the 4.1 upgrade and it seems to have addressed both bugs. I am aware of Oracle's official policy against endorsing DML on internal APEX tables but if you do see something egregious, please let me know.
            exec wwv_flow.g_import_in_progress := true
            
            /* Bug #1 */
            update apex_040100.wwv_flow_authentications a set  
              attribute_03 = null 
            where attribute_03='-BUILTIN-' 
            and security_group_id = 634111608319703
            and exists  (select null from apex_040100.wwv_flows f where f.authentication_id = a.id);
            
            /* Bug #2. That’s 2 single-quotes NOT a double-quote */
            update apex_040100.wwv_flow_authentications a set   
              plsql_code = replace(plsql_code,':APP_USER','v(''APP_USER'')')
            where instr(plsql_code,':APP_USER') > 0 
            and  security_group_id = 634111608319703
            and exists  (select null from apex_040100.wwv_flows f where f.authentication_id = a.id);
            
            COMMIT;
            • 33. Re: 4.1 POST_LOGIN question
              Christian Neumueller-Oracle
              Hi Vikas,

              as a supported alternative, you could use SQL Workshop to create a shortcut link list with all authentications you want to change:
              begin
                for i in (
                      select
                          app.application_id,
                          app.application_name,
                          app.authentication_scheme_id
                      from
                          apex_applications app
                      order by
                          1
                ) loop
                  htp.p('<a href=''f?p=4000:4495:&SESSION.::::FB_FLOW_ID,F4000_P1_FLOW,P4495_ID:'||
                           i.application_id||','||i.application_id||','||
                           i.authentication_scheme_id||
                        ''' target="_new">'||i.application_id||'-'||sys.htf.escape_sc(i.application_name)||'</a>');
                end loop;
              end;
              (Note: I had problems pasting the href part, it should be enclosed in double quotes)

              Then it's just a matter of copy/paste to update all PL/SQL blocks.

              For easier maintenance (we already discussed this), you should consider referencing a master authentication from all your applications. Maybe it would not be that hard to do this bulk change via such a shortcut list, either...

              Regards,
              Christian
              • 34. Re: 4.1 POST_LOGIN question
                Christian Neumueller-Oracle
                >
                The Post Authentication Function attribute refers to a procedure, this could be a program unit in the database or, as is the case here, defined in the PL/SQL code in the authentication scheme. In either case, the post auth procedure is called. So I am not sure I understand this statement. Could you please clarify?
                >
                I was referring to post_auth_729125010984229125 in your authentication scheme, which does not get executed when APEX calls the session sentry, but whose bind variables still affect the APEX engine code which runs after the sentry completes.
                • 35. Re: 4.1 POST_LOGIN question
                  VANJ
                  Then it's just a matter of copy/paste to update all PL/SQL blocks.
                  Good idea, it definitely cuts down on the clicking in the Builder but for dozens of applications it is still cumbersome and error-prone, somehow I prefer my instant data zap, it seems to have done the trick.
                  you should consider referencing a master authentication from all your application
                  Yes, I see the benefits but somehow haven't committed to the whole subscription idea in APEX. Right now, each application is standalone. If various components subscribe to a master application, it would a) introduce dependencies, b) need to consider regression testing all applications when master application is changed and so on. I guess it just needs some more analysis but that's a topic for a different thread.

                  Thanks
                  • 36. Re: 4.1 POST_LOGIN question
                    Christian Neumueller-Oracle
                    Hi!

                    FYI, the one-off patch for the POST_LOGIN issue is finally out on Support.

                    Regards,
                    Christian
                    • 37. Re: 4.1 POST_LOGIN question
                      VANJ
                      Christian - One of our applications has some onload Javascript that does a submit based on some client-side conditions the first time the page is rendered, using something like
                      function initial_submit()
                      {
                          a = $v('P1_SUBMITTED');
                          b = $v('P1_FOO');
                      
                          if (b == 'Y' && (a == null || a == 'N'))
                          {
                            $s('P1_SUBMITTED','Y','Y');
                            apex.submit();
                          }
                      }
                      This seems to be confusing the custom authentication scheme and clearing out an application level item (F144_APP_USER) that was successfully set in the Post Auth section!

                      See the debug log below. FYI the application code does not have any references to g_unrecoverable_error

                      Yes, I agree that this can be redesigned many different ways but the fact remains that this was working fine in 4.0 and it doesn't in 4.1.

                      Is this another bug in the authentication framework?
                      S H O W: application="144" page="1" workspace="" request="" session=""
                      Language derived from: FLOW_PRIMARY_LANGUAGE, current browser language: en-us
                      alter session set nls_language="AMERICAN"
                      alter session set nls_territory="AMERICA"
                      NLS: CSV charset=WE8MSWIN1252
                      ...NLS: Set Decimal separator="."
                      ...NLS: Set NLS Group separator=","
                      ...NLS: Set g_nls_date_format="DD-MON-RR"
                      ...NLS: Set g_nls_timestamp_format="DD-MON-RR HH.MI.SSXFF AM"
                      ...NLS: Set g_nls_timestamp_tz_format="DD-MON-RR HH.MI.SSXFF AM TZR"
                      ...Setting session time_zone to -05:00
                      Setting NLS_DATE_FORMAT to application date format: MM/DD/YYYY
                      ...NLS: Set g_nls_date_format="MM/DD/YYYY"
                      ...NLS: Set g_nls_timestamp_format="DD-MON-RR HH.MI.SSXFF AM"
                      ...NLS: Set g_nls_timestamp_tz_format="DD-MON-RR HH.MI.SSXFF AM TZR"
                      NLS: Language=en-us
                      Application 144, Authentication: PLUGIN, Page Template: 3320304397607081
                      ...fetch session state from database
                      fetch items
                      ...fetched 0 session state items
                      Authentication check: NTLM (NATIVE_CUSTOM)
                      ...Execute Statement: begin declare
                      procedure post_auth_3327617450607108 is
                      begin
                      :F144_APP_USER := v('APP_USER');
                      end;
                      begin
                      wwv_flow.g_boolean := htmldb_public_user.modntlm_page_sentry;
                      end;
                      end;
                      Session created: 7610463736473488 user:TESTER
                      ...Session State: Save Item "FSP_AFTER_LOGIN_URL" newValue="f?p=144:1:7610463736473488::YES:::" "escape_on_input="Y"
                      ...delaying unrecoverable error to the end of execute_login
                      ...Session ID 7610463736473488 can be used
                      ...New Instance Detected - 
                      ... Authentication success
                      ...Execute Statement: begin declare
                      procedure post_auth_3327617450607108 is
                      begin
                      :F144_APP_USER := v('APP_USER');
                      end;
                      begin
                      post_auth_3327617450607108 ;
                      end;
                      end;
                      ...Session State: Save Item "F144_APP_USER" newValue="TESTER" "escape_on_input="Y"
                      ...Session State: Saved Item "FSP_AFTER_LOGIN_URL" New Value=""
                      Redirecting to f?p=144:1:7610463736473488::YES:::
                      ...setting g_unrecoverable_error:=true again
                      ...Session State: Saved Item "F144_APP_USER" New Value=""
                      Stop APEX Engine detected
                      Stop APEX Engine detected
                      Final commit
                      End Page Rendering
                      S H O W: application="144" page="1" workspace="" request="" session="7610463736473488"
                      Language derived from: FLOW_PRIMARY_LANGUAGE, current browser language: en-us
                      alter session set nls_language="AMERICAN"
                      alter session set nls_territory="AMERICA"
                      NLS: CSV charset=WE8MSWIN1252
                      ...NLS: Set Decimal separator="."
                      ...NLS: Set NLS Group separator=","
                      ...NLS: Set g_nls_date_format="DD-MON-RR"
                      ...NLS: Set g_nls_timestamp_format="DD-MON-RR HH.MI.SSXFF AM"
                      ...NLS: Set g_nls_timestamp_tz_format="DD-MON-RR HH.MI.SSXFF AM TZR"
                      ...Setting session time_zone to -05:00
                      Setting NLS_DATE_FORMAT to application date format: MM/DD/YYYY
                      ...NLS: Set g_nls_date_format="MM/DD/YYYY"
                      ...NLS: Set g_nls_timestamp_format="DD-MON-RR HH.MI.SSXFF AM"
                      ...NLS: Set g_nls_timestamp_tz_format="DD-MON-RR HH.MI.SSXFF AM TZR"
                      NLS: Language=en-us
                      Application 144, Authentication: PLUGIN, Page Template: 3320304397607081
                      ...fetch session state from database
                      fetch items
                      ...fetched 2 session state items
                      Authentication check: NTLM (NATIVE_CUSTOM)
                      ...Execute Statement: begin declare
                      procedure post_auth_3327617450607108 is
                      begin
                      :F144_APP_USER := v('APP_USER');
                      end;
                      begin
                      wwv_flow.g_boolean := htmldb_public_user.modntlm_page_sentry;
                      end;
                      end;
                      ... sentry+verification success
                      ...Session ID 7610463736473488 can be used
                      ...New Instance Detected - 
                      ...Application session: 7610463736473488, user=TESTER
                      ...Check for session expiration:
                      Session: Fetch session header information
                      Branch point: Before Header
                      Fetch application meta data
                      ...metadata, fetch computations
                      Computation point: On New Instance
                      ...metadata, fetch buttons
                      Setting NLS_DATE_FORMAT to application date format: MM/DD/YYYY
                      ...NLS: Set g_nls_date_format="MM/DD/YYYY"
                      ...NLS: Set g_nls_timestamp_format="DD-MON-RR HH.MI.SSXFF AM"
                      ...NLS: Set g_nls_timestamp_tz_format="DD-MON-RR HH.MI.SSXFF AM TZR"
                      ...New Session = True
                      Processes - point: AFTER_AUTHENTICATION
                      ...http header processing
                      Computation point: Before Header
                      Processes - point: BEFORE_HEADER
                      ...compatibility mode - do not set mime type
                      ...compatibility mode - do not set additional http headers
                      ...close http header
                      ...metadata, fetch item type settings
                      ...metadata, fetch items
                      Show page template header
                      ...[omitted]
                      v$sesstat.statistic# = 436: execute count=2275
                      Final commit
                      End Page Rendering
                      • 38. Re: 4.1 POST_LOGIN question
                        Christian Neumueller-Oracle
                        Hi Vikas,

                        this is the same "side effect through non-execution" phenomenon that I described above:

                        1. APEX builds the PL/SQL block to run the sentry function
                          begin declare
                            procedure post_auth_3327617450607108 is
                            begin
                              :F144_APP_USER := v('APP_USER');
                            end;
                            begin
                              wwv_flow.g_boolean := htmldb_public_user.modntlm_page_sentry;
                            end;
                          end;
                        2. it binds input parameters with their current session state - F144_APP_USER gets value null
                        3. it executes the block
                        4. the sentry function calls login
                        5. login calls the post auth procedure
                        6. APEX builds the PL/SQL block to run the post auth procedure
                          begin declare
                            procedure post_auth_3327617450607108 is
                            begin
                              :F144_APP_USER := v('APP_USER');
                            end;
                            begin
                              post_auth_3327617450607108 ;
                            end;
                          end;
                        7. it binds input parameters - F144_APP_USER gets value null
                        8. it executes the block
                        9. (after the post auth block) bind variable F144_APP_USER has a different out value ('TESTER') than the item's value (null) -> it saves 'TESTER' as session state
                        10. (after the sentry block) bind variable F144_APP_USER has a different out value (NULL!) than the item's value ('TESTER') -> it saves NULL as session state

                        Patrick and me just discussed the handling of bind variable OUT values. It's much cleaner if they would get compared to the IN values, instead of the current session state. We don't want to introduce a new bug, however. This has to be thought through.

                        Regards,
                        Christian
                        • 39. Re: 4.1 POST_LOGIN question
                          J. André
                          After upgrading from 4.0 to 4.1 and patching we still have the invalid login credentials message in a loop on de login page.
                          We have a main menu-application on which the user logs in. We have written our own athentication package for that and call
                          this function from a custom authentication scheme.
                          Other applications run using the menu. These applications use another custom authentication scheme in which we use a session not valid URL
                          and a logout url.
                          On page 101 of these applications we use a Dynamic action on page load to submit.
                          At that point the login should work like an open door.
                          It works clean in 4.0, but loops in the invalid login credentials message after upgrading to 4.1.
                          Any ideas?
                          • 40. Re: 4.1 POST_LOGIN question
                            VANJ
                            this is the same "side effect through non-execution" phenomenon that I described above:
                            Christian - Thanks.
                            It's much cleaner if they would get compared to the IN values, instead of the current session state
                            I agree (both with the idea and that more analysis is needed). As some Funny side effect using : to reference an application item in a page proc and Re: Session state not set until process completes? discussions show, the whole bind/run/scan for changes/set paradigm APEX uses is very confusing. It might be simpler to just process each bind variable reference to session state (both IN, OUT, assignments, etc) as and when it is encountered in the PL/SQL block, rather than after the fact. For instance, I am not sure how code like
                            :P1_X := :P1_Y;:P1_Y := 'foo';:P1_Z := :P1_X;
                            is processed, anyway you get the point.

                            1. Does the patch for bug 13045147 address this?

                            2. Does the page submit in the onload affect this? Prior to 4.1, I am not sure I saw SHOW being called twice for the first page request in a new session but maybe I never looked closely.

                            More importantly (assuming the answer to #1 above is No), is there a workaround for this bug?

                            Update: Answering my own question. Referring to session state using bind variable notation triggers this bug. So the workaround would be to use v and apex_util.set_session_state to read and write session state respectively.

                            Thanks
                            • 41. Re: 4.1 POST_LOGIN question
                              Christian Neumueller-Oracle
                              Hi J. Andre,

                              did you try out the one-off patch?

                              Regards,
                              Christian
                              • 42. Re: 4.1 POST_LOGIN question
                                J. André
                                Hi Christian, yes, this is after the one-off patch.
                                • 43. Re: 4.1 POST_LOGIN question
                                  Christian Neumueller-Oracle
                                  Hi J. Andre,

                                  can you give us more information about your authentication? Maybe create a test case on apex.oracle.com? It's hard to guess what's going on with the given information.

                                  Regards,
                                  Christian
                                  • 44. Re: 4.1 POST_LOGIN question
                                    J. André
                                    Hi Christian,
                                    I created the situation on apex.oracle.com and sent you the workspacename and login credentials by email.
                                    Kind regards,
                                    Joost