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@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?
>>> [EMAIL PROTECTED] 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: [EMAIL PROTECTED]
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: [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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Gene Sais
INET: [EMAIL PROTECTED]
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: [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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Deshpande, Kirti
INET: [EMAIL PROTECTED]
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: [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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ramon E. Estevez
INET: [EMAIL PROTECTED]
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: [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).
