Yes. This works great. You posted your logon trigger before and I've used
it with considerable success (and modification). We (will) use the logon
trigger to ensure that a particular Oracle userid is logged on only from
one machine (no "sharing" of userids). We also allow certain exemptions,
either by userid or machine. I'll post our trigger but it's based on Mr.
Mercandante's ideas.
--create_LOGON_MULTIPLE_CHECK.sql
CREATE OR REPLACE TRIGGER LOGON_MULTIPLE_CHECK
AFTER logon ON DATABASE
DECLARE
client_info_str V$SESSION.CLIENT_INFO%TYPE;
var_username V$SESSION.USERNAME%TYPE := null;
kill_Login EXCEPTION;
PRAGMA EXCEPTION_INIT( kill_Login, -20997 );
begin
-- Set information string to uniquely identify this session
client_info_str := 'Logon_Trigger_' || LTRIM(dbms_random.value,'.');
-- Push information string into v$session
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(client_info_str);
-- query v$session and see if this user is logged on twice on machines that
are not exempt
begin
SELECT unique(b.username)
INTO var_username
-- look for more than one logon
from v$session a,v$session b where a.username=b.username
-- is the user exempt?
-- trim off the null character that occasionally gets added to the name
AND rtrim(A.USERNAME,CHR(0)) NOT IN (SELECT LME_exemptee FROM
LOGON_MULTIPLE_EXEMPTIONS WHERE LME_exemption_type = 'U')
-- look for two different machines
and a.machine != b.machine
-- are any of the machines exempt?
-- trim off the null character that occasionally gets added to the machine
name
AND rtrim(A.MACHINE,CHR(0)) NOT IN (SELECT LME_exemptee FROM
LOGON_MULTIPLE_EXEMPTIONS WHERE LME_exemption_type = 'M')
AND rtrim(B.MACHINE,CHR(0)) NOT IN (SELECT LME_exemptee FROM
LOGON_MULTIPLE_EXEMPTIONS WHERE LME_exemption_type = 'M')
-- make sure that we are looking at this logon session
and a.client_info=client_info_str;
EXCEPTION WHEN OTHERS THEN
NULL;
end;
-- if the user has a logon from more than 1 non-exempt machine then kill
this logon!
IF var_username is not null
THEN
RAISE kill_Login;
END IF;
EXCEPTION
WHEN kill_Login THEN
RAISE_APPLICATION_ERROR(-20997,'This account is logged on via
another machine!');
WHEN OTHERS THEN
null;
END;
/
Hope this helps and thanks Tom.
"Mercadante,
Thomas F" To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
<NDATFM cc:
@labor.state. Subject: RE: methodology to keep only
certain programs to connect to
ny.us>
Sent by: root
09/10/2002
12:23 PM
Please
respond to
ORACLE-L
Joe,
I use the following with decent success on a logon database trigger:
-- Set a unique string for the session and update the session info.
client_info_str := 'WTWLOGIN_' || LTRIM(dbms_random.value,'.');
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(client_info_str);
-- look into the v$session view for the session just connected.
SELECT program, username,
osuser, terminal, machine
INTO loc_program, loc_username,
loc_osuser,loc_terminal,loc_machine
FROM V$SESSION
WHERE client_info=client_info_str;
>From here, you can test the loc_program variable against the loc_username
to see if the combination is correct.
Stuff like:
IF loc_username='TESTLOGIN' then
RAISE kill_Login;
END IF;
EXCEPTION
WHEN kill_Login THEN
RAISE_APPLICATION_ERROR(-20999,'Login''s using this account and this
tool are Invalid');
Hope this helps!
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
Sent: Tuesday, September 10, 2002 11:58 AM
To: Multiple recipients of list ORACLE-L
I've been tasked to ensure only certain app programs access the database.
I'm thinking on-logon trigger, check the program field from v$session.
unfortunately v$session is for all sessions, i can't seem to find the view
that tells me only MY info during login. I only want the sid, serial#,
username and program for my just now connection to the database.
Does this exist or am I going about this the wrong way?
We're thinking of checking those fields to make sure sql*plus, toad, etc
can't connect as a particular user(even though the password is known out in
the community).
any ideas would be greatly appreciated.
joe
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Thomas Day
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
