| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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.
-----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.
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
-
--
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). Received on Fri Oct 11 2002 - 12:03:44 CDT
 |
 |