| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Restrict login for a particular user to be only from particul ar m achines
Thanks for all the replies. My initial attempts used an AFTER LOGON ON SCHEMA, not DATABASE, trigger.
However, I'm now stymied in a simple test case: the following SYS trigger is firing, but no error appears on login.
create or replace trigger verify_client
after logon on database when (user='PB')
begin
insert into pb.foo values('Test');
commit;
raise_application_error(-20999,'Not authorized');
end;
/
Here's what happens:
SQL> @conn pb/pb
Connected.
PB_at_CSAR.REGRESS.COM> select * from foo;
BAR
PB_at_CSAR.REGRESS.COM> @conn pb/pb
Connected.
PB_at_CSAR.REGRESS.COM> select * from foo;
BAR
As you can probably guess, I added the "insert into pb.foo" to the trigger to verify that it is firing. What's wrong here?
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
-----Original Message-----
From: Bobak, Mark [mailto:Mark.Bobak_at_il.proquest.com]
Sent: Wednesday, September 13, 2006 12:32 PM
To: salem.ghassan_at_gmail.com; Baumgartel, Paul
Cc: oracle-l_at_freelists.org
Subject: RE: Restrict login for a particular user to be only from particul ar m achines
Ghassan beat me to it....
See: http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:5069874198829 <http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:5069874198829>
For a nice example.
-Mark
-- Mark J. Bobak Senior Oracle Architect ProQuest Information & Learning "A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly. Specialization is for insects." --Robert A. Heinlein _____ From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ghassan Salem Sent: Wednesday, September 13, 2006 12:31 PM To: Baumgartel, Paul Cc: oracle-l_at_freelists.org Subject: Re: Restrict login for a particular user to be only from particul ar m achines raise an error (raise_application_error, or simply raise any_error) On 9/13/06, Baumgartel, Paul < <mailto:paul.baumgartel_at_credit-suisse.com> paul.baumgartel_at_credit-suisse.com> wrote: Right, but if the machine name isn't one of the allowed machines, what action does the trigger take? 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 <mailto:paul.baumgartel_at_credit-suisse.com> www.credit-suisse.com <http://www.credit-suisse.com> -----Original Message----- From: Ghassan Salem [mailto: salem.ghassan_at_gmail.com <mailto:salem.ghassan_at_gmail.com> ] Sent: Wednesday, September 13, 2006 12:29 PM To: paul.baumgartel_at_credit-suisse.com <mailto:paul.baumgartel_at_credit-suisse.com> Cc: oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org> Subject: Re: Restrict login for a particular user to be only from particular m achines use a logon trigger to test for the machine's name/ip rgds 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, 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 <mailto:paul.baumgartel_at_credit-suisse.com> www.credit-suisse.com <http://www.credit-suisse.com>Received on Wed Sep 13 2006 - 13:07:09 CDT
==============================================================================
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.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
 |
 |