This content has been marked as final. Show 16 replies
I think you've got to cater for NO_DATA_FOUND in your trigger, otherwise SYS logons could well end up with an error when they log in!1 person found this helpful
Are both logins into schema A and schema B logging on from the same program?
You could perhaps put some code in your trigger to write debug information to a table so that you can easily see what's going on and what conditions are being met or not met on login whilst you're testing it.
1 person found this helpful
infotools wrote:That doesn't tell us anything. It's like saying "my car doesn't work, what's wrong" without giving us the symptoms.
The problem is that RAISE_APPLICATION_ERROR(-20000, 'Development tools are not allowed here.');
work on one scheme A, but not on scheme B. Thank you in advance.
Does it generate an error message?
have you tested that the conditions are met to raise the exception?
Thank you i put the Exception when OTHERS for the SELECT.
Yes, i open both schemas from TOAD.
I put some debug code... both enter into the RAISE_APPLICATION_ERROR. However, the schemaA continue to logon and success, but schema B fails and get the error message.
Thank you, both schemas executes the RAISE_APPLICATION_ERROR code. But, schema A success to logon, on the other hand schema B fails and got the error message. I have no idea what things prevent the schema A to generate the error.
When i have already entered to the schemaA and put the trigger code in the TOAD editor, the error message raise.
So, there is 'something' when the trigger after logon fires that prevents the RAISE_APPLICATION_ERROR, but i have no idea what to see in my database.
[Edit: after testing, this suggestion doesn't seem to work for me. However, I am leaving the code in because the OP marked it "useful". Maybe the OP just took bits of it, or otherwise changed it so it would work.]1 person found this helpful
Please note that USERENV() all alone is deprecated.
select program from v$session where sid = to_number(SYS_CONTEXT ('USERENV', 'SID')) and ( UPPER(program) LIKE '%TOAD%' OR UPPER(program) LIKE '%T.O.A.D%' OR -- Toad UPPER(program) LIKE '%SQLNAV%' OR -- SQL Navigator UPPER(program) LIKE '%PLSQLDEV%' OR -- PLSQL Developer UPPER(program) LIKE '%BUSOBJ%' OR -- Business Objects UPPER(program) LIKE '%EXCEL%' -- MS-Excel plug-in; ); If sql%rowcount > 0 then...
Edited by: Stew Ashton on Dec 13, 2012 2:17 PM
thank you, i try to remove all code, but the raise:
for schema A it success to logon without error messsage, but scheme B it fails and got the message ORA-00604: error occurred at recursive SQL level 1, ORA 2000' Development tools are not allowed here.
DROP TRIGGER SYS.REDLINE_IB; CREATE OR REPLACE TRIGGER SYS.REDLINE_IB AFTER LOGON ON DATABASE DECLARE v_prog SYS.v_$session.program%TYPE; v_TERMINAL SYS.v_$session.TERMINAL%TYPE; V_CNT NUMBER := 0; V_OK NUMBER := 0; BEGIN RAISE_APPLICATION_ERROR ( -20000, 'Development tools are not allowed here.' ); END; /
for USER sys, it also success.
Keep in mind that this kind of approach to preventing 'development tools' from connecting is easily defeated.1 person found this helpful
It may also indicate incorrect use of privileges.
What is your reasoning to prevent users from connecting with these tools?
Thank you. My DBA said that only TOAD is allowed to connect the DB, other tools can explore the database object that is not privileged. The problem is users from other company as our business is B to B , can see our database with other tools - as you said easily defeated.
Hi, you said " incorrect use of privileges.".. Can one schema / users acts as if it is a SYS? tx
What a user has access to when connected is governed entirely by the privileges you have granted to that user.
The choice of tool makes no difference.
A tool can not somehow give you more privileges.
However, if you have granted more privileges than is absolutely necessary for a user to do his job then that's a privileges problem not a tool problem.
>1 person found this helpful
My DBA said that only TOAD is allowed to connect the DB, other tools can explore the database object that is not privileged
Well the code you posted doesn't allow TOAD to connect either since it is the first tool you check.
What other logon triggers exist? The exception is being swallowed by other code that may even have the dreaded WHEN OTHERS handler.