Oracle Trigger: raise_application_error

Question

hi, I want to use the raise_application_error-procedure to stop the login process. I wrote a trigger, that checks the TERMINAL String, if it is right (I know that isn't realy secure, but at first, it is enough) So the Trigger works fine and does what i want, but the raise_application_error causes an rollback and sends not the exception that I want. Whenn I log into the DB with my Application, the raise_application_error doesnt stop the app. First question: Is this the right way, to stop logon the db with the wrong application? Second question: If yes, what is wrong?

create or replace
TRIGGER after_logon_on_database 
AFTER LOGON ON DATABASE
BEGIN
IF sys_context('USERENV', 'TERMINAL')='IAS' THEN
  INSERT INTO event_log
  (event_date, event_time, username, event_case, event_comment)
  VALUES
  (SYSDATE, to_char(sysdate, 'hh24:mi:ss'), USER, 'LOGON-SUCCESS', sys_context('USERENV', 'TERMINAL'));
ELSE
  INSERT INTO event_log
  (event_date, event_time, username, event_case, event_comment)
  VALUES
  (SYSDATE, to_char(sysdate, 'hh24:mi:ss'), USER, 'LOGON-FAILURE', sys_context('USERENV', 'TERMINAL'));
  RAISE_APPLICATION_ERROR(-20001, 'Access denied!');
END IF;
END after_logon_on_database;

Answer 1

In the second part of the IF/ELSE add a commit; statement between the Insert and the Raise. This will ensure that the Login failure message is inserted into the database correctly.

You are aware the the on-logon trigger won't stop the user from logging in if they are a DBA (have the DAB role). This is a feature to ensure that someone can always get access to the database to fix a broken on-logon trigger.

You are also correct in that the trigger won't raise (as the first error message returned by Oracle) the error -20001. It will instead return a -604 (ORA-00604: error occurred at recursive SQL level 1). You are not directly executing the trigger at login, it's executed at a few steps removed. You will want your application to handle this error properly.

Answer 2

Read this ask tom-thread: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3236035522926