RE: Controlling Users Logons
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).
Re: Controlling Users Logons
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_usernameV$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 [EMAIL PROTECTED] kirti.deshpacc: 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?
RE: Controlling Users Logons
Title: RE: Controlling Users Logons
Kirti
... you can still use the logon trigger ...
if total_user_logons max_allowed then
raise_application_error
('-20001','You seem to have exceeded your quota,' ||
'Please come to the DBA group with a Banker''s Check.' ||
We no longer accept credit cards or personal checks.');
end if;
Once the error is raised, it will log them out in the on logon trigger.
Raj
__
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!
-Original Message-
From: Deshpande, Kirti [mailto:[EMAIL PROTECTED]]
Sent: Friday, October 11, 2002 11:14 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Controlling Users Logons
They can come in from various 'machines'.
- Kirti
*This e-mail
message is confidential, intended only for the named recipient(s) above and may
contain information that is privileged, attorney work product or exempt from
disclosure under applicable law. If you have received this message in error, or are
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000
and delete this e-mail message from your computer, Thank
you.*1
RE: Controlling Users Logons
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
andmachine = 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
RE: Controlling Users Logons
Hi Thomas, Thanks a lot for the code. Looks like we can do something very similar. - Kirti -Original Message- Sent: Friday, October 11, 2002 10:24 AM To: Multiple recipients of list ORACLE-L 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_usernameV$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 [EMAIL PROTECTED] kirti.deshpacc: 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: [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: Thomas Day 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
RE: Controlling Users Logons
Ramon,
Thanks a lot.
My problem in using logon trigger turned out to be the 8.1.7.2 version of
the database. Raising appl error is just dumping a trace file without
killing the session. It does work fine with 8.1.7.4 databases.
- Kirti
-Original Message-
Sent: Friday, October 11, 2002 11:00 AM
To: Multiple recipients of list ORACLE-L
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
andmachine = 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
-
--
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).
Re: Controlling Users Logons
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).
