Re: create logon trigger

On Apr 25, 4:52 pm, emdproduct..._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

Ok you are really scaring me now ...

If you can't figure out how to do it all in the trigger, then call a function that returns true or false, 1 or 0 ... however you want to do it exactly. Pass parameters as needed.

You are going to keep IP addresses in your table? Is everything static in your organization?

Yikes. Received on Fri Apr 25 2008 - 16:34:07 CDT