(Tested on an Oracle 9.2.0.7.0 database.)
This can be used to limit the number of times a user can enter an incorrect password. First create a test user and show what profile he is using:
1 grant create session to andrew
2* identified by reid
SQL> /
Grant succeeded.
SQL> select profile from dba_users
2 where username = 'ANDREW';
PROFILE
------------------------------
DEFAULT
SQL>
Then change that profile so that only two incorrect password attempts are allowed:
SQL> alter profile default
2 limit failed_login_attempts 2;
Profile altered.
SQL>
To demonstrate the limit, the user must then try to login twice with the wrong password:
SQL> conn andrew/wrong_password
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn andrew/wrong_password
ERROR:
ORA-01017: invalid username/password; logon denied
SQL>
If he tries to login again he will see that the account is locked:
SQL> conn andrew/wrong_password
ERROR:
ORA-28000: the account is locked
SQL>
Note that this limit still applies even if resource_limit is set to false:
SQL> col value format a20
SQL> l
1 select value from v$parameter
2* where name = 'resource_limit'
SQL> /
VALUE
--------------------
FALSE
SQL>
Also note that the failed login attempts need to be consecutive. A successful login attempt sets the count of failed login attempts back to zero:
SQL> conn / as sysdba
Connected.
SQL> alter user andrew account unlock;
User altered.
SQL> conn andrew/wrong_password
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn andrew/reid
Connected.
SQL> conn andrew/wrong_password
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn andrew/reid
Connected.
SQL> conn andrew/wrong_password
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn andrew/reid
Connected.
SQL>
