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