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

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 18 Sep 2006 20:19:55 -0700
Message-ID: <1158635992.650918@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

Received on Mon Sep 18 2006 - 22:19:55 CDT