Trigger for checking account lock

jl66
jl66 used Ask the Experts™
on
Want to implement the following.
When the user tries to login, enters the wrong passwords, and the account is logged, it will notify a DBA about the locking account? If we can trigger in the right moment, the notification part is easy to implement.

I need some idea how and where to implement the trigger or similar thing ASAP?  
If any guru can shed some light on it (links or just ideas), I will greatly appreciate it.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
I don't think there is an event you can trigger or audit for the locking of an account.

You can track failed login attempts but I don't think there is a way to capture the actual locking event.
David VanZandtOracle Database Administrator III
Commented:
I don't particularly imagine your DBA is sitting around just waiting for an email to appear with a locked account notice.  As it happens in my current shop, any such reset requires an audit trail for the incident and the resolution.  Is the account being locked routinely, suggesting a hack effort, for example.

We do run and document the log of such attempts / failures with their codes, in order to distinguish between locked accounts, wrong password, etc.

Say more about what you want to accomplish as the result of the notification.
Most Valuable Expert 2011
Top Expert 2012
Commented:
you can't use a trigger because LOGON triggers only fire after a successful logon, so obviously a wrong password can't be triggered.


however, you could have a job run periodically by dbms_scheduler to check the audit trail for LOGON attempts in DBA_AUDIT_SESSION.

check the return code,  if it's 0 it's a successful logon.
if it's not 0 that indicates a failed logon.

for example 1017 would indicate the user logged in with the wrong password
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

jl66Consultant

Author

Commented:
Thanks a lot for the info and tips.
sdstuber--Back to "for example 1017 would indicate the user logged in with the wrong password ". which error number corresponds to locked account?
Most Valuable Expert 2011
Top Expert 2012
Commented:
28000 is a locked account
Most Valuable Expert 2011
Top Expert 2012
Commented:
example of a monitoring procedure and job
the procedure would need to be owned by a schema that has select privilege on the dba_audit_session view directly, (i.e. not through a role)

I have not tested this exact code but it should provide an outline of how to approach the task
The idea is it would look each day at 8am and send a report of all failed logins since 8am the previous day.


CREATE OR REPLACE PROCEDURE failed_logon_monitor
IS
    v_message      CLOB := EMPTY_CLOB();
    v_connection   UTL_SMTP.connection;
    v_len          INTEGER;
    v_index        INTEGER;
    v_cnt          INTEGER := 0;
BEGIN
    FOR x IN (  SELECT os_username,
                       username,
                       userhost,
                       terminal,
                       timestamp,
                       returncode,
                       instance_number
                  FROM dba_audit_session
                 WHERE action_name = 'LOGON' AND returncode != 0 AND timestamp > TRUNC(SYSDATE) - 1 + 8 / 24
              ORDER BY timestamp)
    LOOP
        v_cnt := v_cnt + 1;
        v_message :=
               v_message
            || TO_CHAR(x.timestamp, 'yyyy-mm-dd hh24:mi:ss')
            || ' Instance: '
            || x.instance_number
            || ' '
            || x.os_username
            || ' '
            || x.username
            || ' '
            || x.userhost
            || ' '
            || x.terminal
            || ' Error: '
            || x.returncode
            || UTL_TCP.crlf;
    END LOOP;

    IF v_cnt > 0
    THEN
        v_connection := UTL_SMTP.open_connection('your.email.server.com');
        UTL_SMTP.helo(v_connection, 'your.domain.com');
        UTL_SMTP.mail(v_connection, 'your_return_address@your.domain.com');
        UTL_SMTP.rcpt(v_connection, 'your_recipient@your.domain.com');
        UTL_SMTP.open_data(v_connection);

        UTL_SMTP.write_data(v_connection, 'From: LOGON_MONITOR' || UTL_TCP.crlf);
        UTL_SMTP.write_data(v_connection, 'To: DBA' || UTL_TCP.crlf);
        UTL_SMTP.write_data(v_connection, 'Subject: Failed LOGON attempts' || UTL_TCP.crlf);
        UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);

        UTL_SMTP.write_data(v_connection, 'Failed logon attempts: ' || v_cnt || UTL_TCP.crlf || UTL_TCP.crlf);

        v_len := DBMS_LOB.getlength(v_message);
        v_index := 1;

        WHILE v_index <= v_len
        LOOP
            UTL_SMTP.write_data(v_connection, DBMS_LOB.SUBSTR(v_message, 32000, v_index));
            v_index := v_index + 32000;
        END LOOP;

        UTL_SMTP.close_data(v_connection);
        UTL_SMTP.quit(v_connection);
    END IF;
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack);
END;

Open in new window


BEGIN
    DBMS_SCHEDULER.create_job(
        job_name          => 'FAILED_LOGON_ALERT',
        job_type          => 'STORED_PROCEDURE',
        job_action        => 'failed_logon_monitor;',
        start_date        => SYSDATE,
        repeat_interval   => 'FREQ=daily;BYHOUR=8;BYMINUTE=0',
        enabled           => TRUE,
        comments          => 'Daily report of failed logon attempts'
    );
END;
/

Open in new window

jl66Consultant

Author

Commented:
sdstuber --Thanks a lot for the code.  In 12c unified auditing some views and tables have been changed. However the idea is good. Appreciate it.
Most Valuable Expert 2011
Top Expert 2012
Commented:
yes, with unified auditing it does change some, but same idea, just change the cursor to use the unified auditing view and references to the  correct column names


  SELECT os_username,
         dbusername,
         userhost,
         terminal,
         event_timestamp,
         return_code,
         instance_id
    FROM unified_audit_trail
   WHERE action_name = 'LOGON' AND return_code != 0 AND event_timestamp > TRUNC(SYSDATE) - 1 + 8 / 24
ORDER BY event_timestamp

Open in new window

johnsoneSenior Oracle DBA
Commented:
You most certainly can trap failed logon attempts in a trigger.  It is not a logon trigger.  It is a servererror trigger.  I don't have access to my code that does it right now, but this seems like it is pretty close and should get you started:

https://community.oracle.com/thread/3903856?tstart=0

Not need to audit it, the trigger should be able to handle it all without any auditing.
Most Valuable Expert 2011
Top Expert 2012
Commented:
johnsone is correct,  it IS possible to capture logon errors.
Unfortunately, the amount of visible information within a db event trigger is limited compared to what is written with auditing.

An example of such a trigger might look something like this...


CREATE OR REPLACE TRIGGER logon_failure_notification
    AFTER SERVERERROR
    ON DATABASE
DECLARE
    v_connection   UTL_SMTP.connection;
BEGIN
    IF is_servererror(1017) -- invalid password
    OR is_servererror(1045) -- user lacks create session
    OR is_servererror(28000) -- account is locked
    OR is_servererror(28009) -- SYS needs sysdba or sysoper
    OR is_servererror(28013) -- password expired for proxy
    THEN
        v_connection := UTL_SMTP.open_connection('your.email.server.com');
        UTL_SMTP.helo(v_connection, 'your.domain.com');
        UTL_SMTP.mail(v_connection, 'your_return_address@your.domain.com');
        UTL_SMTP.rcpt(v_connection, 'your_recipient@your.domain.com');
        UTL_SMTP.open_data(v_connection);

        UTL_SMTP.write_data(v_connection, 'From: LOGON_MONITOR' || UTL_TCP.crlf);
        UTL_SMTP.write_data(v_connection, 'To: DBA' || UTL_TCP.crlf);
        UTL_SMTP.write_data(v_connection, 'Subject: Failed LOGON attempts' || UTL_TCP.crlf);
        UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);

        UTL_SMTP.write_data(
            v_connection,
               'Failed login on instance: '
            || ora_instance_num
            || ' at '
            || TO_CHAR(SYSTIMESTAMP, 'yyyy-mm-dd hh24:mi:ss')
            || UTL_TCP.crlf);

        FOR i IN 1 .. ora_server_error_depth
        LOOP
            UTL_SMTP.write_data(v_connection, ora_server_error_msg(i) || UTL_TCP.crlf);
        END LOOP;

        UTL_SMTP.close_data(v_connection);
        UTL_SMTP.quit(v_connection);
    END IF;
END;

Open in new window


Note, while it is possible to use a trigger, you might still want to go with periodic polling of audit information anyway.  The reason being you will have a controlled and predictable messaging.

Also, if an application or user is trying to login with an expired password, your dba may get email floods while the user/app tries over and over again.

Another option might be to write to a table instead of or in addition to auditing.  Send one email per hour/day/10-minute/whatever window,  but continue to collect the failed attempts.  That way your dba could still be notified immediately of a first attempt but won't get hammered with alerts if an application goes into a retry loop 100000 times trying to reconnect.
johnsoneSenior Oracle DBA

Commented:
Not sure what information is looking to be gathered, but most information is available in the dbevent trigger.  User you tried to log in as, where they came from, time, program, etc.  We used it quite a bit.
jl66Consultant

Author

Commented:
Very sorry I did not realize that the question was not closed.
jl66Consultant

Author

Commented:
Thanks a lot for everyone's tip/help.
jl66Consultant

Author

Commented:
Thanks a lot for everyone's tip/help.
jl66Consultant

Author

Commented:
Please close it for you. I tried very hard to close it, but failed.
jl66Consultant

Author

Commented:
Please close it for me.
sdstuber should get more than a half of points. The other users split the rest.
jl66Consultant

Author

Commented:
Thanks a lot for everyone.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial