Re: create logon trigger

emdproduction_at_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

What you are doing makes a lot of sense but reformulate it as follows:

SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') INTO user_ip
FROM dual;

Then use the user_ip variable as the basis of your decision making tree.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org