On Apr 25, 4:52=A0pm, emdproduct...@[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
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> CREATE OR REPLACE TRIGGER rds_logon_trigger
> =A0 2 =A0AFTER LOGON ON DATABASE
> =A0 3 =A0BEGIN
> =A0 4 =A0IF SYS_CONTEXT('USERENV','IP_ADDRESS') not in
> ('192.168.2.1','192.168.2.2','192.168.2.3') THEN
> =A0 5 =A0RAISE_APPLICATION_ERROR(-20003,'You are not allowed to connect
to=
> the database');
> =A0 6 =A0END IF;
> =A0 7* end;
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> But if i want to use a query, I got an error
>
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> 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 =A0 =A0 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.
|
