Monday, May 21, 2012

How to get rid of "ora-28000:the account is locked"?

From oracle 10gR2 onwards the db parameter 'FAILED_LOGIN_ATTEMPTS' value has been changed from 'unlimited' to 10. This is unnecessary for a development DB, because it may cause the user account to be locked frequently upon invalid credential input.

To get rid of this we can specify the FAILED_LOGIN_ATTEMPTS to be 'unlimited'.

Login as a system user...

SQL>connect SYSTEM/SYS;

Assuming username is "DEV_USER", we must first find the db profile of it...

SQL>select profile from dba_users where username = 'DEV_USER';

Assuming the returned value is 'DEFAULT', we can change the limit of 'FAILED_LOGIN_ATTEMPTS' for the profile DEFAULT as below...

SQL>alter profile DEFAULT limit FAILED_LOGIN_ATTEMPTS unlimited;

If the account is already locked it can be unlocked as below...

SQL> alter user DEV_USER account unlock; 

Ref:
http://www.xinotes.org/notes/note/717/
https://forums.oracle.com/forums/thread.jspa?threadID=330359

No comments: