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
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!)
Oracle Database 11g Release 126.96.36.199.0 - Production
Many thanks for your help, and suggestions