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

"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; / Received on Mon Sep 18 2006 - 22:09:11 CDT