Oracle-L: RE: Controlling Users Logons

From: Ramon E. Estevez <com.banilejas_at_codetel.net.do>
Date: Fri, 11 Oct 2002 07:59:31 -0800
Message-ID: <F001.004E6D8A.20021011075931@fatcity.com>

Kirti,

I used this to control users not to connect using SQLPLUS, TOAD, NAVIGATOR, etc. Try it to check if it works for you.

Start of DDL Script for Trigger SYS.LOGON_AUDIT_T
Generated 20-May-2002 05:31:48 p.m. from U20188_at_PROD

CREATE OR REPLACE TRIGGER sys.logon_audit_t after logon on database

declare
user_name varchar2(30);
program_name varchar2(40);
machine_name varchar2(40);
user_number number;
logon_date date;
contador integer;
contador1 integer;
external_tool boolean := false;
cursor c1 is select username, program , machine, sysdate from v$session
where audsid=userenv('sessionid');
begin
open c1;
fetch c1 into user_name, program_name, machine_name, logon_date; close c1;
insert into logon_audit values(user_name, program_name,machine_name, logon_date);
commit;
select count(*) into contador1
from v$session
where username = user_name
and machine = machine_name;
select user# into user_number
from sys.user$
where name = user_name;
select count(*) into contador
from user$
where type# = 0
and user# in (select privilege# from sysauth$ where grantee# = user_number
and privilege# in (select user# from user$ where type#=0 and name in
('DBA_JUNIOR','DBA_SENIOR')));
if (
(upper(program_name) LIKE ('%PLUS%') or upper(program_name) LIKE ('%TOAD%') OR
upper(program_name) LIKE ('SQLNAV%'))

)
then
external_tool := true;
end if ;
if (external_tool) and (contador=0)
then
raise_application_error(-20001,'No puede conectarse utilizando esta aplicacion'); end if; if (contador=0) and (contador1=0) then raise_application_error(-20001,'No puede conectarse desde esta terminal'); end if;
exception when others
then
raise_application_error(-20001,'No puede ningun privilegio asignado, contacte del depto de seguridad de sistemas'); end;

Luck,

Ramon

-----Original Message-----
Kirti
Sent: Friday, October 11, 2002 10:14 AM
To: Multiple recipients of list ORACLE-L

They can come in from various 'machines'.

Kirti

-----Original Message-----
Sent: Friday, October 11, 2002 9:39 AM
To: Multiple recipients of list ORACLE-L

Could you use machine from v$session?

>>> kirti.deshpande_at_verizon.com 10/11/02 09:48AM >>> 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: Gene Sais
INET: Gsais_at_co.palm-beach.fl.us

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

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

--

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

Author: Ramon E. Estevez
INET: com.banilejas_at_codetel.net.do

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