ORA-28002/ORA-28000 solve 11g password expiration, password wrong lock user problems -- modify defaultprofil

ORA-28002ORA-28000 to solve the problem of locking the user after the password expires and wrong password -- modify defaultprofile

ORA-28002/ORA-28000 address 11g password expiration, password wrong lock user problems-Modify default profile

A ORA-28002: the password will expire within 7 days

In the 11G, the default user password needs to be modified every three months (180 days. The property value is defined in profile. The following procedure is to set the property value to unlimited, that is, the password will never expire.

1. view the user's profile
SQL> select username, profile from dba_users;

USERNAME PROFILE

Bytes ------------------------------------------------------------------------------------------------------------------------

SYS DEFAULT

SYSTEM DEFAULT

SHARON DEFAULT

GGS DEFAULT

TEST DEFAULT

OUTLN DEFAULT

MGMT_VIEW DEFAULT

FLOWS_FILES DEFAULT

MDSYS DEFAULT

ORDSYS DEFAULT

EXFSYS DEFAULT

....

2. Check the password validity period of the profile.
SQL> SELECT * FROM dba_profiles s WHEREs. profile = 'default' AND resource_name = 'password _ LIFE_TIME ';

PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT

------------------------------------------------------------

DEFAULT PASSWORD_LIFE_TIME PASSWORD 180

3. Change the password validity period (not limited)
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIMEUNLIMITED;

Profilealtered.

4. Reset the user password (original password)
SQL> alter user sharon identified by Oracle; -- no new password is required

Useraltered.

ORA-28000 the account is locked

The defaultprofile of 11g specifies that the user will be locked after the wrong password is entered. The following step is to set the attribute value to UNLIMITED, that is, the user will not be locked no matter how many wrong passwords are entered.

1. Check the user's proifle, which is generally default:
SQL> select username, profile from dba_users;

USERNAME PROFILE

Bytes ------------------------------------------------------------------------------------------------------------------------

SYS DEFAULT

SYSTEM DEFAULT

SHARON DEFAULT

GGS DEFAULT

TEST DEFAULT

OUTLN DEFAULT

MGMT_VIEW DEFAULT

FLOWS_FILES DEFAULT

MDSYS DEFAULT

ORDSYS DEFAULT

EXFSYS DEFAULT

....

2. view the password validity period settings of the specified profile (such as default:
SQL> SELECT * FROM dba_profiles s WHEREs. profile = 'default' AND resource_name = 'failed _ LOGIN_ATTEMPTS ';

PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT

-----------------------------------------------------------------

DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10

-- If you enter the wrong password for 10 times, the user will be locked.

3. Change the number of failed logon attempts from the default 10 to "unlimited ":
SQL> ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTSUNLIMITED;

Profilealtered.

4. Unlock locked users
SQL> alter user sharon identified by oracle accountunlock;

Useraltered.

Recommended reading:

ORA-01172, ORA-01151 error handling

ORA-00600 [2662] troubleshooting

Troubleshooting for ORA-01078 and LRM-00109

Notes on ORA-00471 Processing Methods

ORA-00314, redolog corruption, or missing Handling Methods

Solution to ORA-00257 archive logs being too large to store