Re: create logon trigger
From: DA Morgan <damorgan_at_psoug.org>
Date: Sat, 26 Apr 2008 09:49:02 -0700
Message-ID: <1209228540.896027@bubbleator.drizzle.com>
>> 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
Date: Sat, 26 Apr 2008 09:49:02 -0700
Message-ID: <1209228540.896027@bubbleator.drizzle.com>
Pedro Lopes wrote:
> 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
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.
-- 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.orgReceived on Sat Apr 26 2008 - 11:49:02 CDT