1 2 Previous Next 16 Replies Latest reply: Nov 12, 2010 8:15 AM by Udo RSS

    Apex 4 About Database login do not work

    jariola
      Hi,

      I did install Apex 4.0.1 to XE database. When I go
      Home > SQL Workshop > Utilities > About Database
      and try login using database user having DBA role, SYS or SYSTEM user, it do not work.

      Page is submited, but I just end up same login page. There is no any notification.

      If I use above user and enter wrong password, then I get
      >
      1 error has occurred
      Incorrect password or user does not have required role.
      >

      Page info says
      >
      Enter the credentials for a database account that has been granted the DBA role.
      >
      From that I understand I have try use correct loging details.
      I have also changed all above user passwords, to be sure I have correct password.

      Does someone else have this problem in other version of database than XE ?

      Regards,
      Jari

      Edited by: jarola on Nov 12, 2010 12:48 AM
        • 1. Re: BUG: Apex 4 About Database login do not work
          Udo
          Hi Jari,

          I'm able to reproduce this on a "Oracle Database 11g Enterprise Edition 11.2.0.1.0 64bit Production" in our development area, running APEX 4.0.1.00.03 (upgraded).

          -Udo
          • 2. Re: BUG: Apex 4 About Database login do not work
            joelkallman-Oracle
            Interesting. apex.oracle.com was recently upgraded to 11.2.0.2 not too long ago. I don't have this issue there at all.

            Don't be offended by this question, but are you sure you are entering the correct case-sensitive password?

            Joel
            • 3. Re: BUG: Apex 4 About Database login do not work
              Udo
              Hi Joel,

              I used an account with an "all-lower" password. I redid the test with a newly created user using the following commands (exact copy from SQL Developer) to create it:
              create user testudo identified by testudo;
              grant connect, dba to testudo;
              Then I went to the corresponding APEX-instance and tried to login with
              testudo
              testudo
              Result: I'm redirected to the login page with no error message, as Jari sees in XE. This happens in both IE7 and FF 3.6.
              After that, I also granted APEX_ADMINISTRATOR_ROLE to the user and assigned the schema to the workspace from where I wanted to call the view. No change.
              Using the same parameters to connect in SQL Developer worked and a query on user_role_privs showed the expected result.
              Using a wrong password results, like Jari already stated, the correct error message:
              1 error has occurred
              
                  * Incorrect password or user does not have required role.
              I also looked into the post values with firebug and saw that I did enter the correct password.

              -Udo
              • 4. Re: BUG: Apex 4 About Database login do not work
                joelkallman-Oracle
                Hi Udo,

                Hope you didn't take offense to my question. I knew this was something you would not have overlooked, but I have to ask the obvious.

                So let's do this, just to determine if there is something in this specific platform that Oracle is on. Can you connect as SYS and run the following code (where you have APEX 4 installed)? It won't change anything on your DB.
                alter session set current_schema = apex_040000;
                
                variable P1_USERNAME varchar2(30)
                variable P1_PASSWORD varchar2(30)
                
                begin
                    :P1_USERNAME := 'sys';
                    :P1_PASSWORD := 'your_case_sensitive_sys_password';
                end;
                /
                
                
                
                
                declare 
                    l_auth boolean := false;
                begin
                    l_auth := sys.wwv_flow_val.verify_user(
                        username => :P1_USERNAME,
                        password => :P1_PASSWORD) = sys.wwv_flow_val.valid_user;
                
                    if l_auth then
                        dbms_output.put_line('Authenticated');
                    else
                        dbms_output.put_line('NOT Authenticated');
                    end if;
                
                    if wwv_flow_login.user_is_dba(p_username => :P1_USERNAME) then
                        dbms_output.put_line('Is a DBA');
                    else
                        dbms_output.put_line('Is NOT a DBA');
                    end if;
                end;
                /
                If everything works as expected, it should print:
                Authenticated
                Is a DBA
                Obviously, you'll need to plug in your own username and password here.

                Joel
                • 5. Re: BUG: Apex 4 About Database login do not work
                  Udo
                  Hi Joel,

                  no, I understand you have to be sure. I also had situations were we were running through some details because I didn't expect an obvious fault, but such things do happen.

                  I ran that script for both sys and the test user mentioned above. Both times with the same result:
                  Authenticated
                  Is a DBA
                  I get the same result for sys on my XE running the current APEX version, where I can reproduce this behavoiur as well.
                  To be sure there is no other side effect keeping that answer, I tried the other cases:
                  (non-dba with correct password)
                  Authenticated
                  Is NOT a DBA
                  (non-dba with incorrect password)
                  NOT Authenticated
                  Is NOT a DBA
                  (dba with incorrect password}
                  NOT Authenticated
                  Is a DBA
                  Which seems to prove that we don't have a general authentication problem (we would see the error message otherwhise), but possibly a problem with the redirect or some value to be passed correctly. I'm currently pointing at the redirect, as I get two 302 where I would expect only one, before I'm redirected to the login page again. See the following log of the OHS in front of the 11gR2:
                  192.168.0.53 - APEX_PUBLIC_USER [11/Nov/2010:01:39:33 +0100] "POST /pls/apex/wwv_flow.accept HTTP/1.1" 302 42
                  192.168.0.53 - APEX_PUBLIC_USER [11/Nov/2010:01:39:33 +0100] "GET /pls/apex/f?p=4500:36:507675735006036 HTTP/1.1" 302 5
                  192.168.0.53 - APEX_PUBLIC_USER [11/Nov/2010:01:39:33 +0100] "GET /pls/apex/f?p=4500:DBA_LOGIN:507675735006036:4500,36:NO HTTP/1.1" 200 16535
                  507675735006036 is the correct session number. I get the same results in firebug for the XE instance.
                  From what I know, I should get page 36 instead of a further redirect from there.
                  Just shooting on what I can see...

                  -Udo
                  • 6. Re: BUG: Apex 4 About Database login do not work
                    joelkallman-Oracle
                    Well, I'm officially baffled. I had no issues with this on XE (10.2.0.1) on OEL 5 nor 11.2.0.2 on OEL5.

                    I traced the requests with Firebug and after I entered my credentials, my POST succeeded with a status 302 and then the subsequent GET of 4500:36 happened with an HTTP status 200. APEX is behaving like invalid credentials were entered, I'm sorry to say. Are there any non-ASCII alphabetic characters in the username or password? Is there any chance that there is character set conversion between your browser and the APEX engine?

                    I realize I'm asking far-fetched questions, but I'm really out of ideas.

                    Joel
                    • 7. Re: BUG: Apex 4 About Database login do not work
                      jariola
                      Hi,


                      I did make my install to Ubuntu 10.4 server, running on XP host VirtualBox.
                      Then I did install XE from Oracle repository and upgrade Apex.

                      I know this config is something that is not supported at all. Sorry....

                      But if Udo have same issue in 11G.... It would be nice if someone else could share experiences about this topic !


                      Regards,
                      Jari

                      Edited by: jarola on Nov 11, 2010 11:23 PM

                      All users and SYS , SYSTEM I did use password "oracle" and then "test123"
                      Same result to all users
                      • 8. Re: BUG: Apex 4 About Database login do not work
                        725793
                        Hi Jari,

                        I am unable to replicate your issue.

                        At first instant I tried logging in as outlined in your first post. when i thought the password was oracle (but I must have been mistaken). So I altered the password and it logged in no trouble.

                        I then went on to create a new user with the dba and connect priv and he was also able to log in.
                        SQL> create user new_dba identified by new_dba;
                        
                        User created.
                        
                        SQL> grant connect,dba to new_dba;
                        
                        Grant succeeded.
                        As mentioned, I can then log into Home > SQL Workshop > Utilities > About Database without any trouble.
                        SQL> select *
                          2  from v$version;
                        
                        BANNER
                        ----------------------------------------------------------------
                        Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
                        PL/SQL Release 10.2.0.1.0 - Production
                        CORE     10.2.0.1.0     Production
                        TNS for Linux: Version 10.2.0.1.0 - Production
                        NLSRTL Version 10.2.0.1.0 - Production
                        (installed from RPM available on Oracle Software Downloads)
                        # uname -a
                        Linux galeru 2.6.18-194.17.1.el5 #1 SMP Wed Sep 29 12:51:33 EDT 2010 i686 i686 i386 GNU/Linux
                        # cat /etc/redhat-release
                        CentOS release 5.5 (Final)
                        Ta,
                        Trent
                        • 9. Apex 4 About Database login do not work
                          jariola
                          Hi,


                          Thank you Trent.

                          Strange if Udo did have same problem as I have ?


                          Regards,
                          Jari
                          • 10. Re: Apex 4 About Database login do not work
                            725793
                            Hi Jari,

                            This may be a stupid question, but can you log into another interface - say SQL Developer - with the same credentials?

                            Ta,
                            Trent
                            • 11. Re: Apex 4 About Database login do not work
                              jariola
                              Hi,

                              I can login using SQL Developer and SQLPlus with no problem using those users (my own, SYS and SYSTEM)



                              Regards,
                              Jari
                              • 12. Re: BUG: Apex 4 About Database login do not work
                                Udo
                                Hello Joel,

                                it really is odd.
                                As you can see in my snippet above, there is just plain ascii in both username and password. I tried this through all three different web servers (OHS, EPG, Listener) with same results.

                                I would agree that APEX behaves like invalid credentials were entered - if I got the error message for wrong credentials I get right away when I actually have an unpriviledged user or enter a wrong password.
                                But I get this double redirect to the target page and then to the plain login page again, as if nothing ever happend. When I enter the wrong credentials, there's no redirect. In that case, I see the POST request and I'm just getting the response with the message box and see a GET for the "delete.gif" used as icon to close that message box. Neither of this happens when using the right credentials, so that's neither the same result nor the same behaviour as starting with wrong credentials.

                                Some other possible issues I went after:

                                There are no (other) login-issues with APEX authentication or DB authentication on these instances, even when using non-ASCII characters.

                                To eliminate the browser as possible cause, I downloaded FF Portable in English, ran it plain (no plugins) and stayed with only en-us and en as prefered display language. No impact.

                                I also tested with APEX Listener using the embedded glassfish on my XP to have the web server running exactly on the same locales as the browser. No difference.

                                As I see in later postings, I have one other parallel with Jari: We are both running the database on Ubuntu. My XE and the 11gR2 are located on two different hosts, but they are both running on the (unsupported) Ubuntu 8.04 LTS Server. But since we didn't have any other issue with the database on that platform before, I'm not willing to believe that this could be the cause.

                                Just to complete the listings like Trent provided.
                                My XE, installed via deb-package from OSS
                                Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
                                PL/SQL Release 10.2.0.1.0 - Production
                                "CORE     10.2.0.1.0     Production"
                                TNS for Linux: Version 10.2.0.1.0 - Production
                                NLSRTL Version 10.2.0.1.0 - Production
                                #uname -a
                                Linux <hostname> 2.6.18-028stab070.5 #1 SMP Fri Sep 17 15:37:23 MSD 2010 i686 GNU/Linux
                                #cat /etc/debian_version
                                lenny/sid
                                My 11gR2, installed by regular install package for X86_64
                                Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                                PL/SQL Release 11.2.0.1.0 - Production
                                "CORE     11.2.0.1.0     Production"
                                TNS for Linux: Version 11.2.0.1.0 - Production
                                NLSRTL Version 11.2.0.1.0 - Production
                                #uname -a
                                Linux <hostname> 2.6.24-28-xen #1 SMP Fri Jun 18 16:10:42 UTC 2010 x86_64 GNU/Linux
                                #cat /etc/debian_version
                                lenny/sid
                                I'm going to install XE on one of our training PCs running XP and retest there. If I can't reproduce the error there, at least I get a chance to monitor the positive case and compare traces.

                                -Udo
                                • 13. Re: BUG: Apex 4 About Database login do not work
                                  Udo
                                  Finally I'm done with my XE installation. Result: Issue is reproducible.
                                  Now, I'm relieved that this also occurs on non-Ubuntu systems, but it still isn't solved.

                                  Some thoughts:
                                  - I don't see any chance for a charset issue: Username and password standard ASCII; database and browser both work on the same machine.
                                  - I did not touch anything else in the database.

                                  This only leaves room for a problem during the installation itself. And as I can't do much about the XE-installation on a Windows box, I'll focus on the APEX installation.
                                  Here's what I did this time (and what we always do, and which worked for APEX 3.2):
                                  - DROP all existing APEX-Schemas (i.e., FLOWS_FILES and FLOWS_020100)
                                  - call APEX 4.0.1 installer with standard parameters (@apexins SYSAUX SYSAUX TEMP /i/)
                                  - update OWA (@owainst)
                                  - load APEX 4.0.1 images (@apxldimg.sql APEX_HOME)
                                  - set admin password(@apxchpwd)
                                  - create database user udo (connect, dba, ascii-password)
                                  - login to internal administration (after changing admin password again)
                                  - create workspace udo (reusing schema udo)
                                  - login to workspace udo (after changing admin password; recycled old password)
                                  - tried to login as sys in "About Database"

                                  Usually I also install the language pack for German, but on that installation I run without.

                                  Jari: Did you also install your instance like that?
                                  Joel: I think apex.oracle.com is always getting updates, not fresh installations and migrations. Did your XE get updates or a fresh install like my instances?

                                  Could it be something is missing/wrong in the 4.0.1 installer that doesn't occur when running an update installation?
                                  There's a way to find out: I'll install 3.2 on my new XE instance (following the above procedure for 4.0.1), see if "About Database" is working and, in case of success, do an update to 4.0.1.

                                  -Udo
                                  • 14. Re: BUG: Apex 4 About Database login do not work
                                    jariola
                                    Hi,


                                    Yes, I did install like that.
                                    here is how I did clean Apex 2.1 from XE before install 4.0.1
                                    ****** Drop old Apex image folder, jobs, public synonyms, flows files objects
                                    ****** Remove SYS owned old Apex objects
                                    ****** Remove old Apex schemas
                                    ****** Set XDB listener access to localhost only. Close database HTTP and FTP ports.
                                    begin
                                      dbms_xdb.deleteresource('/i/', DBMS_XDB.DELETE_RECURSIVE_FORCE);
                                      FLOWS_020100.wwv_flow_upgrade.remove_jobs;
                                      FLOWS_020100.wwv_flow_upgrade.drop_public_synonyms;
                                      FLOWS_020100.wwv_flow_upgrade.flows_files_objects_remove('FLOWS_020100');
                                      execute immediate 'drop package WWV_FLOW_VAL';
                                      execute immediate 'drop library WWV_FLOW_VAL_LIB';
                                      execute immediate 'drop package HTMLDB_SYSTEM';
                                      execute immediate 'drop public synonym HTMLDB_SYSTEM';
                                      execute immediate 'drop user flows_020100 cascade';
                                      execute immediate 'drop user flows_files cascade';
                                      dbms_xdb.setlistenerlocalaccess(true);
                                      dbms_xdb.sethttpport(0);
                                      dbms_xdb.setftpport(0);
                                      commit;
                                    end;
                                    /
                                    Regards,
                                    Jari
                                    1 2 Previous Next