Re: create logon trigger
From: DA Morgan <damorgan_at_psoug.org>
Date: Sat, 26 Apr 2008 16:04:04 -0700
Message-ID: <1209251043.101214@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 16:04:04 -0700
Message-ID: <1209251043.101214@bubbleator.drizzle.com>
Pedro Lopes wrote:
> 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
It can be but then it only applies to connections through the application making the database vulnerable to anyone that connects using any other tool. It also requires modifying the application source code. Something rarely easy to do.
Database security belongs in the database ... and nowhere else.
-- 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 - 18:04:04 CDT