Does Oracle have to be configured to log ORA-01017 invalid username/password errors

Question

I am hunting down failed Oracle logins - ORA-01017 invalid username/password errors - and i cannot find them in the alert_SID.log file. I do see other ORA-* errors. Are ORA-01017 errors not logged by default? I am reading about configuring auditing, but would think that unsuccessful logins would be logged automatically. This is for an Oracle 11.2g database and an Oracle 19.0c database.

Answer 1

I had a similar issue. So many people using the same database users, sometimes someone was entering wrong the password so many times that it locked the user. ORA-1017 are not audited by default, neither logged. You either enable audit or you create your own logon trigger for this, which was what I did

To audit that operation, I designed this trigger

CREATE OR REPLACE TRIGGER SYS.logon_denied_audit AFTER SERVERERROR ON DATABASE
DECLARE
 l_message varchar2(2000) := 'ORA-1017: Invalid username/password: logon denied';
BEGIN
 -- ORA-1017: invalid username/password; logon denied
 IF (IS_SERVERERROR(1017)) THEN
    insert into my_audit_table
    (
    RAC_INSTANCE       ,
    SERVICE_NAME       ,
    SESSION_ID         ,
    ORACLE_USER        ,
    OS_USER            ,
    TERMINAL           ,
    PROGRAM            ,
    ACTION_TIME        ,
    ACTION             ,
    IP_ADDRESS         ,
    CLIENT_IDENTIFIER  ,
    ERROR              ,
    IS_LIMITED
    )
    values
    (
    SYS_CONTEXT ('USERENV', 'INSTANCE'),
    nvl(UPPER(SYS_CONTEXT ('USERENV', 'SERVICE_NAME')),'Service Unknown') ,
    9999 ,
    SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY'),
    UPPER(SYS_CONTEXT('USERENV', 'OS_USER')),
    nvl(SYS_CONTEXT ('USERENV', 'HOST'),'Unknown Host'),
    nvl(SYS_CONTEXT('USERENV','MODULE'),'Module unknown'),
    systimestamp,
    'LOGON',
    nvl(sys_context ('USERENV', 'IP_ADDRESS'),'Unknown IP'),
    null,
    l_message ,
    'N'
    );
    end if;
end;
/

Thereby, I can log unsuccessful attempts to login in the database. By the way, it works the same in 11g than in 19c

Answer 2

In general, configuring auditing is the preferred way to go with this. You can create a custom trigger, but I believe they are less reliable and more likely to be compromised than Oracle's built-in method for tracking. Additionally, custom security objects should should always be placed in a dedicated schema. Never create custom objects in the SYS schema.

I wrote a blog recently on basic auditing configuration entitled "Auditing By The Numbers". You can also find resources here: https://oracle-base.com/articles/8i/auditing. The easiest way to enable auditing (assuming you're in Oracle 12c or later) for logins would be the following, executed as a user with DBA or SYSDBA privileges:

-- audit all user logon and logoff attempts:
create audit policy stig_user_logon_actions actions logon, logoff;
audit policy stig_user_logon_actions;

For Oracle 11g:

alter system set audit_trail=DB scope=spfile;
[restart database]
audit create session;

Then look at your audit trail in the DBA_AUDIT_TRAIL view, or DBA_AUDIT_SESSION (11g).