trigger to restrict users

[Knarayan]

Is there a System Before Logon trigger where one can prevent certain users at certain IP addresses from logging in to the database.

[julian]

I use this trigger in one of my databases:

CREATE OR REPLACE TRIGGER logon_from_linux
AFTER
ON
REFERENCING NEW AS NEW OLD AS OLD
declare
W_NAME varchar2(20);
W_IP   varchar2(20);
begin
 select Sys_Context('USERENV','SESSION_USER') into W_NAME from dual;
 select Sys_Context('USERENV','IP_ADDRESS') into W_IP from dual;
     if W_NAME != 'LARRY' and W_IP = '567.79.117.6' then
        insert into LINUX_LOGIN values(W_NAME,W_IP,sysdate);
        RAISE_APPLICATION_ERROR(-20111, 'Sorry, you are not allowed here!');
     end if;
end;
/

[Sureshy]

You could also look into using cman rules as part of oracle connection manager. This can block connections from certain IP addresses or to certain servers.

[julian]

Originally posted by Sureshy
You could also look into using cman rules as part of oracle connection manager. This can block connections from certain IP addresses or to certain servers.

Well, yes, but what is easier: to configure the Connection Manager to or to write 10 lines of PL/SQL code :-)