Trigger for checking account lock

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.
jl66ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) 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 IIICommented:
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.
sdstuberCommented:
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
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

jl66ConsultantAuthor 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?
sdstuberCommented:
28000 is a locked account
sdstuberCommented:
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

jl66ConsultantAuthor 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.
sdstuberCommented:
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 DBACommented:
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.
sdstuberCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnsoneSenior Oracle DBACommented:
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.
jl66ConsultantAuthor Commented:
Very sorry I did not realize that the question was not closed.
jl66ConsultantAuthor Commented:
Thanks a lot for everyone's tip/help.
jl66ConsultantAuthor Commented:
Thanks a lot for everyone's tip/help.
jl66ConsultantAuthor Commented:
Please close it for you. I tried very hard to close it, but failed.
jl66ConsultantAuthor Commented:
Please close it for me.
sdstuber should get more than a half of points. The other users split the rest.
jl66ConsultantAuthor Commented:
Thanks a lot for everyone.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.