RE: Sending feed back to sqlplus user
- From: "Mercadante, Thomas F \(LABOR\)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>
- To: <BSpears@xxxxxxxxxxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 25 Aug 2006 10:20:14 -0400
Brian,
Let me ask a stupid question.
If they are not allowed to use these tools to make a connection (I am
concluding this because of the error message you are issuing), then why
not simply give them the message and then disconnect them? The
"Raise_Application_Error" provides you with a way to display a string
(just like you are doing).
I do exactly this for certain Oracle accounts from certain IP addresses.
Works fine.
Tom
--------------------------------------------------------
This transmission may contain confidential, proprietary, or privileged
information which is intended solely for use by the individual or entity to
whom it is addressed. If you are not the intended recipient, you are hereby
notified that any disclosure, dissemination, copying or distribution of this
transmission or its attachments is strictly prohibited. In addition,
unauthorized access to this transmission may violate federal or State law,
including the Electronic Communications Privacy Act of 1985. If you have
received this transmission in error, please notify the sender immediately by
return e-mail and delete the transmission and its attachments.
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Spears, Brian
Sent: Friday, August 25, 2006 9:00 AM
To: oracle-l
Subject: Sending feed back to sqlplus user
Hi,
I have been trying to send message to logged on user using the database
logon trigger. I can send them a message when signing them
off by raising exception in the trigger but I need to send in any ugly
format a message back to the sqlplus user from inside this trigger.
(Not all code include for sizing)
CREATE OR REPLACE TRIGGER block_tools_from_prod
AFTER LOGON ON DATABASE
BEGIN
DBMS_OUTPUT.ENABLE(100000);
SELECT program, machine, username INTO v_prog, v_mach, v_user
FROM sys.v_$session
WHERE audsid = USERENV('SESSIONID')
AND audsid != 0 -- Don't Check SYS Connections
AND rownum = 1 -- Parallel processes will have the same AUDSID's
AND upper(username) in ('WORKBRAIN') ;
IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%'OR --
Toad
UPPER(v_prog) LIKE '%SQLPLUS%' -- SQLPLUS
AND upper(v_mach) LIKE 'LIMITED\%'
THEN
DBMS_OUTPUT.PUT_LINE('yup output from trigger');
RAISE e_badlogin;
END IF;
EXCEPTION
WHEN e_badlogin THEN
-- DBMS_OUTPUT.PUT_LINE('yup output from trigger');
RAISE_APPLICATION_ERROR(-20000, 'This access is in violation of LTS
compliance, audit, and IT Security Policies.!'||' User: '||v
_user|| ' Program: '||v_prog);
END;
============
Problem is, I can't from the trigger send back any message without
killing the session. Dbms_output doesnot seem to work from inside this
trigger (Should it?) .
Can someone point me in the right direction here? There must be some way
to send message to SQLplus user a message without killing the session.
Brian
Other related posts:
