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

My 2 cents... go for Secure Application Roles

example here:

http://www.oracle.com/technology/obe/obe10gdb/security/approles/approles.htm

cheers,
pedro Received on Sat Apr 26 2008 - 07:28:31 CDT