My experience is that an invalid trigger doesn't fire --- no effect.
Also, userids with the DBA role don't fire the trigger. So you can't lock
yourself out of the database. Just go in with a DBA role userid and drop
the logon trigger.
"Markham,
Richard" To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
<RMarkham cc:
@hafeleameric Subject: RE: methodology to keep only
certain programs to connect to
as.com>
Sent by: root
09/10/2002
01:18 PM
Please
respond to
ORACLE-L
what are the drawbacks with such a trigger, what if the code went invalid
and would not compile is
it possible that you could lock yourself out, or would the base login
functionality still work regardless
or the status of this trigger?
-----Original Message-----
Sent: Tuesday, September 10, 2002 12:24 PM
To: Multiple recipients of list ORACLE-L
Joe,
I use the following with decent success on a logon database trigger:
--� Set a unique string for the session and update the session info.
client_info_str := 'WTWLOGIN_' || LTRIM(dbms_random.value,'.');
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(client_info_str);
-- look into the v$session view for the session just connected.
SELECT program, username,
osuser, terminal, machine
INTO loc_program, loc_username,
loc_osuser,loc_terminal,loc_machine
FROM V$SESSION
WHERE client_info=client_info_str;
>From here, you can test the loc_program variable against the loc_username
to see if the combination is correct.
Stuff like:
IF loc_username='TESTLOGIN'�then
�� RAISE kill_Login;
END IF;
EXCEPTION
WHEN kill_Login THEN
��� RAISE_APPLICATION_ERROR(-20999,'Login''s using this account and this
tool are Invalid');
Hope this helps!
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
Sent: Tuesday, September 10, 2002 11:58 AM
To: Multiple recipients of list ORACLE-L
I've been tasked to ensure only certain app programs access the database.
I'm thinking on-logon trigger, check the program field from v$session.
unfortunately v$session is for all sessions, i can't seem to find the view
that tells me only MY info during login.� I only want the sid, serial#,
username and program for my just now connection to the database.
Does this exist or am I going about this the wrong way?
We're thinking of checking those fields to make sure sql*plus, toad, etc
can't connect as a particular user(even though the password is known out
in the community).
any ideas would be greatly appreciated.
joe
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Thomas Day
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).
