1 2 Previous Next 17 Replies Latest reply on Mar 8, 2006 1:50 AM by 138979

    HTMLDB Connection Pooling with Oracle usernames

    jmitchell
      We've got an internal application that we expect to receive about 10,000 page hits an hour on a heavily used OLTP database. Last year we had memory issues due to the number of logins, in fact (system architecture limits). Currently, we have HTMLDB using connection pooling, so that we don't run our connections up, and it works great.

      However, as well as this works, we need to make sure that users are forced to log in, and we have multiple other applications that need to use the same users and roles as we are using here. For years we've been using the standard Oracle usernames and passwords.

      However, in HTMLDB, we can't seem to use both the Oracle usernames and passwords and connection pooling, as we can't seem to authenticate a password against a username without using a non-pooled DAD without a username. I've created a function that uses the hash from DBA_USERS, but it seems to cause some issues in it's current implementation, eventually leading to lockups. Is there some cleaner way that we can check the Oracle username and password without making the user log in, or should we create a second app on a different DAD that creates a cookie that the first one uses to authenticate, if that's even possible.
        • 1. Re: HTMLDB Connection Pooling with Oracle usernames
          60437
          Justin,

          If you're looking for an alternative to the browser's basic authentication challenge, you could change the application's authentication scheme to the HTML DB scheme (make that one "current", creating it first if necessary). Edit the scheme and in the Authentication Function field type "return login_valid;" in order to use the following function which you will create in your application's schema:
              create or replace FUNCTION login_valid(
                  p_username IN VARCHAR2,
                  p_password IN VARCHAR2)
                  RETURN BOOLEAN
              --
              -- Called from login procedure
              --
              AS
                  l_stmt           VARCHAR2(255);
                  l_old_password   VARCHAR2(30);
                  l_new_password   VARCHAR2(30);
              BEGIN
             
                  IF p_username IS NULL OR p_password IS NULL THEN
                      RETURN FALSE;
                  END IF;
                  --
                  -- Grab the user's current password
                  --
                  BEGIN
                    SELECT PASSWORD INTO l_old_password
                      FROM sys.dba_users
                     WHERE username = UPPER(p_username);
                  EXCEPTION
                      WHEN NO_DATA_FOUND THEN
                          --
                          -- The user doesn't exist
                          --
                          RETURN FALSE;
                  END;
                  --
                  -- Change their password
                  --
                  l_stmt := 'ALTER USER ' || p_username || ' IDENTIFIED BY "' || p_password||'"';
                  EXECUTE IMMEDIATE l_stmt;
                  --
                  -- Now, grab the password that this was changed to
                  --
                  BEGIN
                    SELECT PASSWORD INTO l_new_password
                      FROM sys.dba_users
                     WHERE username = UPPER(p_username);
                  EXCEPTION
                      WHEN NO_DATA_FOUND THEN
                          --
                          -- The user doesn't exist
                          --
                          RETURN FALSE;
                  END;
                  --
                  -- Very quickly change their password back in the
                  -- event that they were not equal
                  --
                  IF ( l_old_password <> l_new_password ) THEN
                      --
                      l_stmt := 'ALTER USER ' || p_username || ' IDENTIFIED BY VALUES''' || l_old_password || '''';
                      EXECUTE IMMEDIATE l_stmt;
                  END IF;
             
              --    RETURN l_old_password = l_new_password;
             
              END login_valid;
          This function will check the password entered in the application's login page against the database password for the account name entered as the username field in the login page.

          I didn't follow your question about the second app...

          Scott
          • 2. Re: HTMLDB Connection Pooling with Oracle usernames
            482473
            sorry to be such a dunderhead but what do you mean when you say create function in your application schema( in other words where would this function be place)
            • 3. Re: HTMLDB Connection Pooling with Oracle usernames
              60437
              Compile the PL/SQL function in the database schema used by your HTML DB application. You can use SQL*Plus or the HTML DB SQL Command Processor to compile objects.

              Scott
              • 4. Re: HTMLDB Connection Pooling with Oracle usernames
                256719
                i know this is a bit of an old thread, but i didn't see this point in it...

                using this technique to change the user's password will confilict with password reuse settings, so has very limited applicability...

                if a conscientious dba sets policies using PASSWORD_REUSE_TIME or PASSWORD_REUSE_MAX to anything other than UNLIMITED, this will sooner or later break all applications that depend that use this method

                if a PASSWORD_LIFE_TIME is used and passwords are allowed to be reused, this will appear to work, but will reset the users' password EXPIRY_DATE

                it also requires that the application schema be granted dba privileges, which is a very dangerous thing to do

                just my $0.02-1/2
                • 5. Re: HTMLDB Connection Pooling with Oracle usernames
                  joelkallman-Oracle
                  mcstock,

                  Make no mistake - this is not the optimal method to perform database authentication. Beyond the concerns that you raise, there's also the possibility of locking a user out of their account (in the event of a database exception, during the small time window before the password is reset). But without any provision in the Oracle database to take an arbitrary user's credentials and validate them, this is a workaround.

                  We'll hopefully see this functionality in the Oracle database. But you are correct, the issues you raise should be taken into account when using this method.

                  Joel
                  • 6. Re: HTMLDB Connection Pooling with Oracle usernames
                    60437
                    Mark - Good points and I concur with what Joel added. Just one point, the schema that runs this code needs only "alter any user" privilege, not the DBA role, if I remember correctly. Even that may be more than you want the applications' parsing schema to have, so the preferred approach to that problem would be to put the login page in a separate application from the other applications. All other applications would redirect to the login application's login page (by URL) for authentication. The login application would be the only app that parses as the schema with the "alter any user" privilege. The applications would then be tied together as one using a common cookie name.

                    Scott
                    • 7. Re: HTMLDB Connection Pooling with Oracle usernames
                      256719
                      good refinement to use the extra schema. yes, it is only 'alter user' that needs to be granted, but that allows things like 'alter user system identified by mypw;'

                      i saw a presentation on AJAX today -- got some ideas on using that as a possible work-around (including making the APEX app aware of the DB user's DB roles), I'll post that here if I get it to work
                      • 8. Re: HTMLDB Connection Pooling with Oracle usernames
                        256719
                        looks like this became a moot point with the production release of OracleXE -- I see a new DATABASE ACCOUNT authentication schema (the default).

                        this is new to the Application Express 2.1.0.00.39, isn't it? (by the way, there is no version number on the OXE AE home page)
                        • 9. Re: HTMLDB Connection Pooling with Oracle usernames
                          joelkallman-Oracle
                          It is new to the version of Application Express bundled with XE.

                          It is an intentional decision to not show the version number on the login page. This is only shown to authenticated users.

                          Joel
                          • 10. Re: HTMLDB Connection Pooling with Oracle usernames
                            Dietmar Aust
                            looks like this became a moot point with the
                            production release of OracleXE -- I see a new
                            DATABASE ACCOUNT authentication schema (the
                            default).
                            AFAIK this new authentication scheme will be made available in the upcoming release of Apex 2.2 (standalone) also, right?

                            ~Dietmar.
                            • 11. Re: HTMLDB Connection Pooling with Oracle usernames + DATABASE ACCOUNT auth
                              256719
                              and you guys fixed the DBMS_SYS_SQL issue, too!

                              that makes APEX feasible to use with existing db schemas, and really simplifies setting up multi-user APEX applications, and multiple APEX applications in different workspaces.

                              great improvement -- needs to be advertised widely!
                              • 12. Re: HTMLDB Connection Pooling with Oracle usernames + DATABASE ACCOUNT auth
                                VANJ
                                Can someone summarize what exactly the new database authentication scheme in 2.2 is going to do? How is it going to work?

                                Does the app parsing schema still need the ALTER ANU USER system privilege or not?
                                • 13. Re: HTMLDB Connection Pooling with Oracle usernames + DATABASE ACCOUNT auth
                                  60437
                                  Vikas - It lets you use a database schema's username/pw to authenticate. If the application's authentication scheme specifies this option in the authentication function, the login API will call a new function in the database to check a username/pw combination. No privileges are required of the application's schema to use this.

                                  Scott
                                  • 14. Re: HTMLDB Connection Pooling with Oracle usernames + DATABASE ACCOUNT auth
                                    60437
                                    Mark,
                                    and you guys fixed the DBMS_SYS_SQL issue, too!...that makes APEX feasible to use with existing db schemas, and really simplifies setting up multi-user APEX applications, and multiple APEX applications in different workspaces.
                                    The effect will be seen only when using the SQL Workshop command processor, running SQL scripts, and when performing DDL using the Object Browser. It has no effect on the Application Builder or in the behavior of developed applications which will still behave as though all code in your application were contained in compiled definers rights objects. The change simply makes the command processor, for example, work more like SQL*Plus, such that enabled default roles are observed during statement parsing/execution. It doesn't even extend to changing who "user" is, i.e., it still will be the "actual" session's user value (ANONYMOUS or HTMLDB_PUBLIC_USER or the DAD-specified user). It also does not make more schemas visible in any LOVs in the tools environment.

                                    A concrete example of how the change is useful -- Say you want to create a table in the Object Browser or SQL command window: Before, if the current parsing schema did not have CREATE TABLE privilege directly granted to it, this would fail. Now, if the current parsing schema has the privilege directly granted to it or via a database role it will succeed.

                                    Scott
                                    1 2 Previous Next