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

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

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

"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;

/

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

"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

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;
/

But on the other hand this works, I am totally confused:

create or replace trigger test after logon on database

2 begin

3 if user='TEST' then

4 raise_application_error(-20000, 'You are not allowed to login');

5 end if;

6 end;

7 /

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

Jerome Vitalis wrote:
> 3. On my Windows box, SQL*Plus shows up as 'sqlplus.exe' (without w) in
> gv$session.
>
> Jérôme

Because you're running the DOS client (sqlplus.exe), presumably? The
Windows client shows up as "sqlplusw.exe" on my Windows box.