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

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