Oracle-L: Re: Controlling Users Logons

From: Thomas Day <tday6_at_csc.com>
Date: Fri, 11 Oct 2002 07:23:53 -0800
Message-ID: <F001.004E6BEC.20021011072353@fatcity.com>

Try this:

--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; /

We are allowing multiple logons from the same machine and some userids are allowed to logon from multiple machines but the basic force of this trigger is to allow a userid to be logged on from only one machine. i.e., users are not allowed to "share" userids. You'll want to change the logic but the basic mechanism is there. We handle exemptions through a table on the database.

HTH

                                                                                                                   
                    "Deshpande,                                                                                    
                    Kirti"               To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>       
                    <kirti.deshpa        cc:                                                                       
                    nde                  Subject:     Controlling Users Logons                                     
                    @verizon.com>                                                                                  
                    Sent by: root                                                                                  
                                                                                                                   
                                                                                                                   
                    10/11/2002                                                                                     
                    09:48 AM                                                                                       
                    Please                                                                                         
                    respond to                                                                                     
                    ORACLE-L

Hello Listers,
I was asked by a co-worker if there was a way in Oracle to prevent users from connecting to the databases if the same OSUSER has already a created a specified number of sessions to a particular instance.

We discussed profiles and resource limits etc. However, the requirement is that the user should a get message that they have exceeded their quota and should not be allowed to log in (there goes the log on trigger).

The denial of connection *must* be based on 'OSUSER'. In this environment different OSUSERs use the same Oracle Username for these connections, and the expectation is that the DBA find a solution to enforce some rules.

Any tricks? Third party software?

Thanks.

Kirti

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Deshpande, Kirti
INET: kirti.deshpande_at_verizon.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (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).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Thomas Day
INET: tday6_at_csc.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message