Wednesday, August 8, 2012

Concurrent User Login Denied from Different Machine


create or replace trigger TG_MULTILOGIN
after logon on database
declare
cnt pls_integer;
begin
select count(*) into cnt from v$session
where username=sys_context('userenv','session_user')  and terminal !=sys_context('userenv','terminal') and status like '%ACTIVE%';
if cnt >= 1 then
raise_application_error (-20001, 'You are already connected on another machine');
end if;
exception
when no_data_found then raise;
end;
/


RAISE_APPLICATION_ERROR (-20001, 'You are not allowed to logon'); 
wont work for a  user who has ADMINISTER DATABASE TRIGGER privilege. 

The ADMINISTER DATABASE TRIGGER privilege allows you to create database-level triggers (server error, login, and logout triggers). It also allows you to log in regardless of errors thrown by a login trigger as a failsafe. If you inadvertently coded your login trigger to throw an error no matter who was logging in, for example, you need to allow someone to log in to fix the trigger.

if someone is exit from oracle without logout then record is still present in v$session then  error occur.

we can use status like '%ACTIVE%'; to avoid killed and sniped session


No comments:

Post a Comment

Followers