Pedro Lopes wrote:
> DA Morgan wrote:
>> Pedro Lopes wrote:
>>> emdproduction@[EMAIL PROTECTED]
wrote:
>>>> Dear group,
>>>>
>>>> I want to maintain a table, so that if people in this table, they can
>>>> logon using sqlplus, if they are not, they can only logon through our
>>>> application server.
>>>>
>>>> So
>>>>
>>>> this trigger works fine
>>>> ============
>>>> CREATE OR REPLACE TRIGGER rds_logon_trigger
>>>> 2 AFTER LOGON ON DATABASE
>>>> 3 BEGIN
>>>> 4 IF SYS_CONTEXT('USERENV','IP_ADDRESS') not in
>>>> ('192.168.2.1','192.168.2.2','192.168.2.3') THEN
>>>> 5 RAISE_APPLICATION_ERROR(-20003,'You are not allowed to connect
to
>>>> the database');
>>>> 6 END IF;
>>>> 7* end;
>>>> =============
>>>> But if i want to use a query, I got an error
>>>>
>>>> =================
>>>> CREATE OR REPLACE TRIGGER rds_logon_trigger
>>>> AFTER LOGON ON DATABASE
>>>> BEGIN
>>>> IF SYS_CONTEXT('USERENV','IP_ADDRESS') not in (select '1' from dual)
>>>> THEN
>>>> RAISE_APPLICATION_ERROR(-20003,'You are not allowed to connect to the
>>>> database');
>>>> END IF;
>>>> end;
>>>> 2/47 PLS-00405: subquery not allowed in this context
>>>>
>>>>
>>>> Is there any way I can achieve what i wanted?
>>>>
>>>> Thanks for your help
>>>
>>> My 2 cents... go for Secure Application Roles
>>>
>>> example here:
>>>
>>>
http://www.oracle.com/technology/obe/obe10gdb/security/approles/approles.htm
>>>
>>>
>>> cheers,
>>> pedro
>>
>> I would be fascinated to hear an explanation of how this could be used
>> to address the question asked by the OP without the use of an AFTER
>> LOGON trigger.
>
> Can't the SET ROLE be made at the application level ?
> That way only ppl coming from the application (application server) would
> have the role to login.
>
> cheers,
> pedro
It can be but then it only applies to connections through the
application making the database vulnerable to anyone that connects
using any other tool. It also requires modifying the application source
code. Something rarely easy to do.
Database security belongs in the database ... and nowhere else.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Wa****ngton
damorgan@[EMAIL PROTECTED]
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
|
