CREATE OR REPLACE VIEW ISMV_SESSION_RESTRICT ( USERID,
USERNAME, RESTRICT, RESTRICT_NO, SID,
SERIAL# ) AS select
userid,
username,
restrict,
min(restrict_no),
sid,
serial#
from
(
(select
t1.userid,
t3.username,
'A' restrict,
nvl(t2.restrict_no, 998) restrict_no,
t3.sid,
t3.serial#
from
ism_user t1,
ism_active_task_restrict t2,
sys.v_$session t3
where
t2.userid(+) = t1.userid and
t3.username = t1.username(+) and
t3.status <> 'KILLED' and
nvl(t1.status, 'A') = 'A'
and nvl(nvl(t2.osuser, t3.osuser), 'X') = nvl(t3.osuser, 'X')
and nvl(nvl(t2.terminal, t3.terminal), 'X') = nvl(t3.terminal, 'X')
and nvl(substr(t3.machine, 1, length(t3.machine) - 1), 'X') = nvl(nvl(t2.machine, substr(t3.machine, 1, length(t3.machine) - 1)), 'X')
and upper(nvl(nvl(t2.program, t3.program), 'X')) like '%' || upper(nvl(t3.program, 'X')) || '%'
and 86400 * (sysdate - trunc(sysdate)) >= nvl(t2.from_dt, 0)
and 86400 * (sysdate - trunc(sysdate)) <= nvl(t2.to_dt, 86401)
and 86400 * (sysdate - t3.logon_time) <= nvl(t2.idle_time, 86400 * (sysdate - t3.logon_time))
)
union
(
select
t1.userid,
t3.username,
'D' restrict,
nvl(t2.restrict_no, 999) restrict_no,
t3.sid,
t3.serial#
from
ism_user t1,
ism_active_task_restrict t2,
sys.v_$session t3
where
t2.userid(+) = t1.userid and
t3.username = t1.username(+) and
t3.status <> 'KILLED' and
(
nvl(t1.status, 'A') <> 'A'
or nvl(nvl(t2.osuser, t3.osuser), 'X') <> nvl(t3.osuser, 'X')
or nvl(nvl(t2.terminal, t3.terminal), 'X') <> nvl(t3.terminal, 'X')
or nvl(substr(t3.machine, 1, length(t3.machine) - 1), 'X') <> nvl(nvl(t2.machine, substr(t3.machine, 1, length(t3.machine) - 1)), 'X')
or upper(nvl(nvl(t2.program, t3.program), 'X')) not like '%' || upper(nvl(t3.program, 'X')) || '%'
or 86400 * (sysdate - trunc(sysdate)) < nvl(t2.from_dt, 0)
or 86400 * (sysdate - trunc(sysdate)) > nvl(t2.to_dt, 86401)
or 86400 * (sysdate - t3.logon_time) > nvl(t2.idle_time, 86400 * (sysdate - t3.logon_time))
)
)
) restr1
group by userid, username, restrict, sid, serial#


CREATE TABLE ISM_ACTIVE_TASK_RESTRICT (
USERID NUMBER (6) NOT NULL,
RESTRICT_NO NUMBER (3) NOT NULL,
OSUSER VARCHAR2 (30),
MACHINE VARCHAR2 (64),
TERMINAL VARCHAR2 (16),
PROGRAM VARCHAR2 (64),
FROM_DT NUMBER (10),
TO_DT NUMBER (10),
IDLE_TIME NUMBER (10) ) ;



procedure j_restrict_task_kill as
cursor c_drop is select t1.*, t2.terminal, t2.machine, t2.osuser from ismv_session_restrict t1, sys.v_$session t2 where t1.restrict = 'D' and t2.sid = t1.sid and t2.serial# = t1.serial# and t2.username = t1.username;
v_permit ismv_session_restrict%rowtype;
v_flag boolean;
v_text varchar2(2000);
v_session sys.v_$session%rowtype;
v_cursor number;
v_prom number;
begin
begin
select distinct username into v_text from ismv_session_restrict where username = 'ISM_RESTRICT_STOP' and restrict = 'A';
aum.p_syslog_module('BTBJ0001', 'ISM restrict connection checking is stopped by supervisor connection: ISM_RESTRICT_STOP.');
commit;
exception
when no_data_found then
for v_drop in c_drop loop
begin
select * into v_permit from ismv_session_restrict where restrict = 'A' and username = v_drop.username and sid = v_drop.sid and serial# = v_drop.serial#;
if v_permit.restrict_no <= v_drop.restrict_no then
v_flag := false;
else
v_flag := true;
end if;
exception
when no_data_found then
v_flag := true;
end;

if v_flag then
v_text := 'USER = ' || v_drop.username || ' (' || v_drop.sid || ', ' || v_drop.serial# || ') TERMINAL = <' || v_drop.terminal || '>, MACHINE = <' || v_drop.machine || '>, OSUSER = <' || v_drop.osuser || '>.';
aum.p_syslog_module('BTBJ0001', 'Not allowed connection! ' || v_text);

v_text := 'ALTER SYSTEM KILL SESSION ''' || v_drop.sid || ', ' || v_drop.serial# || '''';
v_cursor := dbms_sql.open_cursor;
dbms_sql.parse(v_cursor, v_text, dbms_sql.v7);
aum.p_syslog_module('BTBJ0001', 'Attemt to perform SQL query: ' || v_text);
commit;
begin
v_prom := dbms_sql.execute(v_cursor);
dbms_sql.close_cursor(v_cursor);
aum.p_syslog_module('BTBJ0001', 'SQL query executed: ' || v_text);
commit;
begin
select * into v_session from sys.v_$session where sid = v_drop.sid and serial# = v_drop.serial# and username = v_drop.username;
aum.p_syslog_module('BTBJ0001', 'Not allowed connection for ' || v_drop.username || ' (' || v_drop.sid || ', ' || v_drop.serial# || ') status: ' || v_session.status);
commit;
exception
when no_data_found then
aum.p_syslog_module('BTBJ0001', 'Not allowed connection for ' || v_drop.username || ' (' || v_drop.sid || ', ' || v_drop.serial# || ') status: DISCONNECTED');
commit;
end;
exception
when others then
aum.p_syslog_module('BTBJ0001', 'SQL query execution trubles! ' || sqlerrm);
commit;
end;
end if;
end loop;
end;
end;
END ISM;

------------------------------------------------
Остальное додумывайте сами, исправляйте, обсуждайте. Этот код не претендует на гениальность, но все же тоже способ решить проблему. В данном коде есть уязвимые места: вызывается как джоб, переодически и если другой джоб запущен от имени реально работающего пользователя, то он будет убит, если конечно ему не прописать полный доступ.