Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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)

share|improve this question
    
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

2 Answers 2

up vote 1 down vote accepted

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!

share|improve this answer
    
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 ?

share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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