Tuesday, February 28, 2012

Database Login Trigger to prevent access from specific user HOST & DB User


There are couple of ways to restrict database access from some particular user HOST or DATABASE USER or even combination of HOST & DB USER.

We can create a database login trigger that would check for each DB connection at login time and prevent login for the specified hosts & DB users.

Here in my example, I'm trying to protect HR,SCOTT and SALES schemas connection from PC_USER1 & PC_USER2 PC.
CREATE OR REPLACE TRIGGER SYSTEM.LOGON_DENY
AFTER LOGON
ON DATABASE
declare
OSUSER varchar2 (200);
HOSTNAME varchar2 (200);
       begin
       select sys_context ('USERENV', 'OS_USER') into OSUSER from dual;
        select sys_context ('USERENV', 'HOST') into HOSTNAME from dual;
        if sys_context('USERENV','SESSION_USER')in ('HR','SCOTT','SALES')
        and sys_context ('USERENV', 'HOST') in ('PC_USER1','PC_USER2')
        then
  raise_application_error(-20001,'Denied!  You are not allowed to logon from host '||HOSTNAME|| ' using '|| OSUSER);
         end if;
 end;
/
Trigger Created

Now lets try to login as SCOTT from the PC named PC_USER1 and see if that works?
Y:\>sqlplus scott/tiger@LABDB
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Feb 28 16:03:06 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Denied!  You are not allowed to logon from host PC_USER1 using
SCOTT
ORA-06512: at line 10