trigger to restrict users
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: trigger to restrict users

Hybrid View

  1. #1
    Join Date
    Feb 2000
    Location
    NJ, United States
    Posts
    250
    Is there a System Before Logon trigger where one can prevent certain users at certain IP addresses from logging in to the database.
    KN

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    I use this trigger in one of my databases:

    Code:
    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;
    /

  3. #3
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    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.
    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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 :-)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width