Disconnecting an Oracle Session from a Logon Trigger

oracleoracle-11g

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)

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!

Related Topic