Re: Restrict login for a particular user to be only from particular m achines

  • From: "Stefan Knecht" <knecht.stefan@xxxxxxxxx>
  • To: paul.baumgartel@xxxxxxxxxxxxxxxxx
  • Date: Thu, 14 Sep 2006 10:10:30 +0200

Hi Paul,

This works:

sys@ORA9I> create table t (x int);

Table created.

sys@ORA9I> create or replace procedure do_insert
 2  as
 3  pragma autonomous_transaction;
 4  begin
 5  insert into t values (1);
 6  commit;
 7  end;
 8  /

Procedure created.

sys@ORA9I> create or replace trigger verify_user
 2  after logon on database
 3  begin
 4  if (user='FOO') then
 5     do_insert;
 6     raise_application_error(-20001,'Foo');
 7  end if;
 8  end;
 9  /

Trigger created.

sys@ORA9I> @conn foo/bar
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Foo
ORA-06512: at line 4

Warning: You are no longer connected to ORACLE. idle>


On 9/13/06, Baumgartel, Paul <paul.baumgartel@xxxxxxxxxxxxxxxxx> wrote:


A client wants to be able to limit login for particular database user to
be from a specified set of client machines.  My understanding of login
restriction at the listener level is that it is possible only to limit all
database logins based on machine.  I've been experimenting with login
triggers that inspect sys_context('userenv','host'), but haven't figured out
how to prevent the session from proceeding if the client machine name is not
in the approved list.  If anyone has any ideas or suggestions I'd be most


*Paul Baumgartel*
Information Technology
DBA & Admin - NY, KIGA 1
11 Madison Avenue
New York, NY 10010
Phone 212.538.1143

Please access the attached hyperlink for an important electronic communications 

Other related posts: