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_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