From oracle-l-bounce@freelists.org Wed Feb 2 07:39:22 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j12DdM7I016127 for ; Wed, 2 Feb 2005 07:39:22 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j12DdMem016123 for ; Wed, 2 Feb 2005 07:39:22 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 026086C425; Wed, 2 Feb 2005 07:38:27 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 27432-07; Wed, 2 Feb 2005 07:38:26 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 74FF26C2E3; Wed, 2 Feb 2005 07:38:26 -0500 (EST) Content-class: urn:content-classes:message MIME-Version: 1.0 Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Subject: RE: logon trigger with machine condition Date: Wed, 2 Feb 2005 14:36:13 +0200 Message-ID: <1831A554E8800049B6B970790D2513C001C36161@fnbkrkmx01.fnb.co.za> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: logon trigger with machine condition Thread-Index: AcUIlUIWDRGOjVVMRnaAQk6+kG1i1wAjlTug From: "Leonard, George" To: , X-OriginalArrivalTime: 02 Feb 2005 12:36:12.0807 (UTC) FILETIME=[C7A76970:01C50923] X-archive-position: 15656 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: GLeonard@wesbank.co.za Precedence: normal Reply-To: GLeonard@wesbank.co.za X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Checker-Version: SpamAssassin 2.60 (1.212-2003-09-23-exp) on air891.startdedicated.com X-Spam-Status: No, hits=0.2 required=5.0 tests=AWL autolearn=ham version=2.60 X-Spam-Level: Hi there Have a look at the following=20 =09 , sys_context('USERENV', 'IP_ADDRESS') =09 , Sys_context('USERENV', 'HOST') =09 , Sys_context('USERENV', 'OS_USER') =09 , Sys_context('USERENV', 'TERMINAL') that will give you the host name. I would suggest not hard coding the host name you want to monitor. Rather add it as a record in a table. That way you can add and removed records at will. George =20________________________________________________ George Leonard Oracle Database Administrator New Dawn Technologies @ Wesbank E-mail:gleonard@wesbank.co.za =20 You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit! =20 -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of jsb@digistar.com Sent: 01 February 2005 21:35 PM To: oracle-l@freelists.org Subject: logon trigger with machine condition I'm looking for a way to trace a session on logon with the condition that it be from a particular host. Wolfgang brought up a good point about logon to a particular schema but in this case I need a database-wide trigger. I'm looking to do something like this below, but it does not work: CREATE OR REPLACE TRIGGER trace_a_session =20 AFTER LOGON ON DATABASE BEGIN =20 IF USER LIKE 'USER' =20 THEN =20 IF HOST LIKE 'SOMEHOST.DOMAIN.COM' =20 THEN =20 EXECUTE IMMEDIATE 'alter session set events ''10046 trace name= context forever, level 12'''; =20 END IF; =20 END IF; END; / There apparently is no PL/SQL HOST function which is what I need, or a way to do it via a nested select and variables. I intend to purchase a book or two after work tonight but I need some help now if anyone has dealt with this situation before. regards, Jason -- http://www.freelists.org/webpage/oracle-l _________________________________________________________________________= __________________________ The views expressed in this email are, unless otherwise stated, those of = the author and not those of the FirstRand Banking Group an Authorised Financial Service Provider o= r its management. The information in this e-mail is confidential and is intended solely for= =20the addressee. Access to this e-mail by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, distribut= ion or any action taken or=20 omitted in reliance on this, is prohibited and may be unlawful. Whilst all reasonable steps are taken to ensure the accuracy and integrit= y of information and data=20 transmitted electronically and to preserve the confidentiality thereof, n= o liability or=20 responsibility whatsoever is accepted if information or data is, for what= ever reason, corrupted=20 or does not reach its intended destination. =20 ________________________________ -- http://www.freelists.org/webpage/oracle-l