SQL query to retrieve value of FAILED_LOGIN_ATTEMPTS

Document ID : KB000049683
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

The FAILED_LOGIN_ATTEMPTS value limits the number of failed login attempts allowed before an account is locked. Setting this value limits the ability of unauthorized users to guess passwords and alerts the DBA when password guessing has occurred (accounts display as locked). For non-interactive accounts, the number of failed logins should be set to an IAO approved value.

Solution:

Below is the SQL query for retrieving value of FAILED_LOGIN_ATTEMPTS

select profile||': '||limit from dba_profiles,
(select limit as def_login_attempts from dba_profiles
where profile = 'DEFAULT'
and resource_name = 'FAILED_LOGIN_ATTEMPTS')
where resource_name = 'FAILED_LOGIN_ATTEMPTS'
and replace(limit, 'DEFAULT', def_login_attempts) IN
('UNLIMITED', NULL)
or resource_name = 'FAILED_LOGIN_ATTEMPTS'
and to_number(decode(limit, 'UNLIMITED', 10, 'DEFAULT', 10, limit)) > 3;