I'm trying to construct an Oracle 11g logon trigger that restricts access for a specific user to a known list of applications. For example, user SCOTT can only connect with MYAPP.EXE.

I believe I have the correct trigger and logic, but the problem I'm having is that the RAISE_APPLICATION_ERROR(); doesn't kill the session. The trigger is enabled, and I've tried granting the ADMINISTER DATABASE TRIGGER privilege, but I'm still missing something.

Based on the this example, here's the trigger I have so far (owned by the same user):

   p_session_user  varchar2(64);
   p_module        varchar2(64);

   DBMS_SESSION.SET_IDENTIFIER(p_session_user || '-' || p_module);

   IF ((p_session_user = 'SCOTT') AND (p_module IN ('MYAPP.EXE'))) THEN
      DBMS_SESSION.SET_IDENTIFIER('about to raise app_error..');
      RAISE_APPLICATION_ERROR(-20003,'You are not allowed to connect to the database');
   END IF;


But logging in as SCOTT with SQL*Plus still works. And if I then query v$session, with the following query:

select username, client_identifier
from v$session
where username not in ('SYSMAN', 'DBSNMP');

I get the following result..

------------------ -----------------------------------
SCOTT              about to raise app_error..

What am I missing? Is this a privileges thing, a trigger code thing, or an Oracle bug thing? Or is there a better way to do what I'm trying to do?! (I have full control over the Database and the Server it sits on, if that makes it any easier!)

I'm using Oracle Database 11g Release - Production

Many thanks for your help, and suggestions

Andrew :o)

  • Some great answers from @Vincent and @Guy, and after reading through the links they've provided, I don't think I'll be implementing this logon disconnect trigger, even though I've now got it working! :o) – Andrew Aug 26 '09 at 12:44

Check out TOM KYTE's blog / Q&A site asktom. There may be a subtle reason why this is not working as you would expect.

Your problem discussed here and here for two examples.

An alternative is to use APPLICATION ROLES that are only enabled by the application and a password configured in the application.

As a DBA I would not like to restrict access to the database via SQLPLUS under any circumstances as this is often your best route to gain access to a sickly database. And to have it in a global trigger. No way matey!

  • The second of your example links fixed the issue - in trying to get it to work I had granted 'Administer Database Trigger' - revoking this privilege prevents the user from logging in as desired :o) – Andrew Aug 26 '09 at 12:37

Has the Scott schema been granted the DBA role? From this discussion on Asktom, it seems a member of the DBA group can't be disconnected with an ON LOGON trigger.

By the way you will also see in that thread why your solution is not bullet-proof (eg: copy sqlplus.exe foo.exe)

Update: additionaly this trigger won't work in the following cases:

  • the owner of the trigger is SCOTT (i-e: he won't be disconnected if he actually owns the trigger, this must be to prevent accidental self-locking),
  • the user has the privilege Administer Database Trigger

Could you please check if the trigger is owned by SCOTT ?

  • Thanks. SCOTT isn't a DBA, and I tried TOAD instead of SQL*Plus - in case the latter is a special case - and the login is still allowed. Thanks for the xcopy workaround - I had thought of that, though I'd still like to get it working so as to understand why the RAISE_APPLICATION_ERROR doesn't work in this case. – Andrew Aug 26 '09 at 12:05
  • @Andrew: I updated my answer: apparently the trigger won't disconnect the OWNER of the trigger. – Vincent Aug 26 '09 at 12:30
  • Thanks again - I didn't know the OWNER made a difference as well - in my case the Trigger was owned by a different DBA user :o) – Andrew Aug 26 '09 at 13:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.