Wednesday 10 September 2014

User account is locked frequently in oracle returns ORA-28000...!!




  • Find the default profile of the database user.
  • Check the value for FAILED_LOGIN_ATTEMPTS in the profile.
  • Edit the profile and change the value / or create a new profile with new value.
  • Assigned to the user.

Select profile from dba_users where username = 'USERNAME';
Select resource_name, resource_type, limit From dba_profiles Where profile='DEFAULT';

Look at the parameter FAILED_LOGIN_ATTEMPTS ,  by default it is 10 prior 11g it used to be "unlimited".

If you like to change the profile option edit the specific (here default) profile, but editing in default profile is not suggested for security reasons.

It is a good idea to create a new profile with options of that in the default profile and change the FAILED_LOGIN_ATTEMPTS to "unlimited".

***** To Create a Profile *****

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 Profile to a user *****
Alter user mylogin  profile Default_new;

Now you would not face the issue of user account locking frequently.


To check who has tried to loggin with wrong password..

*****Execute the below query *****
Select username, userhost, terminal, timestamp, action_name,             
          logoff_time, sessionid, os_process, returncode
From  dba_audit_session
Order by timestamp desc;


Note: The column  Return code is the error we get as ORA-XXXX
for example if returncode is  28000 which means ORA-28000
Error ORA-28000 means "User Accout Locked"
Error ORA-1017  means "invalid username/password; logon denied"


***** For more detail info *****
Select  os_username, username, userhost, terminal, timestamp,
           owner, obj_name,action, action_name, logoff_time,
           comment_text, sessionid, returncode, os_process, sql_text
From dba_audit_trail
Where username like 'username'
Order by timestamp desc;

**********

No comments:

Post a Comment