Home » RDBMS Server » Security » HOW TO AVOID TRUNCATE AND DROP IN ORACLE SQL DEVELOPER (ORACLE 10G ON LINUX)
HOW TO AVOID TRUNCATE AND DROP IN ORACLE SQL DEVELOPER [message #303945] Mon, 03 March 2008 06:39 Go to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
As we can use table product_user_profile for sqlplus tool to avoid Drop and Truncate through sqlplus. Is there any way that we can stop drop and truncate from the tool called 'ORACLE SQL DEVELOPER'

Regards,

Mifi
Re: HOW TO AVOID TRUNCATE AND DROP IN ORACLE SQL DEVELOPER [message #303950 is a reply to message #303945] Mon, 03 March 2008 07:03 Go to previous messageGo to next message
Michel Cadot
Messages: 58625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try in the same way as for SQL*Plus using "SQL Developer" or "Oracle SQL Developer" for product.

Come back here to share your results.

Regards
Michel
Re: HOW TO AVOID TRUNCATE AND DROP IN ORACLE SQL DEVELOPER [message #303966 is a reply to message #303950] Mon, 03 March 2008 08:13 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
No affect after inserting a record
Re: HOW TO AVOID TRUNCATE AND DROP IN ORACLE SQL DEVELOPER [message #303967 is a reply to message #303966] Mon, 03 March 2008 08:19 Go to previous messageGo to next message
Michel Cadot
Messages: 58625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So it seems SQL Developer does not check it.

Regards
Michel
Re: HOW TO AVOID TRUNCATE AND DROP IN ORACLE SQL DEVELOPER [message #303975 is a reply to message #303967] Mon, 03 March 2008 09:37 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
I have got the solution

Writing following trigger for drop on whole schema will do the trick like following

CREATE OR REPLACE TRIGGER drop_trigger
BEFORE DROP ON <schema name>.SCHEMA

BEGIN
RAISE_APPLICATION_ERROR (
num => -20000,
msg => 'Cannot drop object');
END;
Re: HOW TO AVOID TRUNCATE AND DROP IN ORACLE SQL DEVELOPER [message #303977 is a reply to message #303975] Mon, 03 March 2008 09:46 Go to previous messageGo to next message
Michel Cadot
Messages: 58625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is not the same purpose but if it fits your requirements.
If you want to disable the command only for sql developer you can check module in v$session for one of the values I gave.

Regards
Michel
Re: HOW TO AVOID TRUNCATE AND DROP IN ORACLE SQL DEVELOPER [message #303991 is a reply to message #303977] Mon, 03 March 2008 11:04 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
i WANTED THAT MY USER who is the owner of table should not be allowed to drop or truncate his own table from any tool. Only sys user can do it

Re: HOW TO AVOID TRUNCATE AND DROP IN ORACLE SQL DEVELOPER [message #303992 is a reply to message #303991] Mon, 03 March 2008 11:07 Go to previous message
Michel Cadot
Messages: 58625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ok, so database trigger is definitively the way to do it and you have it. Wink

Regards
Michel
Previous Topic: DES,Triple DES and AES
Next Topic: HOW TO AVOID DROP COLUMN IN WHOLE SCHEMA BY THE OWNER
Goto Forum:
  


Current Time: Wed Jul 30 17:13:30 CDT 2014

Total time taken to generate the page: 0.12723 seconds