If you are on Oracle 10g and frequently facing the issue "Oracle user account LOCKED(TIMED)" with any user account then.
You need to observe the profile assigned to the particular user
select username,profile from dba_profiles where username='MYLOGIN';
If you find that the profile is DEFAULT then you have to observe one thing
SELECT resource_name, resource_type, limit FROM dba_profiles WHERE profile='DEFAULT';
COMPOSITE_LIMIT KERNEL UNLIMITED
SESSIONS_PER_USER KERNEL UNLIMITED
CPU_PER_SESSION KERNEL UNLIMITED
CPU_PER_CALL KERNEL UNLIMITED
LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
LOGICAL_READS_PER_CALL KERNEL UNLIMITED
IDLE_TIME KERNEL UNLIMITED
CONNECT_TIME KERNEL UNLIMITED
PRIVATE_SGA KERNEL UNLIMITED
FAILED_LOGIN_ATTEMPTS PASSWORD 10
PASSWORD_LIFE_TIME PASSWORD UNLIMITED
PASSWORD_REUSE_TIME PASSWORD UNLIMITED
PASSWORD_REUSE_MAX PASSWORD UNLIMITED
PASSWORD_VERIFY_FUNCTION PASSWORD NULL
PASSWORD_LOCK_TIME PASSWORD UNLIMITED
PASSWORD_GRACE_TIME PASSWORD UNLIMITED
The FAILED_LOGIN_ATTEMPTS PASSWORD is 10 in the previous versions it used to be UNLIMITED.
If you like to change the profile option edit the default profile, but its not suggested for security reasons.
You can create a new profile with options of that in the default profile and change the FAILED_LOGIN_ATTEMPTS PASSWORD to UNLIMITED.
CREATE PROFILE DEFAULT_NEW LIMIT
COMPOSITE_LIMIT KERNEL UNLIMITED
SESSIONS_PER_USER KERNEL UNLIMITED
CPU_PER_SESSION KERNEL UNLIMITED
CPU_PER_CALL KERNEL UNLIMITED
LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
LOGICAL_READS_PER_CALL KERNEL UNLIMITED
IDLE_TIME KERNEL UNLIMITED
CONNECT_TIME KERNEL UNLIMITED
PRIVATE_SGA KERNEL UNLIMITED
FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED PASSWORD_LIFE_TIME PASSWORD UNLIMITED
PASSWORD_REUSE_TIME PASSWORD UNLIMITED
PASSWORD_REUSE_MAX PASSWORD UNLIMITED
PASSWORD_VERIFY_FUNCTION PASSWORD NULL
PASSWORD_LOCK_TIME PASSWORD UNLIMITED
PASSWORD_GRACE_TIME PASSWORD UNLIMITED;
Assign this profile to the user
ALTER USER mylogin PROFILE DEFAULT_NEW;
Now you would not face the issue of user account locking frequently.
2 comments:
You can do this, but the bigger issue is should you do this? There is a VERY good reason that default profile is no longer unlimited. Do you really want to give some hacker unlimited attempts to crack a users password? Not in my world.
It looks like password_lock_time is also involved in LOCKED(TIMED) status.
http://shonythomas.blogspot.com/2012/05/oracle-11g-user-account-status.html
Post a Comment