"DA Morgan" <damorgan@psoug.org> wrote in message
news:1158635992.650918@bubbleator.drizzle.com...
> Simo Silmu wrote:
>> "DA Morgan" <damorgan@psoug.org> wrote in message
>> news:1158499972.933788@bubbleator.drizzle.com...
>>> Sybrand Bakker wrote:
>>>> On Sun, 17 Sep 2006 10:03:31 GMT, "Simo Silmu" <kalle@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