14 Replies Latest reply: Mar 20, 2011 8:04 PM by 835457 RSS

    ORA-28001

    835457
      Hi All,

      I'm using forms 6i, when I create user with expire password, you know at first logon attempt by that user, he will be asked to enter a new password...when I connect to that user through SQL* PLUS it raises (ORA-28001: the password has expired)..It is ok,but when I connect to that user through forms 6i, it doesn't raise ORA-28001, it just raise (ORA-01012:not logged on), Also, in forms 6i , when I enter a wrong password for some existing user it gives me (ORA-01012:not logged on) too ! while it gives me ORA-01017: invalid username/password; logon denied in sql*plus. So, in forms 6i, I can't distinguish if it is because password expired or because wrong password..

      I think (ORA-01012:not logged on) is a general exception raised when LOGON attempt is unsuccessful in forms 6i?

      I used DBMS_ERROR_CODE to catch exception is it ok? Is there better way to catch the exact exception ?


      Thanks in advance.






      Regards,
      LoveSoul
        • 1. Re: ORA-28001
          Amatu Allah Neveen Ebrahim
          Al-Salamu Alikum We Rahmatu Allah We Barakatu...

          oRA - 28001 : THE PASSWORD HAS EXPIRED
          This is due to specifing in the Profile to expire the password after a limited time.

          u can modify this to...

          ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME  UNLIMITED;
          or any of the following Pls have a look here ....
          I used DBMS_ERROR_CODE to catch exception is it ok?
          yes it is just fine; For non-Forms errors, use the DBMS_ERROR_CODE built-in to get the error code and DBMS_ERROR_TEXT to get the error message.
          Is there better way to catch the exact exception ?
          it's the best :)

          but how do u define it ? u can handle this error by testing the DBMS_ERROR_CODE Forms system variable to see if it equals -01012

          and also display the DBMS_ERROR_TEXT default error description or you can use your own error description.


          In On-Error Trigger Form-Level
          BEGIN
               .... other code here
           
          EXCEPTION
          
          -- to handel a specific non-form error code pls use the following
          
           WHEN DBMS_ERROR_CODE = -01012 THEN
          
          -- Pls note the error number is preciding by -
          
          MESSAGE (DBMS_ERROR_TEXT );
          
          -- Or ur own handeled exception message
          
          END;
          In forms, you catch the raised error number using both DBMS_ERROR_CODE and DBMS_ERROR_TEXT.

          Go on u r on the right track. :)

          Here is The Basics of Error-Message Handling Pls have a look ...

          Hope this helps...

          Regards,

          Abdetu...
          • 2. Re: ORA-28001
            835457
            Wa Alaikum A'ssalam Wa Rahmatullahi Wa Barakatuh :)

            Thanks Abdetu for your fast reply..

            I altered profile as you stated...then I created new user with password expire, it doesn't help...and again the same problem.

            Any time I try to connect using sql*plus it gives me what I wanted (ORA-28001), but when I connect to the user using forms 6i, (ORA-28001) exception doesn't occure, but only ORA-01012 :(


            The link you gave me doesn't open, I don't know why..

            I'm thinking to forget about creating users with password expire ..I know it is better that DBA has no knowledge about users passwords and DBA is just asked by users to reset password when it is lost.

            Shukran jazeelan 4 your help & Barak Allah Fiki Abdetu...
            • 3. Re: ORA-28001
              Amatu Allah Neveen Ebrahim
              Pls tell me where u use the Alter Statment ?

              Pls run the following statment ...

              SQL> select profile,resource_name ,limit from dba_profiles where profile='DEFAULT';
              then do the ALter Statment and re - select as mentioned above. and see if there is any difference.

              Regards,

              Abdetu...
              • 4. Re: ORA-28001
                835457
                By the way..

                When I use LOGON('USER','PASSWORD',FALSE) nothing appear before exception ORA-01012

                but when I use LOGON('USER','PASSWORD',TRUE), A builtin dialog for cahnging paswword is popped out and then when I cancel the dialog ORA-01012 was raised

                I always use LOGON('USER','PASSWORD',FALSE) because I have my own dialog for LOGON processing... What I have to do to get ORA-28001 without showing buitlin dialog ??



                Regards,
                LoveSoul
                • 5. Re: ORA-28001
                  835457
                  Hello Abdetu..


                  I used Alter in SYS account (connect /as sysdba) and used it again in my ADMIN user which has DBA role....I tried select and no change before and after altering profile, all resources are UNLIMITED except PASSWORD_VERIFY_FUNCTION resource, is NULL.


                  Regards,
                  LoceSoul
                  • 6. Re: ORA-28001
                    Amatu Allah Neveen Ebrahim
                    I create user with expire password
                    where did u do that...?

                    it should work ... FAILED_LOGIN_ATTEMPTS.

                    Pls notice the changes before and after the Alter Statment u do not need to restart the database after the changes will take effect immediately.

                    Pls notice the result in the Select Statment.

                    u can also check the previous link it's working now...

                    The following is also a useful linkSecuring Your Oracle™ Database With User Profilesthat contains scripts & some useful advices.

                    Hope it helps...

                    Regards,

                    Abdetu...
                    • 7. Re: ORA-28001
                      835457
                      Yes logon attempts are unlimitted...but ora-28001 couldn't be catched.

                      I created user from sql plus as follows;

                      Create user A identified by A password expire account unlock;
                      grant create session to a;


                      everything is alright inside sql it gives ora-28001 but inside forms 6i it just showing change password built in dialog or not according to false or true parameter in LOGON procedure call, and both without raising ora-28001 exception..


                      Regards,
                      LoveSoul
                      • 8. Re: ORA-28001
                        Amatu Allah Neveen Ebrahim
                        Al-Salamu Alikum We Rahmatu Allah We Barakatu...

                        altered profile as you stated...then I created new user with password expire, it doesn't help...
                        Pls note that ...

                        Setting FAILED_LOGIN_ATTEMPTS is a brilliant idea for end user accounts, and a terrible idea for accounts that are used for automated authentication of applications.

                        Beccause When it is set, this parameter limits the number of failed login attempts that can be made before the account is locked for the value of the

                        PASSWORD_LOCK_TIME setting.

                        u r not using the right commands...

                        ora-28001
                        The user's account has expired and the password needs to be changed
                        ALTER USER myuser IDENTIFIED BY new_password;
                        
                        ALTER USER myuser ACCOUNT UNLOCK;
                        Pls make the necessary Grants...

                        pls check the links it works for me... and have a look here ... Basic Security Measures for Oracle.


                        Regards,

                        Abdetu...
                        • 9. Re: ORA-28001
                          835457
                          Hello,


                          ALTER USER myuser IDENTIFIED BY new_password;

                          ALTER USER myuser ACCOUNT UNLOCK;


                          Application has to discover expired password first, then go into these commands, That is why I need to catch ORA-28001 !!

                          I don't think I used wrong commands (they are not used in logon processing), creating users is one of my application's tasks and it have seperate page for managing users..I just thought about creating users with expired passwords and when they logon

                          , an expired password have to be detected by the application (through ora-28001), A custom dialog must appear asking the user to change password..if the two passwords are matched then a logon into my application ADMIN (which has DBA role) is made then the commands above have to be applied then another logon is made with that user.


                          Thanks a lot for the links :) and for your precious time..


                          Regards,
                          LoveSoul
                          • 10. Re: ORA-28001
                            835457
                            Hi,

                            Now I got ORA-28001 after reducing (:system.message_level) variable from 25 to 10; I didnt know that this variable blocks ORA exceptions!!...I thought it just blocks FORM bulitin messages...I'm really sorry for annoying you Miss Abdetu...


                            Please take care
                            • 11. Re: ORA-28001
                              Amatu Allah Neveen Ebrahim
                              Al-Salamu Alikum We Rahmatu Allah We Barakatu...
                              I'm really sorry for annoying you Miss Abdetu...
                              Not at alllll u welcomed any time i just thought u logged off, i found the following steps didn't try it before but through my search i found it helpful

                              Here the details if the link didn't work....


                              Details: ORA-28001: the password has expired

                              One of the reasons could be that the password for the SYSMAN user is expired. However, changing the password alone will not solve this issue.*

                              Several additional steps are required in order to make Oracle Enterprise Manager connect:_

                              Before you start: Verify that ORACLE_HOME, ORACLE_SID environment variables are set.

                              – If not, set them using as environment variables or open a command line and type SET ORACLE_SID=<The database SID>

                              1. Stop the dbconsole: emctl stop dbconsole (dos ) or using the windows services stop the OrcleDBConsole<The database SID>.

                              2. Connect to the database as a user with DBA privilege with SQL*Plus

                              and run the following command:

                              alter user sysman identified by <the new password> ;

                              3. Verify that the new password works

                              SQL> connect sysman/<the new password>

                              4. Go to ORACLE_HOME/<HostName_SID>/sysman/config and save a backup of the emoms.properties file.

                              a) Open the file emoms.properties and search for:

                              oracle.sysman.eml.mntr.emdRepPwd=<some encrypted value>

                              Replace the encrypted value with the new password value

                              b) Search for oracle.sysman.eml.mntr.emdRepPwdEncrypted=TRUE and change TRUE to FALSE

                              c) Save and close emoms.properties

                              5. Restart the dbconsole:

                              emctl start dbconsole (dos ) or using the windows services start the OrcleDBConsole<The database SID>.

                              6. Open emoms.properties again and Search for:

                              a)oracle.sysman.eml.mntr.emdRepPwd= verify that the password is encrypted

                              b) oracle.sysman.eml.mntr.emdRepPwdEncrypted= verify that the value is set to TRUE

                              7. Refresh Oracle Enterprise Manager

                              Hope this solves ur problem... :)

                              Regards,

                              Abdetu...
                              • 12. Re: ORA-28001
                                Amatu Allah Neveen Ebrahim
                                Pls note that...

                                The expiration of passwords in Oracle is controlled by the profile that is assigned to the user. You can see which profile is assigned to each user by querying the DBA_USERS table as follows...

                                SELECT username, profile   FROM dba_users ;
                                You can revert to the old behavior either by creating a new profile and assigning that new profile to your existing users ( ADMIN ) or by updating the existing profile that is already assigned to your users.
                                don't think I used wrong commands
                                i mean i had a guess when u said all resources are UNLIMITED except PASSWORD_VERIFY_FUNCTION resource, is NULL.
                                that u didn't focus on PASSWORD_LIFE_TIME but PASSWORD_VERIFY_FUNCTION that's was my guess..


                                i believe that solving this db issue will solve urs in forms accordingly... :)

                                Hope this helps...

                                Regards,

                                Abdetu...
                                • 13. Re: ORA-28001
                                  835457
                                  Thanks a lot Abdetu...but now I have no problem after I catched ORA-28001....the problem was about I couldn't catch this exception...

                                  message_level system variable was set to 25 , I just reduced it it (10), now everthing is alright and I can catch this exception and do my processing....


                                  God Bless you...and thanks for being here always.



                                  Regards,
                                  LoveSoul
                                  • 14. Re: ORA-28001
                                    Amatu Allah Neveen Ebrahim
                                    Glade it's working... but to tell u the truth i don't recommand the solution of :System.Message_Level
                                    There are many work arrounds that we can cover's Developping bugs as using
                                    CLEAR_MESSAGE;
                                    
                                    &
                                    
                                    WHEN OTHERS THEN NULL;
                                    
                                    IF ERROR_NUMBER THEN NULL;
                                    This can't be elaborated work;_ if u recognize the problem and having a suggestive Links & Solutions the Problem has to be correctly solved , i do recommand u to give it a try...

                                    Hope this helps...

                                    Regards,

                                    Abdetu...