Thursday 29 August 2013

ORA-28007: the password cannot be reused

SQL> alter user system identified by mufti@143;
alter user system identified by mufti@143
*
ERROR at line 1:
ORA-28007: the password cannot be reused


Check user profile
============
SQL> select USERNAME, PROFILE, ACCOUNT_STATUS from dba_users where username = 'SYSTEM';
USERNAME  PROFILE        ACCOUNT_STATUS
------------------    ------------------ --------------------------------
SYSTEM  USER_PROFILE  OPEN

SQL> select * from dba_profiles where profile='USER_PROFILE';

PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
USER_PROFILE SESSIONS_PER_USER KERNEL DEFAULT
USER_PROFILE CPU_PER_SESSION KERNEL DEFAULT
USER_PROFILE CPU_PER_CALL KERNEL DEFAULT
USER_PROFILE LOGICAL_READS_PER_SESSION KERNEL DEFAULT
USER_PROFILE LOGICAL_READS_PER_CALL KERNEL DEFAULT
USER_PROFILE IDLE_TIME KERNEL DEFAULT
USER_PROFILE CONNECT_TIME KERNEL DEFAULT
USER_PROFILE PRIVATE_SGA KERNEL DEFAULT
USER_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULT
USER_PROFILE PASSWORD_LIFE_TIME PASSWORD DEFAULT

PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
USER_PROFILE PASSWORD_REUSE_TIME PASSWORD DEFAULT
USER_PROFILE PASSWORD_REUSE_MAX PASSWORD DEFAULT
USER_PROFILE PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
USER_PROFILE PASSWORD_LOCK_TIME PASSWORD DEFAULT
USER_PROFILE PASSWORD_GRACE_TIME PASSWORD DEFAULT


SQL>alter profile USER_PROFILE limit password_verify_function NULL;

SQL> alter profile USER_PROFILE limit PASSWORD_REUSE_MAX unlimited;

Profile altered.

SQL> alter user system identified by mufti@143;

User altered.

SQL> select * from dba_profiles where profile='USER_PROFILE';

PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
USER_PROFILE SESSIONS_PER_USER KERNEL DEFAULT
USER_PROFILE CPU_PER_SESSION KERNEL DEFAULT
USER_PROFILE CPU_PER_CALL KERNEL DEFAULT
USER_PROFILE LOGICAL_READS_PER_SESSION KERNEL DEFAULT
USER_PROFILE LOGICAL_READS_PER_CALL KERNEL DEFAULT
USER_PROFILE IDLE_TIME KERNEL DEFAULT
USER_PROFILE CONNECT_TIME KERNEL DEFAULT
USER_PROFILE PRIVATE_SGA KERNEL DEFAULT
USER_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULT
USER_PROFILE PASSWORD_LIFE_TIME PASSWORD DEFAULT

PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
USER_PROFILE PASSWORD_REUSE_TIME PASSWORD DEFAULT
USER_PROFILE PASSWORD_REUSE_MAX PASSWORD UNLIMITED
USER_PROFILE PASSWORD_VERIFY_FUNCTION PASSWORD NULL
USER_PROFILE PASSWORD_LOCK_TIME PASSWORD DEFAULT
USER_PROFILE PASSWORD_GRACE_TIME PASSWORD DEFAULT

16 rows selected.

SQL>


Once the password is changed make it to default...
SQL>alter profile USER_PROFILE limit password_verify_function DEFAULT;

SQL>alter profile USER_PROFILE limit PASSWORD_REUSE_MAX DEFAULT;

3 comments: