Here what we've done to alter the optimizer goal at logon time.
CREATE OR REPLACE TRIGGER change_optimizer_on_startup
AFTER LOGON ON DATABASE
DECLARE
p_osuser_txt V$SESSION_CONNECT_INFO.OSUSER%TYPE;
BEGIN
BEGIN
SELECT OSUSER
INTO p_osuser_txt
FROM V$SESSION
WHERE AUDSID = (SELECT USERENV('SESSIONID') FROM DUAL);
EXCEPTION
WHEN OTHERS THEN
p_osuser_txt:=null;
END;
IF p_osuser_txt IN ('psoftfs') THEN
BEGIN
EXECUTE IMMEDIATE 'alter session set optimizer_mode=rule';
EXCEPTION
WHEN OTHERS THEN
SYS.DBMS_SYSTEM.KSDWRT(2,TO_CHAR(SYSDATE,'dd-mon-rr hh:mm:ss')||SQLERRM);
END;
END IF;
END;
-----Original Message-----
From: Buecherl Dieter (BUE) [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 31, 2001 12:27 PM
To: Multiple recipients of list ORACLE-L
Subject: LOGON TRIGGER
Hi, all
we are using Oracle 8.1.7 and WebSphere on Solaris.
We use a connection pool that allows 20 sessions
from the app server to the database.
In order to prevent anyone to connect to the datebase other
than the app server, we would like to implement a security
feature based on a LOGON TRIGGER that checks (client IP,
application, etc.) and counts the active sessions.
After 20 sessions have logged in, we want to issue an
'ALTER SYSTEM ENABLE RESTRICTED SESSION'
to lock out any additional connection requests .
Any comments on this?
TIA, Dieter
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Buecherl Dieter (BUE)
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).