RE: Restrict login for a particular user to be only from particul ar m achines

From: Baumgartel, Paul <paul.baumgartel_at_credit-suisse.com>
Date: Thu, 14 Sep 2006 14:42:03 +0100
Message-ID: <D97D1FAE0521BD44820B920EDAB3BBAC0F40E871@ENYC11P32005.corpny.csfb.com>

Stefan,

Thanks. Here's the result in my environment--very puzzling.

SQL> set instance hounddog
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options

SQL> @conn "sys as sysdba"
Enter password:
Connected.
SYS_at_CSAR.REGRESS.RDBMS.DEV.US.ORACLE.COM> 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_CSAR.REGRESS.RDBMS.DEV.US.ORACLE.COM> SYS_at_CSAR.REGRESS.RDBMS.DEV.US.ORACLE.COM> create or replace trigger verify_client   2 after logon on database
  3 begin
  4 if (user='PB') then

  5     do_insert;
  6     raise_application_error(-20001,'Foo');

  7 end if;
  8 end;
  9 /

Trigger created.

SYS_at_CSAR.REGRESS.RDBMS.DEV.US.ORACLE.COM> @conn pb/pb Connected.

Ideas? Anyone? Or is it time to open an SR?

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

-----Original Message-----
From: Stefan Knecht [mailto:knecht.stefan_at_gmail.com] Sent: Thursday, September 14, 2006 4:11 AM To: paul.baumgartel_at_credit-suisse.com
Cc: oracle-l
Subject: Re: Restrict login for a particular user to be only from particular m achines

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 <mailto: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,

Please access the attached hyperlink for an important electronic communications disclaimer:

<http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html>

http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html

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 - 08:42:03 CDT