How to solve the problem of Oracle user password expiration

This article shows you how to solve the expiration of Oracle users' passwords. The content is concise and easy to understand, which will definitely brighten your eyes. I hope you can gain something through the detailed introduction of this article.

Cause:

This is caused by the default setting of "PASSWORD_LIFE_TIME= 180 days" in the default profile in oracle11g.

"FAILED_LOGIN_ATTEMPTS=10 times" is set by default in the default profile in oracle11g, which will cause this problem when the number of input password errors reaches the set value.

Solution:

For reason 1, talk about it->

1. Check which user's proifle is, usually default:

Sql > SELECT username,PROFILE FROM dba_users

2. Check the password validity settings for the specified profile (such as default):

Sql > SELECT * FROM dba_profiles 's WHERE s.profileholders default AND resource_name='PASSWORD_LIFE_TIME'

3. Change the password validity period from the default 180 days to "* system":

Sql > ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED

Note: there is no need to restart the database after modification, it will take effect immediately

After modification, accounts that have not been prompted for ORA-28002 warning will not receive the same prompt again; accounts that have been prompted must change their passwords again, for example:

$sqlplus / as sysdba sql > alter user smsc identified by-there is no need to change a new password

The password validity period is valid regardless of whether the oracle11g startup parameter resource_limit is set to false or true, so it must be modified in the above way. Please change the above account name according to the account name actually used.

For reason 2, talk about it->

1. Check which user's proifle is, usually default:

Sql > SELECT username,PROFILE FROM dba_users

2. Check the password validity settings for the specified profile (such as default):

Sql > SELECT * FROM dba_profiles 's WHERE s.profileholders default AND resource_name='FAILED_LOGIN_ATTEMPTS'

3. Change the number of failed login attempts from the default 10 to "*":

Sql > ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED

4. After modification, users who have not been prompted with ORA-28000 warning will not encounter the same prompt again.

Accounts that have been locked must be unlocked, for example:

$sqlplus / as sysdba sql > alter user smsc identified by oracle account unlock

5. The modified default profile should be as follows:

Sql > select * from dba_profiles WHERE dba_profiles.profile='DEFAULT'

*

Note: there is no need to restart the database after modification, it will take effect immediately

If ORA-28000 the account is locked. Error

Sql > alter user username account unlock; the above is how to solve the problem of Oracle user password expiration. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.