Overview

This document describes security vulnerability countermeasures for the Access Management - Is a lockout policy set for a certain number of failed login attempts? (D-15) item.

Inspection Purpose

• Check whether the DBMS is configured with an account lockout policy for a certain number of failed login attempts.
• By setting an account lockout policy after a certain number of failed login attempts, unauthorized automated brute-force or dictionary attacks can be prevented, thus protecting user account passwords from leakage.

Inspection Criteria

Precautions

• You must proceed using an account with DBA privileges on the database.
• When limiting the number of login attempts for a database account, if the password is entered incorrectly the number of times allowed, the account may become locked when connecting with that account.
  TBR-17006: Account is locked., JDBC-17006:Account is locked.
• If a session connection was established before the account was locked, there is no issue; however, new session connections will be restricted.
• Be sure to review the application's database connection information before addressing this security vulnerability.

Method

Please ensure the Precautions have been reviewed before proceeding.

Verification

• Check the accounts requiring login attempt limits using the following SQL.
• If the limit is UNLIMITED, it means no login attempt limit has been applied.

-- Format for tbssql usage
set linesize 300
col username for a30
col profile for a30
col resource_name for a30
col limit for a30

-- SQL to check accounts without login attempt limits
select
    du.username, 
    du.profile, 
    dp.resource_name, 
    dp.limit
from 
    dba_users du, dba_profiles dp 
where 
    du.profile = dp.profile 
    and dp.resource_name='FAILED_LOGIN_ATTEMPTS'
    and dp.limit = 'UNLIMITED'
    -- Accounts in the du.username NOT IN clause are database system accounts
    and du.username not in ('SYS','SYSCAT','SYSGIS','OUTLN','PUBLIC','SYSBACKUP','WMSYS','LBACSYS')
order by
    dp.limit, du.profile, du.username;
  
ex) If the limit is UNLIMITED, the account has no login attempt limit applied
USERNAME        PROFILE                        RESOURCE_NAME                  LIMIT
--------------- ------------------------------ ------------------------------ ------------------------------
test1            DEFAULT                        FAILED_LOGIN_ATTEMPTS          UNLIMITED
test2            DEFAULT                        FAILED_LOGIN_ATTEMPTS          UNLIMITED
test3            DEFAULT                        FAILED_LOGIN_ATTEMPTS          UNLIMITED

Action

Please ensure the Precautions have been reviewed before proceeding.

Create PROFILE

• Do not modify the DEFAULT profile for rollback purposes; instead, create a new profile for use.
• Alternatively, you can add the FAILED_LOGIN_ATTEMPTS parameter to an existing profile in use.

When creating a new profile

create profile {profile name} limit FAILED_LOGIN_ATTEMPTS {limit count};
ex) create profile pf_security limit FAILED_LOGIN_ATTEMPTS 10;

How to add the FAILED_LOGIN_ATTEMPTS parameter to an existing profile
- If you apply it this way, you may skip the PROFILE application step.

alter profile {profile name} limit 
FAILED_LOGIN_ATTEMPTS 10;

ex) alter profile modify_profile limit
FAILED_LOGIN_ATTEMPTS 10;

Apply PROFILE

• You can apply the newly created profile as follows.
• The limit will apply to newly connected sessions immediately upon application.

alter user {user name} profile {profile name};
ex) alter user user_security profile pf_security;

Check Login Attempts

• After applying the profile, monitor the number of incorrect password attempts on the account over a certain period.
• If login_fail_count reaches the set FAILED_LOGIN_ATTEMPTS, the account will be locked. Use the following SQL to check:

-- Format for tbssql usage
set linesize 300
col username for a30
col login_fail_count for 99
col status for a20

-- SQL to check failed login attempts
select
    ddu.name username,
    ddu.lcount login_fail_count,
    du.account_status status
from 
    _dd_user ddu, dba_users du
where 
    -- Accounts in the du.username NOT IN clause are database system accounts
    du.username not in ('SYS','SYSCAT','SYSGIS','OUTLN','PUBLIC','SYSBACKUP','WMSYS','LBACSYS')
    and ddu.name = du.username
    /* To check specific accounts
     * and du.username = '{user name}'
     */
order by
    ddu.lcount;
       
ex) If LOCKED occurs, new session connections will result in errors
USERNAME                       LOG STATUS
------------------------------ --- --------------------
TEST1                            0 OPEN
TEST2                            0 OPEN
TEST3                            0 OPEN
USER_SECURITY                    5 LOCKED(TIMED)

• If the password is incorrect during login attempts, messages can be checked in the sys.log file.
• Among two logs, the log with the -17006 error can be used to identify which account attempted to connect.
  THROW. ec=ERROR_SECU_LOGIN_FAILED(-17001) [ Login failed: invalid user name or password.]
ERROR_SECU_ACCOUNT_LOCKED(-17006) during a user authentication process. client ip:127.0.0.1, port:52542, prog_name:tbsql, user_name:USER_SECURITY, osuser_name:root, terminal:

Rollback

This can be used if the account is locked after profile application or if the profile needs to be reverted.

Rollback PROFILE

• How to revert the profile.
• If you applied a newly created profile, change it back to the original profile.
• This also applies if you added the FAILED_LOGIN_ATTEMPTS parameter to an existing profile.

Rollback when a new profile was created

alter user {user name} profile default;
ex) alter user user_security profile default;

Rollback when the FAILED_LOGIN_ATTEMPTS parameter was added to an existing profile

alter profile {profile name} limit 
FAILED_LOGIN_ATTEMPTS unlimited;

ex) alter profile modify_profile limit
FAILED_LOGIN_ATTEMPTS unlimited;

Unlock USER

• If an account is locked, the application may show errors such as:
  TBR-17006: Account is locked., JDBC-17006:Account is locked.
• If the account is locked, unlock it using the following SQL.

alter user {user name} account unlock;
ex) alter user user_security account unlock;