- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
1) -- CHECK PASSWORD_LIFE_TIME, SHOULD BE 180 BY DEFAULT
SELECT * FROM DBA_PROFILES WHERE RESOURCE_NAME = 'PASSWORD_LIFE_TIME';
2) -- CHANGE IT TO UMLIMITED
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
3) -- GEN SCRIPT TO UPDATE PASSWORD AS THE CURRENT PASSWORD
select 'alter user "'||d.username||'" identified by values '''||u.password||''';' c
from dba_users d, sys.user$ u
where d.username = upper('&&username')
and u.user# = d.user_id;
4) -- UNLOCK THE USER
alter user CPADEMO account unlock;
5) -- CHECK THE USER, IT SHOULD BE OPNE NOW.
select username,account_status from dba_users;
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Solutions:
1. Connect to database using sys users.
2. Execute the following query
Sql > select * from dba_profiles;
the output of this query will be like.
PROFILE RESOURCE_NAME RESOURCE LIMIT
-------------------- ------------------------- -------- ---------------
MONITORING_PROFILE COMPOSITE_LIMIT KERNEL DEFAULT
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
MONITORING_PROFILE SESSIONS_PER_USER KERNEL DEFAULT
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
MONITORING_PROFILE CPU_PER_SESSION KERNEL DEFAULT
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
MONITORING_PROFILE CPU_PER_CALL KERNEL DEFAULT
DEFAULT CPU_PER_CALL KERNEL UNLIMITED
MONITORING_PROFILE LOGICAL_READS_PER_SESSION KERNEL DEFAULT
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
MONITORING_PROFILE LOGICAL_READS_PER_CALL KERNEL DEFAULT
PROFILE RESOURCE_NAME RESOURCE LIMIT
-------------------- ------------------------- -------- ---------------
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
MONITORING_PROFILE IDLE_TIME KERNEL DEFAULT
DEFAULT IDLE_TIME KERNEL UNLIMITED
MONITORING_PROFILE CONNECT_TIME KERNEL DEFAULT
DEFAULT CONNECT_TIME KERNEL UNLIMITED
MONITORING_PROFILE PRIVATE_SGA KERNEL DEFAULT
DEFAULT PRIVATE_SGA KERNEL UNLIMITED
MONITORING_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10
MONITORING_PROFILE PASSWORD_LIFE_TIME PASSWORD DEFAULT
DEFAULT PASSWORD_LIFE_TIME PASSWORD 180
PROFILE RESOURCE_NAME RESOURCE LIMIT
-------------------- ------------------------- -------- ---------------
MONITORING_PROFILE PASSWORD_REUSE_TIME PASSWORD DEFAULT
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
MONITORING_PROFILE PASSWORD_REUSE_MAX PASSWORD DEFAULT
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
MONITORING_PROFILE PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
MONITORING_PROFILE PASSWORD_LOCK_TIME PASSWORD DEFAULT
DEFAULT PASSWORD_LOCK_TIME PASSWORD 1
MONITORING_PROFILE PASSWORD_GRACE_TIME PASSWORD DEFAULT
DEFAULT PASSWORD_GRACE_TIME PASSWORD 7
32 rows selected.
Here PASSWORD_LIFE_TIME field is responsible for expiring of password after 180 days.
3. execute following command to disable this feature:
Sql> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
4. Now crosscheck for disabling of this feature.
Sql > select * from dba_profiles;
The value in PASSWORD_LIFE_TIME has changed to unlimited. Now password will never expire.
5. Now reset the password of locked user. Here are two options to reset password. Either you can keep same password or you can give a new password.
To Keep same Password:
SQL> select 'alter user "'||d.username||'" identified by values '''||u.password||''';' c
from dba_users d, sys.user$ u
where d.username = upper('&&username')
and u.user# = d.user_id;
Enter value for username: scott
old 3: where d.username = upper('&&username')
new 3: where d.username = upper('scott')
C
--------------------------------------------------------------------------------
alter user "SCOTT" identified by values 'F894844C34402B67';
SQL> alter user "SCOTT" identified by values 'F894844C34402B67';
To give a new Password:
sql> alter user [user_name] identified by [password];
5. Now reset the password of locked user. Here are two options to reset password. Either you can keep same password or you can give a new password.
To Keep same Password:
SQL> select 'alter user "'||d.username||'" identified by values '''||u.password||''';' c
from dba_users d, sys.user$ u
where d.username = upper('&&username')
and u.user# = d.user_id;
Enter value for username: scott
old 3: where d.username = upper('&&username')
new 3: where d.username = upper('scott')
C
--------------------------------------------------------------------------------
alter user "SCOTT" identified by values 'F894844C34402B67';
SQL> alter user "SCOTT" identified by values 'F894844C34402B67';
To give a new Password:
sql> alter user [user_name] identified by [password];
6. Unlock user account using below command
sql> alter user [User_name] account unlock;
7. Crosscheck by value of accout_status field in dba_users view.
sql> select username,account_status from dba_users;
The value of account_status filed should by "OPEN" for corresponding user.
沒有留言:
發佈留言