Oracle user password Problems

Oracle users seldom pay attention to their passwords. Generally, the password expiration time is set to never expire after the database is installed, but an error occurs when autotrace is enabled.

Oracle users seldom pay attention to their passwords. Generally, the password expiration time is set to never expire after the database is installed, but an error occurs when autotrace is enabled.

Oracle users seldom pay attention to their passwords. Generally, the password expiration time is set to never expire after the database is installed, but an error occurs when autotrace is enabled, it attracted my attention on user passwords. The environment we encounter at work is very different. You cannot know what the next customer's environment looks like, and you cannot know the database installed by the DBA of the database you are using, this is a difficult and fun part of your work. The diversity of production environments is also one of the driving force for us to learn. The following introduces three considerations for Oracle user passwords:

1. the user password is about to expire, so autotrace cannot be enabled

If your password is about to expire, you will receive the following prompt when logging on to the database:

ERROR:

ORA-28002: the password will expire within 7 days

Of course, the password has not expired yet, and the user can still log on to the database after receiving the error message. However, if you receive a prompt that the password is about to expire, it will be a problem if you want to enable autotrace.

SQL> conn darren/darren

SP2-0619: Error while connecting
SP2-0611: Error enabling STATISTICS report

The prompt that the password is about to expire indicates that the PASSWORD_LIFE_TIME parameter in profile is definitely not set to unlimited. You can view dba_profiles for verification:

If the production environment has no special restrictions on password expiration, you can change this parameter to unlimitd:

Alter profile default limit PASSWORD_LIFE_TIME UNLIMITED

Then try to open autotrace:

If the password is about to expire, changing the profile will not take effect for users whose password is about to expire. The password should be reset (the password can be the same as the previous password.

The autotrace function is successfully enabled.
Try another scenario. After the user logs on, change the user password, but do not log on again. Then, try to enable autotrace.

The same error occurs again.
At this point, we come to the conclusion that when the autotrace function is enabled, the database creates another session and uses the newly created session to track the current session. Therefore, when the autotrace feature is enabled when the user password is about to expire, an exception occurs when a new session is created because the ORA-2802 is returned.

Ii. New Features of 11g, password delay Authentication

In the 11g, the user password delay authentication feature is added, that is, if the user entered the wrong password during login, therefore, the login authentication of this user increases with the increase in the number of wrong passwords entered, until the correct logon is recalculated. As follows:

As you can see, the verification time is getting longer and longer. If you continue to increase the number of wrong logins, the verification time will continue to grow. If multiple sessions are connected to the database with the wrong password at the same time, it will cause the user to log on to HANG. To disable this feature, you can set the following events:

1 'scope = SPFILE;

3. sys users are not restricted by the PASSWORD_LIFE_TIME parameter.
4. After the user password expires, the user's status (dba_user.account_status) will not be updated to expired immediately. The user status will be updated only when the user tries to connect to the database.
5. User Password-related parameters

PASSWORD_LIFE_TIME:
Set the password validity period (number of days). Once the password expires, you must reset the password. The default is 180 days (11 GB, 10 gUNLIMITED ).
PASSWORD_REUSE_TIME:
Many systems do not allow users to re-enable previous passwords. This resource item sets the number of days after which a password expires before the user can use the password again. The default value is UNLIMITED.
PASSWORD_REUSE_MAX:
The number of times that a previously used password must be reset before it is re-enabled ).
PASSWORD_LOCK_TIME:
Set the number of days when the account is locked (when the logon fails to reach FAILED_LOGIN_ATTEMPTS ).
PASSWORD_GRACE_TIME:
Set the number of days before the password expires. If the password is returned after it expires, a warning message is displayed during logon, indicating the number of days. If the password is not modified within a grace period, the password will become invalid.
PASSWORD_VERITY_FUNCTION:
This resource item allows you to call a PL/SQL to verify the password. Oracle has provided scripts for this application, but users can develop their own verification scripts as long as they wish. This parameter is set to the name of the PL/SQL function. The default value is NULL.
FAILED_LOGIN_ATTEMPTS:
Set the number of failures that can be performed when logging on to the Oracle database. Once a user attempts to log on to the database to reach this value, the user's account is locked and can only be unlocked by the DBA.

This article permanently updates the link address: