Path: text.usenetserver.com!out02a.usenetserver.com!news.usenetserver.com!in01.usenetserver.com!news.usenetserver.com!news.tele.dk!news.tele.dk!small.news.tele.dk!bnewspeer01.bru.ops.eu.uu.net!bnewspeer00.bru.ops.eu.uu.net!emea.uu.net!newshub.netvisao.pt!not-for-mail
From: Pedro Lopes <pedro.lopes@netvisao.pt>
Newsgroups: comp.databases.oracle.server
Subject: Re: create logon trigger
Date: Sat, 26 Apr 2008 21:12:14 +0100
Organization: Netvisao, A sua Internet por Cabo
Lines: 62
Message-ID: <newscache$218yzj$h5o$1@newsfront4.netvisao.pt>
References: <381dd266-90c3-45e7-8b2d-20afe2ecc087@f36g2000hsa.googlegroups.com> <newscache$8kmxzj$xja$1@newsfront4.netvisao.pt> <1209228540.896027@bubbleator.drizzle.com>
NNTP-Posting-Host: newsfront4.netvisao.pt
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Trace: newshub.netvisao.pt 1209240884 1989 213.228.128.120 (26 Apr 2008 20:14:44 GMT)
X-Complaints-To: abuse@netvisao.pt
NNTP-Posting-Date: Sat, 26 Apr 2008 20:14:44 +0000 (UTC)
User-Agent: Thunderbird 2.0.0.12 (Windows/20080213)
In-Reply-To: <1209228540.896027@bubbleator.drizzle.com>
X-NNTP-Posting-Host: pa3-84-90-5-238.netvisao.pt
Xref: usenetserver.com comp.databases.oracle.server:444163
X-Received-Date: Sat, 26 Apr 2008 16:10:17 EDT (text.usenetserver.com)

DA Morgan wrote:
> Pedro Lopes wrote:
>> emdproduction@hotmail.com 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