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

From: Stefan Knecht <knecht.stefan_at_gmail.com>
Date: Thu, 14 Sep 2006 10:10:30 +0200
Message-ID: <486b2b610609140110q4793b6f9m9e31ecfd1ddef920@mail.gmail.com>

Hi Paul,

This works:

sys_at_ORA9I> create table t (x int);

Table created.

sys_at_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_at_ORA9I>
sys_at_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_at_ORA9I> @conn foo/bar
ERROR:

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>

Stefan

On 9/13/06, Baumgartel, Paul <paul.baumgartel_at_credit-suisse.com> wrote:
>
> All,
>
> 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
> grateful.
>
> Thanks,
>
> *Paul Baumgartel*
> *CREDIT SUISSE*
> Information Technology
> DBA & Admin - NY, KIGA 1
> 11 Madison Avenue
> New York, NY 10010
> USA
> Phone 212.538.1143
> paul.baumgartel_at_credit-suisse.com
> www.credit-suisse.com
>
>
>
> ==============================================================================
> Please access the attached hyperlink for an important electronic communications disclaimer:
>
> http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
> ==============================================================================
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Sep 14 2006 - 03:10:30 CDT