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

And what does (as SYS) select * from t show ?

Stefan

On 9/14/06, Stefan Knecht <knecht.stefan_at_gmail.com> wrote:
>
> 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_at_credit-suisse.com> 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_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> 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
> > > ==============================================================================
> > >
> > >
> > ==============================================================================
> > 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