Resolving Oracle User Password expiration issues

One, the user password is about to expire, causing autotrace cannot open

If the user password is about to expire, you will receive the following prompt when you log in to the database:
ERROR:
Ora-28002:the password'll expire within 7 days
Of course, the password has not really expired at this point, and the user can still log in to the database after receiving the error prompt. However, if you get a hint that your password is about to expire, you will have a problem opening autotrace.

1. Sql> Conn Darren/darren
2. ERROR:
3. Ora-28002:the password'll expire within 7 days
5. Connected.
6. Sql> set autotrace on;
7. ERROR:
8. Ora-28002:the password'll expire within 7 days

Sp2-0619:error while connecting
Sp2-0611:error Enabling STATISTICS Report

Since there is a hint that the password is about to expire, the Password_life_time parameter in profile is definitely not set to unlimited and can be viewed dba_profiles for verification:

1. Sql> Select Profile,resource_name,limit from dba_profiles where resource_name= ' password_life_time ';
3. Profile Resource_name LIMIT
4. ---------- -------------------------------- ----------
5. DEFAULT Password_life_time 1

If the production environment does not have a special restriction on password expiration, you can modify this parameter to UNLIMITD:

1. ALTER profile DEFAULT LIMIT password_life_time UNLIMITED

Try opening autotrace again at this point:

1. Sql> Select Profile,resource_name,limit from dba_profiles where resource_name= ' password_life_time ';
3. Profile Resource_name LIMIT
4. ---------- -------------------------------- ----------
5. DEFAULT Password_life_time UNLIMITED
7. Sql> Conn Darren/darren
8. ERROR:
9. Ora-28002:the password'll expire within 7 days
12. Connected.
13. Sql> set autotrace on;
14. ERROR:
15. Ora-28002:the password'll expire within 7 days
18. Sp2-0619:error while connecting
19. Sp2-0611:error Enabling STATISTICS Report

The same error indicates that if the user's password is about to expire, then modifying profile will not take effect for the user who will expire the password. You should use a method that resets the user's password (the password can be the same as the previous password).

1. Sql> alter user Darren identified by Darren;
3. User altered.
5. Sql> Conn Darren/darren
6. Connected.
7. Sql> set autotrace on;

The Autotrace function is turned on successfully at this time.
Try another scenario, change the user password after the user logs on, but do not log back in to try to turn on autotrace.

1. Sql> alter user Darren identified by Darren
2. 2;
4. User altered.
6. Sql> set autotrace on;
7. ERROR:
8. Ora-01017:invalid Username/password; Logon denied
11. Sp2-0619:error while connecting
12. Sp2-0611:error Enabling STATISTICS Report

The same mistake came up again.
Here, we come to the conclusion that when the Autotrace function is turned on, the database will create another session to track the current session with the newly created session. Therefore, when the user password is about to expire, when the Autotrace feature is turned on, the new session is created with an exception due to the return ORA-2802.

Second, 11g new features, password delay authentication

11g, added the user Password delay authentication feature, that is, if the user entered the wrong password, then the user's login verification will increase with the number of input error password, until the correct login and re-count. As follows:

2. Sql> set time on
4. 13:32:45 sql>
6. Conn Darren/xxxxxx
8. Conn Darren/xxxxxx
10. Conn Darren/xxxxxx
12. Conn Darren/xxxxxx
14. Conn Darren/xxxxxx
16. Conn Darren/xxxxxx
18. Conn Darren/xxxxxx
20. ERROR:
22. Ora-01017:invalid Username/password; Logon denied
26. Warning:you is no longer connected to ORACLE.
28. 13:32:59 sql> ERROR:
30. Ora-01017:invalid Username/password; Logon denied
34. 13:32:59 sql> ERROR:
36. Ora-01017:invalid Username/password; Logon denied
40. 13:32:599 sql> ERROR:
42. Ora-01017:invalid Username/password; Logon denied
46. 13:33:00 sql> Conn Gyl/xxxxxx
48. ERROR:
50. Ora-01017:invalid Username/password; Logon denied
54. 13:33:02 sql>
56. ERROR:
58. Ora-01017:invalid Username/password; Logon denied
62. 13:33:05 sql>
64. ERROR:
66. Ora-01017:invalid Username/password; Logon denied
70. 13:33:10 sql>
72. ERROR:
74. Ora-01017:invalid Username/password; Logon denied

As you can see, the time for validation is getting longer, and if you continue to increase the number of incorrect logins, the validation time will continue to grow. If multiple sessions are constantly using the wrong password to connect to the database, it will cause the user's login to hang. If you want to turn off this feature, you can set the following events:

1. sql> ALTER SYSTEM SET EVENT = ' 28401 TRACE NAME ' CONTEXT FOREVER, Level 1 ' SCOPE = SPFILE;

Third, SYS user is not subject to the Password_life_time parameter limit
Iv. After the user's password expires, the user's status ( Dba_user.account_status is not immediately updated to expired, the user state is updated only when the user tries to connect to the database
v. Some parameters related to user password

2. Sql> SELECT * from Dba_profiles where resource_name like '%password% ';
6. Profile Resource_name RESOURCE LIMIT
8. ---------- -------------------------------- -------- ----------
10. DEFAULT password_life_time PASSWORD UNLIMITED
12. DEFAULT password_reuse_time PASSWORD UNLIMITED
14. DEFAULT Password_reuse_max PASSWORD UNLIMITED
16. DEFAULT password_verify_function PASSWORD NULL
18. DEFAULT password_lock_time PASSWORD 1
20. DEFAULT Password_grace_time PASSWORD 7

Password_life_time:
Set the effective time (in days) for the password to be reset once the password is exceeded. The default is 180 days (11g,10gunlimited).
Password_reuse_time:
Many systems do not allow users to re-enable passwords that have been used in the past. This resource entry sets the number of days after which a failed password is passed before the user can re-use the password. The default is unlimited.
Password_reuse_max:
The number of times the password must be re-provisioned before re-enabling a previously used password (number of repetitions).
Password_lock_time:
Sets the number of days the account is locked (when the login failure reaches failed_login_attempts).
Password_grace_time:
Set a grace day to reset the password before the password expires. When the password expires, a warning message appears at logon to show the number of days. If the password is not modified within the Grace Day, the password is invalidated.
password_verity_function:
This resource entry allows you to invoke a PL/SQL to verify the password. Oracle has provided scripts for the app, but users can develop their own validation scripts whenever they want. The parameter is set to the name of the PL/SQL function. The default is null.
failed_login_attempts:
Sets the number of failures that can fail when logging on to an Oracle database. Once a user attempts to log in to the database, the user's account is locked and can only be unlocked by the DBA.

Resolving Oracle User Password expiration issues