RE: Restrict login for a particular user to be only from particul ar m achines
- From: "Baumgartel, Paul" <paul.baumgartel@xxxxxxxxxxxxxxxxx>
- To: "'Bobak, Mark'" <Mark.Bobak@xxxxxxxxxxxxxxx>, salem.ghassan@xxxxxxxxx
- Date: Wed, 13 Sep 2006 19:07:09 +0100
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@xxxxxxxxxxxxxxxx> select * from foo;
BAR
--------------------
Test
PB@xxxxxxxxxxxxxxxx> @conn pb/pb
Connected.
PB@xxxxxxxxxxxxxxxx> select * from foo;
BAR
--------------------
Test
Test
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@xxxxxxxxxxxxxxxxx
www.credit-suisse.com
-----Original Message-----
From: Bobak, Mark [mailto:Mark.Bobak@xxxxxxxxxxxxxxx]
Sent: Wednesday, September 13, 2006 12:32 PM
To: salem.ghassan@xxxxxxxxx; Baumgartel, Paul
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Ghassan Salem
Sent: Wednesday, September 13, 2006 12:31 PM
To: Baumgartel, Paul
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxx>
paul.baumgartel@xxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxx <mailto:paul.baumgartel@xxxxxxxxxxxxxxxxx>
www.credit-suisse.com <http://www.credit-suisse.com>
-----Original Message-----
From: Ghassan Salem [mailto: salem.ghassan@xxxxxxxxx
<mailto:salem.ghassan@xxxxxxxxx> ]
Sent: Wednesday, September 13, 2006 12:29 PM
To: paul.baumgartel@xxxxxxxxxxxxxxxxx
<mailto:paul.baumgartel@xxxxxxxxxxxxxxxxx>
Cc: oracle-l@xxxxxxxxxxxxx <mailto:oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxxxxxx
<mailto: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 <mailto:paul.baumgartel@xxxxxxxxxxxxxxxxx>
www.credit-suisse.com <http://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>
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
==============================================================================
Other related posts:
