Home » Other » Client Tools » TOAD Block (Oracle 10g Rel2 (HP-UX) 11.11 (PA_RISC))
TOAD Block [message #391893] Sat, 14 March 2009 05:28 Go to next message
razisolaris
Messages: 48
Registered: February 2008
Location: Riyadh
Member
Hi,

I wanted to block TOAD client software fully so no one can access even by changing TOAD_XYZ.exe file name.

Any database trigger/script to restrict all user require.

I appreciate your valuable response.

Regards,

Report message to a moderator

Re: TOAD Block [message #391895 is a reply to message #391893] Sat, 14 March 2009 05:34 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Restrict the users, not the tool.
What's so special about TOAD as opposed to sqlplus, SQL-Developer or PL/SQL-Developer?

Report message to a moderator

Re: TOAD Block [message #391898 is a reply to message #391893] Sat, 14 March 2009 05:55 Go to previous messageGo to next message
razisolaris
Messages: 48
Registered: February 2008
Location: Riyadh
Member
How can i restrict toad users by database trigger or any other script .

Previously i used below script to restrict toad.exe users, But users change toad.exe to any xyz.exe and they are able to login from toad.


SCRIPT:
======create or replace trigger ban_toad after logon on database
declare
v_sid number;
v_isdba varchar2(10);
v_program varchar2(30);
v_user varchar2(30);
begin
execute immediate 'select distinct sid from sys.v_$mystat' into v_sid;
execute immediate 'select program from sys.v_$session where sid = :b1' into v_program using v_sid;
execute immediate 'select username from sys.v_$session where sid = :b1' into v_user using v_sid;
select sys_context('userenv','ISDBA') into v_isdba from dual;
if upper(v_program) in ('TOAD.EXE','SQLPLUSW.EXE','SQLPLUS.EXE') and v_isdba = 'FALSE' and v_user not in('SYSTEM','SYS') then
raise_application_error
(-20001,'Unale to access toad',true);
end if;
end;

Report message to a moderator

Re: TOAD Block [message #391900 is a reply to message #391898] Sat, 14 March 2009 07:07 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Again, what is so evil about TOAD. What can your user do with TOAD that he cannot do with sqlplus?
You want to revoke privileges from your user. Take a look at application roles.

Report message to a moderator

Re: TOAD Block [message #391904 is a reply to message #391893] Sat, 14 March 2009 09:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
RENAME TOAD.EXE WORKAROUND.EXE
Now what happens with the silly trigger?

Report message to a moderator

Re: TOAD Block [message #391907 is a reply to message #391904] Sat, 14 March 2009 09:23 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
He kinda figured that out already..

Report message to a moderator

Re: TOAD Block [message #391909 is a reply to message #391893] Sat, 14 March 2009 09:36 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
The real solution is a change in policy/procedures.
Change the passwords & fire anyone who accesses DB without authorization.

Report message to a moderator

Re: TOAD Block [message #391912 is a reply to message #391900] Sat, 14 March 2009 09:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Frank wrote on Sat, 14 March 2009 13:07
Again, what is so evil about TOAD. What can your user do with TOAD that he cannot do with sqlplus?
You want to revoke privileges from your user. Take a look at application roles.

For instance, if you request for an execution plan of a query, it does not commit and keep a transaction opened leading to other running out of space.
This does not happen with SQL*Plus and set autotrace.

Regards
Michel

Report message to a moderator

Re: TOAD Block [message #391914 is a reply to message #391893] Sat, 14 March 2009 10:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
TOAD signs itself setting module name in v$session.
Not visible at logon time (I think) but you can have a job that checks this periodically.

That said, I agree with others, this is more an organization problem and if the policy is to not use TOAD and someone use it, he has to be fired.

Regards
Michel

Report message to a moderator

Re: TOAD Block [message #391932 is a reply to message #391912] Sat, 14 March 2009 12:53 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Michel Cadot wrote on Sat, 14 March 2009 15:58
Frank wrote on Sat, 14 March 2009 13:07
Again, what is so evil about TOAD. What can your user do with TOAD that he cannot do with sqlplus?
You want to revoke privileges from your user. Take a look at application roles.

For instance, if you request for an execution plan of a query, it does not commit and keep a transaction opened leading to other running out of space.
This does not happen with SQL*Plus and set autotrace.

Regards
Michel



I can do that just as easy in sqlplus.
What I was trying to make clear is that ANYTHING anybody can do in TOAD can also be done in other tools.

Report message to a moderator

Re: TOAD Block [message #391934 is a reply to message #391932] Sat, 14 March 2009 13:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What I mean is what is evil with TOAD is that it does things behind the scene you don't what and sometimes (like with the execution plan) it leads to many problems like breaking the work of others (or even you) and you don't know and understand this is you with your (you think) harmless tools is the root of them.
How many times we have on-call DBA wakes up in the middle of night because a treatment failed with no more space in undo just because a stupid guy left his toad opened after his shift.

Without speaking about those that urgently call DBA because TOAD put a red light on an indicator but they don't of course know what is this indicator, what it means, just it is red and DBA must do something (anything) for the light turns green.
I remember, just after 9.0 were released with undo tablespace, one called me because he "verified" (that is he told TOAD to advice) the database I created for him and toad told him the database has no rollback segment but SYSTEM one and this is very bad.

Regards
Michel

Report message to a moderator

Re: TOAD Block [message #391950 is a reply to message #391909] Sun, 15 March 2009 00:36 Go to previous messageGo to next message
razisolaris
Messages: 48
Registered: February 2008
Location: Riyadh
Member
Hi,

I appreciate all for their valuable input to me ,


Could you please define which kind of policy/procedures should be write to restrict the complete usage of TOAD by the users.

Thanks & Regards,

Report message to a moderator

Re: TOAD Block [message #391951 is a reply to message #391950] Sun, 15 March 2009 01:20 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
razisolaris wrote on Sat, 14 March 2009 22:36
Hi,

I appreciate all for their valuable input to me ,


Could you please define which kind of policy/procedures should be write to restrict the complete usage of TOAD by the users.

Thanks & Regards,



The real solution is a change in policy/procedures.
Change the passwords & fire anyone who accesses DB without authorization.

Report message to a moderator

Re: TOAD Block [message #391953 is a reply to message #391950] Sun, 15 March 2009 01:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A policy is just a text like "It is strictly forbidden to use TOAD on production database. Offender will be sacked."

Regards
Michel

Report message to a moderator

Re: TOAD Block [message #391954 is a reply to message #391953] Sun, 15 March 2009 01:37 Go to previous messageGo to next message
razisolaris
Messages: 48
Registered: February 2008
Location: Riyadh
Member
Hi,


Again how can i write a policy(format require) to restrict all TOAD users even though they change a name of TOAD.exe file.


Regards,

Report message to a moderator

Re: TOAD Block [message #391956 is a reply to message #391893] Sun, 15 March 2009 01:41 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Which part of "A policy is just a text like "It is strictly forbidden to use TOAD on production database. Offender will be sacked." do you not understand?

Report message to a moderator

Re: TOAD Block [message #391958 is a reply to message #391956] Sun, 15 March 2009 02:35 Go to previous messageGo to next message
razisolaris
Messages: 48
Registered: February 2008
Location: Riyadh
Member
Hi,

At present i dont have any policy created, In group policy nothing is there , Now how to proceed further require help step by step ..........

OS:HP-UX
DB:10g Rel2


Thanks in Advance.

Report message to a moderator

Re: TOAD Block [message #391959 is a reply to message #391958] Sun, 15 March 2009 02:37 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Let's rephrase what was said over the last zillion replies:

YOU CAN NOT.


Maybe you will understand that

Report message to a moderator

Re: TOAD Block [message #391964 is a reply to message #391959] Sun, 15 March 2009 04:56 Go to previous messageGo to next message
razisolaris
Messages: 48
Registered: February 2008
Location: Riyadh
Member
Thanks buddy for you hard efforts .............

Report message to a moderator

Re: TOAD Block [message #392597 is a reply to message #391893] Wed, 18 March 2009 10:29 Go to previous messageGo to next message
Kaeluan
Messages: 179
Registered: May 2005
Location: Montreal, Quebec
Senior Member
Here is another solution.
If you use trigger to validate if the application can connect to the database.
Instead of checking for the program name that may change if the user is renaming the EXE file. You could try to verify the module name instead. Even if you change the program name, the module name will stay the same.

Maybe it could worth a try.

Attached is the print screen of what i can see in v$session even if i rename the EXE file

Hope it help.

Report message to a moderator

Re: TOAD Block [message #392600 is a reply to message #392597] Wed, 18 March 2009 10:36 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Give me two minutes and a HEX editor and I will change the module-name of your exe.

Report message to a moderator

Previous Topic: SQLDeveloper
Next Topic: ora-06550 error after execute a pl/sql block?
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Apr 20 11:23:27 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.