Could you please post the privs for that user?
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 ==============================================================================