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

From: Simo Silmu <kalle_at_nospam.com>
Date: Tue, 19 Sep 2006 03:39:25 GMT
Message-ID: <NDJPg.38525$_k2.692911@news2.nokia.com>

"DA Morgan" <damorgan_at_psoug.org> wrote in message news:1158635992.650918_at_bubbleator.drizzle.com...

> Simo Silmu wrote:

>> "DA Morgan" <damorgan_at_psoug.org> wrote in message
>> news:1158499972.933788_at_bubbleator.drizzle.com...

>>> Sybrand Bakker wrote:
>>>> On Sun, 17 Sep 2006 10:03:31 GMT, "Simo Silmu" <kalle_at_nospam.com>
>>>> wrote:
>>>>
>>>>> Well,
>>>>>
>>>>> the subject tells it all but how would you prevent users using toad 
>>>>> and sqlplus from their client (and perhaps getting nice denial 
>>>>> message).
>>>>>
>>>>> Cheers
>>>>> SS
>>>> create an after logon trigger (assuming you are on a version >= 8.1),
>>>> and determine the program using the sys_context function.
>>>> Rest should be peanuts.
>>>> However: if you have proper security set up, I don't think sql*plus
>>>> access (which of course is readonly) should be a problem.
>>>>
>>>>
>>>> --
>>>> Sybrand Bakker, Senior Oracle DBA
>>> One can also deal with SQL*Plus access using PRODUCT_USER_PROFILE.
>>>
>>> A demo can be found in Morgan's Library at www.psoug.org.
>>> -- 
>>> Daniel Morgan
>>> University of Washington
>>> Puget Sound Oracle Users Group
>>

>> Hi,

>>

>> I tried to create a trigger as follows but the problem is that it doesn't
>> fire. What might be the problem: _system_trigger_enabled is set to true.

>>

>> Any tips would be helpful :)

>>

>> Cheers
>> SS

>>

>> CREATE OR REPLACE TRIGGER ban_sqlplus

>>

>> AFTER LOGON

>>

>> ON DATABASE

>>

>> DECLARE

>>

>> --Declare a cursor to find out the program

>>

>> --the user is connecting with.

>>

>> CURSOR user_prog IS

>>

>> SELECT program, schemaname FROM v$session

>>

>> WHERE audsid=sys_context('USERENV','SESSIONID');

>>
>>

>> --Assign the cursor to a PL/SQL record.

>>

>> user_rec user_prog%ROWTYPE;

>>

>> BEGIN

>>

>> OPEN user_prog;

>>

>> FETCH user_prog INTO user_rec;

>>

>> IF substr(user_rec.program,1,7) = 'sqlplus' and
>> user_rec.schemaname='TEST'

>>

>> THEN

>>

>> RAISE_APPLICATION_ERROR(-20001, 'You are not allowed to login');

>>

>> END IF;

>>

>> CLOSE user_prog;

>>

>> END;

>>

>> /

>
> Tongue in cheek here you get today's award for using a technique
> because you know it no matter that it is totally irrelevant and,
> in fact, harmful.
>
> This is absolutely no place to use a cursor. In fact unless you are
> in a version of Oracle prior to 8.1.7 using a cursor with an explicit
> fetch is just plain bad practice.
>
> In this case you are fetching a single row into a variable so the
> most efficient way would be to just do it.
>
> Given that users of TOAD are on windows I think you will find it
> likley that your result set, though, looks more like this.
>
> SQL> SELECT program, schemaname FROM v$session
>   2  WHERE audsid=sys_context('USERENV','SESSIONID');
>
> PROGRAM
> -----------------------------------------------------
> SCHEMANAME
> ------------------------------
> sqlplusw.exe
> UWCLASS
>
> sqlplusw.exe <> sqlplus
>
> And I would suggest you query gv$session not v$session just to get
> into the habit.
> -- 
> Daniel Morgan
> University of Washington
> Puget Sound Oracle Users Group

Thanks,

seems you never sleep :)

Cheers
SS Received on Mon Sep 18 2006 - 22:39:25 CDT