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

Hi Paul,

Connected as PB, what does "select * from session_privs" show ? You don't
happen to have SYSDBA as that user, do you ?

Stefan

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

 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@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx>
SYS@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> @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@xxxxxxxxxxxxxxxxx
www.credit-suisse.com

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

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>
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
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@xxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxx
> 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
> ==============================================================================
>
>
==============================================================================
Please access the attached hyperlink for an important electronic communications 
disclaimer:

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


Other related posts: