Skip to content
April 3, 2015 / doganay

LOGON TRIGGER WORKS BUT INACTIVE SESSION REMAINS

Let’s say you are using a logon trigger like this:

CREATE OR REPLACE TRIGGER sys.trg_pre_logon_ip
 AFTER
  LOGON
 ON DATABASE
DECLARE
    v_ip         VARCHAR2 (20);
    v_terminal   VARCHAR2 (30);
    v_username   VARCHAR2 (30);
    v_dummy      VARCHAR2 (1);
BEGIN
    v_username := NVL (SYS_CONTEXT ('USERENV', 'SESSION_USER'), 'SYS');
    v_ip := SYS_CONTEXT ('USERENV', 'IP_ADDRESS');
    v_terminal := SYS_CONTEXT ('USERENV', 'TERMINAL');

    IF v_username NOT IN (
'ANONYMOUS',
'APPQOSSYS',
'DBSNMP',
'DIP',
'EXFSYS',
'ORACLE_OCM',
'OUTLN',
'SYS',
'SYSTEM',
'WMSYS',
'XDB',
'XS$NULL')
    THEN
        BEGIN
            SELECT 1
              INTO v_dummy
              FROM DUAL
             WHERE (v_ip not like '<banned_ip_block1>%' and v_ip not like '<banned_ip_block2>%') or v_ip is null;
        EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
                raise_application_error (-20000, 'YOU CAN NOT CONNECT VIA USER '||v_username||' on IP: '||v_ip);
        END;
    END IF;
END trg_pre_logon_ip;
/

Everything is fine, you ban users by raising a message.
But if sqlplus or sqlplusw is used the situation is a bit different.
If they try to connect using sqlplus or sqlplusw, they’re been raised the same message; he can not do anything, he thinks he was disconnected.

But!!!

If he does not close sqlplus/sqlplusw screen or does not run exit command.
Look in v$session you see an inactive session.

After idle_time of that user’s profile plus 1-2 minutes (I do not know what this time is?)
the session becomes “SNIPED”.
You may kill sniped sessions using a job of course.
But be careful, if this user has an unlimited session_per_user profile he can fill up sessions
to the sessions limit.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: