Re: create logon trigger
From: Pedro Lopes <pedro.lopes_at_netvisao.pt>
Date: Sat, 26 Apr 2008 21:12:14 +0100
Message-ID: <newscache$218yzj$h5o$1@newsfront4.netvisao.pt>
>>> 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 21:12:14 +0100
Message-ID: <newscache$218yzj$h5o$1@newsfront4.netvisao.pt>
DA Morgan wrote:
> 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.
Can't the SET ROLE be made at the application level ? That way only ppl coming from the application (application server) would have the role to login.
cheers,
pedro
Received on Sat Apr 26 2008 - 15:12:14 CDT