RE: Sending feed back to sqlplus user

From: Mercadante, Thomas F \(LABOR\) <Thomas.Mercadante_at_labor.state.ny.us>
Date: Fri, 25 Aug 2006 10:20:14 -0400
Message-ID: <ABB9D76E187C5146AB5683F5A07336FF681566@EXCNYSM0A1AJ.nysemail.nyenet>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] 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

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Aug 25 2006 - 09:20:14 CDT