Home / server / Questions / 58856
Accepted
Andrew
Asked: 2009-08-27 01:03:09 +0800 CST

Disconnecting an Oracle Session from a Logon Trigger

  • 772

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):

CREATE OR REPLACE TRIGGER global_logon_trg AFTER logon ON DATABASE
DECLARE
   p_session_user  varchar2(64);
   p_module        varchar2(64);
BEGIN
   SELECT UPPER(SYS_CONTEXT('USERENV', 'SESSION_USER')) INTO p_session_user FROM DUAL;
   SELECT UPPER(SYS_CONTEXT('USERENV', 'MODULE')) INTO p_module FROM DUAL;

   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;

END;
/

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..

USERNAME           CLIENT_IDENTIFIER
------------------ -----------------------------------
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 11.1.0.6.0 - Production

Many thanks for your help, and suggestions

Andrew :o)

oracle

2 Answers

  1. 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 ?

    • 1
  2. Best Answer

    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!

    • 1