Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to prevent users using toad and sqlplus from their client

Re: How to prevent users using toad and sqlplus from their client

From: Jerome Vitalis <vitalismanREMOVETHAT_at_gmail.com>
Date: Tue, 19 Sep 2006 14:52:53 +0200
Message-ID: <450fe7fc$0$17142$626a54ce@news.free.fr>


Simo Silmu wrote:
> Hmm,
>
> still trigger doesn't fire, any ideas.
>
>
> 1* select program, schemaname from gv$session where program='sqlplusw.exe'
> SQL> /
>
> PROGRAM
> --------------------------------------------------------------------------------
> SCHEMANAME
> --------------------------------------------------------------------------------
> sqlplusw.exe
> TEST
>
>
> --------------------------------------------------------
> CREATE OR REPLACE TRIGGER ban_sqlplus
> AFTER LOGON
> ON DATABASE
> declare
> v_program varchar2(48);
> v_schemaname varchar2(30);
> BEGIN
> SELECT program, schemaname into v_program, v_schemaname
> FROM gv$session
> WHERE audsid=sys_context('USERENV','SESSIONID');
>
> IF v_schemaname='TEST' and v_program='sqlplusw.exe'
> THEN
> RAISE_APPLICATION_ERROR(-20001, 'You are not allowed to login');
> END IF;
> END;
> /
>
>

  1. When the trigger is fired, gv$session.schemaname is the name of the owner of the trigger, so it can't work. Use username instead.
  2. You have to grant 'select on gv_$session' to your users for this trigger to work (see if it is compatible with your security policy!)
  3. On my Windows box, SQL*Plus shows up as 'sqlplus.exe' (without w) in gv$session.

Jérôme Received on Tue Sep 19 2006 - 07:52:53 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US