1 2 Previous Next 24 Replies Latest reply: May 24, 2013 11:01 AM by steveFred RSS

    CHANGE_PASSWORD_ON_FIRST_USE Function

    steveFred
      I built an application in Apex 4.2 . It uses access control to set access level. It also uses a custom table based authentication for log in that checks the users log in ID and password
      This log in function is under Shared Components --> Authentication Schemes --> Authentication Function Name


      I also want to add a function to have the user reset their password the first time they log in

      I see this function in Apex_Util:

      CHANGE_PASSWORD_ON_FIRST_USE Function

      But I'm not understanding how to implement it.
      Can some one give me an example? Can I put it somewhere in shared components like the authentication function?

      Thanks

      Edited by: steveFred on May 14, 2013 8:10 AM
        • 1. Re: CHANGE_PASSWORD_ON_FIRST_USE Function
          TexasApexDeveloper
          That is used for Authentication using APEX users not custom table driven Authentication.. What I would suggest is.. Add a column to your users table (change_password, varchar2(1)) that defaults to Y. In your Authentication process check this field, and if it is set to Y, then re-direct your user to a form that will require their password to be changed. When it passes your validation requirements, update the password in your table and set the change_password field to N..

          Thank you,

          Tony Miller
          Ruckersville, VA
          • 2. Re: CHANGE_PASSWORD_ON_FIRST_USE Function
            DanielB
            i think is when you create an user, for set the user and the user needs to chenge the pass at first connection
            • 3. Re: CHANGE_PASSWORD_ON_FIRST_USE Function
              steveFred
              thanks for the response
              sorry for my lack of understanding ,

              "In your Authentication process check this field"

              there is a log in function:


              create or replace FUNCTION check_Login (
              p_username IN VARCHAR2,
              p_password IN VARCHAR2)
              RETURN BOOLEAN IS
              BEGIN
              FOR c1 IN (SELECT 1
              FROM myusers
              WHERE upper(username) = upper(p_username)
              AND upper(userpassword) = upper(p_password))
              LOOP
              RETURN TRUE;
              END LOOP;
              RETURN FALSE;
              END;

              Are you saying I should add something here?
              Then based on what this function returns my log in page would direct the user to a page to update the password ( and then that page/form would set this field change_password
              field to 'N')


              Or is there an item on the login page that looks at the value of change_password?

              not sure how this works

              thanks
              • 4. Re: CHANGE_PASSWORD_ON_FIRST_USE Function
                TexasApexDeveloper
                I would add a few things to your authentication, but that is for another time..

                What I normally do is
                create or replace FUNCTION check_Login (
                p_username IN VARCHAR2,
                p_password IN VARCHAR2)
                RETURN BOOLEAN IS
                
                v_password myusers.userpassword%TYPE;
                v_return boolean;
                
                BEGIN
                  
                  -- wrap the select below inside a select to catch exceptions and use them locally
                
                   BEGIN
                
                      SELECT userpassword into v_password
                         FROM myusers
                      WHERE  upper(username) = upper(p_username);
                
                        exception
                        when no_data_found then
                           v_password := '~';
                
                   END;
                
                   if  upper(userpassword) = upper(p_password)
                
                       v_return := TRUE;
                     
                     else
                        v_return := FALSE;
                     
                     end if;
                
                     return v_return;
                  
                END;   
                What this does is, it selects the password for an existing login, based upon the entered username, otherwise it sets it to ~ . THis way we get no rows not found error being propogated to the login page, but the true error that the login information is invalid..

                Thank you,

                Tony Miller
                Ruckersville, VA
                • 5. Re: CHANGE_PASSWORD_ON_FIRST_USE Function
                  steveFred
                  okay, understood, but where do I deal with directing user to reset password if change_password ="y"
                  • 6. Re: CHANGE_PASSWORD_ON_FIRST_USE Function
                    TexasApexDeveloper
                    owa_util.redirect_url('f?p=&APP_ID.:765:&SESSION.::NO:::');
                    
                    create or replace FUNCTION check_Login (
                    p_username IN VARCHAR2,
                    p_password IN VARCHAR2)
                    RETURN BOOLEAN IS
                     
                    v_password myusers.userpassword%TYPE;
                    v_return boolean;
                    v_reset_password varchar2(1);
                     
                    BEGIN
                      
                      -- wrap the select below inside a select to catch exceptions and use them locally
                     
                       BEGIN
                     
                          SELECT userpassword,
                                     reset_password
                             into v_password,
                                   v_reset_password 
                             FROM myusers
                          WHERE  upper(username) = upper(p_username);
                     
                            exception
                            when no_data_found then
                               v_password := '~';
                               v_reset_password := 'N'
                     
                       END;
                     
                       if  upper(userpassword) = upper(p_password)
                              
                           v_return := TRUE;
                         
                         else
                            v_return := FALSE;
                         
                         end if;
                     
                      if v_return = TRUE and v_reset_password = 'Y'
                        
                        -- Change the page # (765:) you are re-directing to in below statement
                        -- also page must be public with NO AUTHORIZATION
                    
                        owa_util.redirect_url('f?p=&APP_ID.:765:&SESSION.::NO:::');
                    
                       -- on that page, have a after process update your myusers
                       -- with the new password for the user, based upon P101_USERNAME value
                    
                      end if;
                    
                      return v_return;
                      
                    END;
                    Thank you,

                    Tony Miller
                    Ruckersville, VA
                    • 7. Re: CHANGE_PASSWORD_ON_FIRST_USE Function
                      steveFred
                      thanks
                      I'm trying to get it working now, I'll let you know when I succeed
                      • 8. Re: CHANGE_PASSWORD_ON_FIRST_USE Function
                        steveFred
                        I got the function to compile
                        but when I go to log in

                        ORA-06550: line 4, column 23: PLS-00306: wrong number or types of arguments in call to 'check_login' ORA-06550: line 4, column 1: PL/SQL: Statement ignored


                        makes sense, I added a 3rd argument to the function, but not sure what I'm supposed to change.
                        On the log in page I see an after submit process called Login . The only other code on this page is get user name cookies and set user name cookies

                        The Login process was

                        wwv_flow_custom_auth_std.login(
                        P_UNAME => :P101_USERNAME,
                        P_PASSWORD => :P101_PASSWORD,
                        P_SESSION_ID => v('APP_SESSION'),
                        P_FLOW_PAGE => :APP_ID||':1'
                        );


                        so I added a hidden item called P101_RESETPASSWORD,

                        and changed login process to


                        wwv_flow_custom_auth_std.login(
                        P_UNAME => :P101_USERNAME,
                        P_PASSWORD => :P101_PASSWORD,
                        P_RESETPASSWORD => :P101_RESETPASSWORD,
                        P_SESSION_ID => v('APP_SESSION'),
                        P_FLOW_PAGE => :APP_ID||':1'
                        );

                        but get an error

                        ORA-06550: line 2, column 1: PLS-00306: wrong number or types of arguments in call to 'LOGIN' ORA-06550: line 2, column 1: PL/SQL: Statement ignored
                        • 9. Re: CHANGE_PASSWORD_ON_FIRST_USE Function
                          TexasApexDeveloper
                          Lets take a step BACK, okay.. When you used your Check_login function, did it work in your application without the changes to handle the change password logic?

                          Thank you,

                          Tony Miller
                          LuvMuffin Software
                          Ruckersville, VA
                          • 10. Re: CHANGE_PASSWORD_ON_FIRST_USE Function
                            steveFred
                            yes
                            I was able to add users, change passwords , etc and then log in with those user ids
                            • 11. Re: CHANGE_PASSWORD_ON_FIRST_USE Function
                              steveFred
                              I made some changes based on the compile error messages I was getting. I got to this point:

                              create or replace FUNCTION newLogin (
                              p_username IN VARCHAR2,
                              p_password IN VARCHAR2)
                              RETURN BOOLEAN IS

                              v_password myusers.userpassword%TYPE;
                              v_return boolean;
                              v_reset_password varchar2(1);

                              BEGIN

                              -- wrap the select below inside a select to catch exceptions and use them locally
                              BEGIN
                              SELECT userpassword,
                              resetpassword
                              into v_password,
                              v_reset_password
                              FROM myusers
                              WHERE upper(username) = upper(p_username);

                              exception
                              when no_data_found then
                              v_password := '~';
                              v_reset_password := 'N';

                              END;

                              if upper(userpassword) = upper(p_password) then
                              v_return := TRUE;
                              else
                              v_return := FALSE;
                              end if;

                              if v_return = TRUE and v_reset_password = 'Y' then

                              owa_util.redirect_url('f?p=&APP_ID.:16:&SESSION.::NO:::');

                              end if;

                              return v_return;

                              END;

                              and when I went to compile I got this message

                              Compilation failed,line 31 (10:03:36)
                              PLS-00201: identifier 'USERPASSWORD' must be declaredCompilation failed,line 31 (10:03:36)
                              PL/SQL: Statement ignored

                              the error points to this line : if upper(userpassword) = upper(p_password)
                              I don't understand since userpassword is on the table myusers


                              SO I declared userpassword.
                              The function looks like this now. This function below compiles successfully. But when I run the application now I can't log in under ANY user name and password

                              create or replace FUNCTION newLogin (
                              p_username IN VARCHAR2,
                              p_password IN VARCHAR2)
                              RETURN BOOLEAN IS

                              userpassword myusers.userpassword%TYPE; <=== ADDED THIS LINE
                              v_password myusers.userpassword%TYPE;
                              v_return boolean;
                              v_reset_password varchar2(1);

                              BEGIN

                              -- wrap the select below inside a select to catch exceptions and use them locally

                              BEGIN

                              SELECT userpassword,
                              resetpassword
                              into v_password,
                              v_reset_password
                              FROM myusers
                              WHERE upper(username) = upper(p_username);

                              exception
                              when no_data_found then
                              v_password := '~';
                              v_reset_password := 'N';

                              END;

                              if upper(userpassword) = upper(p_password) then

                              v_return := TRUE;

                              else
                              v_return := FALSE;

                              end if;

                              if v_return = TRUE and v_reset_password = 'Y' then

                              owa_util.redirect_url('f?p=&APP_ID.:16:&SESSION.::NO:::');

                              end if;
                              • 12. Re: CHANGE_PASSWORD_ON_FIRST_USE Function
                                TexasApexDeveloper
                                You really need to take a step back here.. How long have you been developing in PL/SQL? Why I ask is, you have a VERY basic error on your function:
                                when you added this line: userpassword myusers.userpassword%TYPE; <=== ADDED THIS LINE
                                
                                Where does this variable get its value?  
                                
                                Why I am asking is, you're using it in an if statement:
                                
                                if upper(userpassword) = upper(p_password) then
                                So that this will ALWAYS be false since you are comparing a NULL value to your entered in password..
                                Maybe you should take a step back and look at some more examples of how to code in PL/SQL??

                                Thank you,

                                Tony Miller
                                LuvMuffin Software
                                Ruckersville, VA
                                • 13. Re: CHANGE_PASSWORD_ON_FIRST_USE Function
                                  steveFred
                                  I'm not a PL/SQL developer at all . I'm a mainframe developer with a client that wants me to move an application to APEX.
                                  My SQL experience is 20 years of COBOL Db2, very different
                                  I have the entire application working except for a function to get the user to change their password when the first log on

                                  I coded the function based on what I thought was your suggestion, ( look at the first part of my post) but I always got an error at this line;

                                  if upper(userpassword) = upper(p_password)

                                  it was saying that userpassword was not declared
                                  I don't know who to get around that
                                  • 14. Re: CHANGE_PASSWORD_ON_FIRST_USE Function
                                    TexasApexDeveloper
                                    create or replace FUNCTION newLogin (
                                    p_username IN VARCHAR2,
                                    p_password IN VARCHAR2)
                                    RETURN BOOLEAN IS
                                    
                                    v_password myusers.userpassword%TYPE;
                                    v_return boolean;
                                    v_reset_password varchar2(1);
                                    
                                    BEGIN
                                    
                                    -- wrap the select below inside a select to catch exceptions and use them locally
                                    BEGIN
                                    
                                    -- Question here: Do you have a table Named Users with the required columns?
                                    --  Here you will  need a table of users info with columns:
                                    
                                    username
                                    userpassword,
                                    resetpassword
                                    
                                    
                                    -- We are selecting the users password (userpassword) and reset password flag (resetpassword)
                                    -- from the users table where there is an upper case user name equal to the upper case passed in user name
                                    
                                    SELECT userpassword,
                                    resetpassword
                                    into v_password,
                                    v_reset_password
                                    FROM myusers
                                    WHERE upper(username) = upper(p_username);
                                    
                                    
                                    -- if no user is found, then we force the password to a ~
                                    -- and set the reset password to N
                                    
                                    exception
                                    when no_data_found then
                                    v_password := '~';
                                    v_reset_password := 'N';
                                    
                                    END;
                                    
                                    -- Now we check to see if the two passwords are the same
                                    -- if they are, then we check the reset password flag
                                    
                                    if upper(v_password) = upper(p_password) then
                                    v_return := TRUE;
                                    else
                                    v_return := FALSE;
                                    end if;
                                    
                                    -- We don't check the rset password flag if the password is not valid
                                    
                                    if v_return = TRUE and v_reset_password = 'Y' then
                                    
                                    -- if it is, we then redirect the user to your PUBLIC page that will collect the new password and save it to your users table
                                    -- and the redirect BACK to page 101 so they can re login
                                    
                                    owa_util.redirect_url('f?p=&APP_ID.:16:&SESSION.::NO:::');
                                    
                                    end if;
                                    
                                    return v_return;
                                    
                                    END;
                                    Does that help??

                                    Thank you,

                                    Tony Miller
                                    LuvMuffin Software
                                    Ruckersville, VA
                                    1 2 Previous Next