RE: methodology to keep only certain programs to connect to

From: Thomas Day <tday6_at_csc.com>
Date: Tue, 10 Sep 2002 12:19:20 -0400
Message-Id: <22528.293339@fatcity.com>

Yes. This works great. You posted your logon trigger before and I've = used
it with considerable success (and modification). We (will) use the log= on
trigger to ensure that a particular Oracle userid is logged on only fro= m
one machine (no "sharing" of userids). We also allow certain exemption= s,
either by userid or machine. I'll post our trigger but it's based on M= r.
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 :=3D null;   kill_Login EXCEPTION;
  PRAGMA EXCEPTION_INIT( kill_Login, -20997 ); begin
-- Set information string to uniquely identify this session

     client_info_str :=3D '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=3Db.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 =3D '= U')
-- look for two different machines

          and a.machine !=3D b.machine
-- are any of the machines exempt?
-- trim off the null character that occasionally gets added to the mach= ine
name

          AND rtrim(A.MACHINE,CHR(0)) NOT IN (SELECT LME_exemptee FROM
               LOGON_MULTIPLE_EXEMPTIONS WHERE LME_exemption_type =3D '=
M')
          AND rtrim(B.MACHINE,CHR(0)) NOT IN (SELECT LME_exemptee FROM
               LOGON_MULTIPLE_EXEMPTIONS WHERE LME_exemption_type =3D '=

M')
-- make sure that we are looking at this logon session

          and a.client_info=3Dclient_info_str;
          EXCEPTION WHEN OTHERS THEN
          NULL;
     end;

-- if the user has a logon from more than 1 non-exempt machine then ki= ll
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 vi=

a
another machine!');

     WHEN OTHERS THEN
          null;

END;
/

Hope this helps and thanks Tom.

                                                                       =


                                           =20

                    "Mercadante,                                       =


                                           =20

                    Thomas F"            To:     Multiple recipients of=
 list ORACLE-L <ORACLE-L_at_fatcity.com>      =20
                    <NDATFM              cc:                           =


                                           =20

                    @labor.state.        Subject:     RE: methodology t=
o keep only certain programs to connect to =20
                    ny.us>                                             =


                                           =20

                    Sent by: root                                      =


                                           =20

                                                                       =


                                           =20

                                                                       =


                                           =20

                    09/10/2002                                         =


                                           =20

                    12:23 PM                                           =


                                           =20

                    Please                                             =


                                           =20

                    respond to                                         =


                                           =20

                    ORACLE-L                                           =


                                           =20

                                                                       =


                                           =20

                                                                       =


                                           =20

Joe,

I use the following with decent success on a logon database trigger:

--=A0 Set a unique string for the session and update the session info.=

client_info_str :=3D '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 Received on Tue Sep 10 2002 - 11:19:20 CDT