Home » RDBMS Server » Server Administration » Creating Trigger to prevent dropping/truncating tables
Creating Trigger to prevent dropping/truncating tables [message #315785] Tue, 22 April 2008 15:12 Go to next message
bogey
Messages: 53
Registered: February 2006
Member
We ran into a problem today when a user notified us that they dropped a table withing their scheama. Since they are the owners of the schema they have total control. I was wondering if its wise to create a before trigger to prevent the user(s) from dropping/truncating tables. What I have below will notify admins if a user does try this.

-
-- TRIGGER_DATABASE_TRUNC_DROP  (Trigger) 
--
CREATE OR REPLACE TRIGGER TRIGGER_DATABASE_TRUNC_DROP
BEFORE DROP OR TRUNCATE
ON DATABASE
DECLARE 
  SENDER VARCHAR2(200);
  RECIPIENTS VARCHAR2(200);
  CC VARCHAR2(200);
  BCC VARCHAR2(200);
  SUBJECT VARCHAR2(200);
  MESSAGE VARCHAR2(200);
  MIME_TYPE VARCHAR2(200);
  PRIORITY BINARY_INTEGER; 
  
BEGIN
  
 
  SENDER := 'xxx';
  RECIPIENTS := 'xxx';
  CC := NULL;
  BCC := NULL;
  SUBJECT := 'xxx';
  MESSAGE := 'Errors drop/trunc';
  MIME_TYPE := NULL;
  PRIORITY := NULL;

  SYS.UTL_MAIL.SEND ( SENDER, RECIPIENTS, CC, BCC, SUBJECT, MESSAGE, MIME_TYPE, PRIORITY );
   RAISE_APPLICATION_ERROR(-20000, 'DROPPING OR TRUNCATING IS NOT ALLOWED IN PRODUCTION DATABASE');
  COMMIT;                         
END;
/
Re: Creating Trigger to prevent dropping/truncating tables [message #315790 is a reply to message #315785] Tue, 22 April 2008 15:37 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Couple of things:

1. If you put this trigger in the schema(s), then the schema owner can still drop the trigger.
2. commit does nothing in this context
3. The dropped table is most likely in the recyclebin for recovery.
Re: Creating Trigger to prevent dropping/truncating tables [message #315795 is a reply to message #315790] Tue, 22 April 2008 15:47 Go to previous messageGo to next message
Littlefoot
Messages: 20893
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
And you, of course, have yesterday's backup, haven't you?

Besides, is it a good idea to forbid dropping tables? (Some) developers tend to create "temporary" tables now and then in order to save some data and easily recover from changes (which weren't done the way they supposed to be), change their mind (drop a few tables, create another few) etc. Who will clean up this mess if they can't clean up this "garbage" themselves?

[Updated on: Tue, 22 April 2008 15:47]

Report message to a moderator

Re: Creating Trigger to prevent dropping/truncating tables [message #315836 is a reply to message #315785] Tue, 22 April 2008 23:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do they have tables in their schema if they don't really own them?

Regards
Michel
Re: Creating Trigger to prevent dropping/truncating tables [message #315999 is a reply to message #315836] Wed, 23 April 2008 08:10 Go to previous messageGo to next message
bogey
Messages: 53
Registered: February 2006
Member
"Since they are the owners of the schema they have total control"

They own the schema so they can do what they want.


"And you, of course, have yesterday's backup, haven't you?"

Sure, but if a user drops a table I cannot do a point in time recovery in a production database.

"Who will clean up this mess if they can't clean up this "garbage" themselves? "

My whole point here is to limit what a user can do in a production database. There seems to be no consensus on this. Do we let users do what they want e.g. Create, Drop, Alter, Truncate.....?
Re: Creating Trigger to prevent dropping/truncating tables [message #316000 is a reply to message #315785] Wed, 23 April 2008 08:12 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>My whole point here is to limit what a user can do in a production database.
Users should NOT have access to Production database; only the DBA should ever be able to log onto Production instance!

[Updated on: Wed, 23 April 2008 08:12] by Moderator

Report message to a moderator

Re: Creating Trigger to prevent dropping/truncating tables [message #316001 is a reply to message #315999] Wed, 23 April 2008 08:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 23 April 2008 06:23
Why do they have tables in their schema if they don't really own them?

Regards
Michel


Re: Creating Trigger to prevent dropping/truncating tables [message #316006 is a reply to message #316000] Wed, 23 April 2008 08:34 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

Users should NOT have access to Production database; only the DBA should ever be able to log onto Production instance!



Umm... Don't they need at least SOME access? Aren't they the people who actually WORK on that database? Razz

( Through applicable front ends of course )

And they they definitely shouldn't be able to do any DDL for example.

Re: Creating Trigger to prevent dropping/truncating tables [message #316029 is a reply to message #316006] Wed, 23 April 2008 09:11 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
ThomasG wrote on Wed, 23 April 2008 06:34
Quote:

Users should NOT have access to Production database; only the DBA should ever be able to log onto Production instance!



Umm... Don't they need at least SOME access? Aren't they the people who actually WORK on that database? Razz

( Through applicable front ends of course )

And they they definitely shouldn't be able to do any DDL for example.




Users should log into the application but NOT logon database as schema user/owner to even do DML; in my opinion.
Re: Creating Trigger to prevent dropping/truncating tables [message #316119 is a reply to message #316029] Wed, 23 April 2008 16:24 Go to previous message
Littlefoot
Messages: 20893
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It seems that only @bogey doesn't understand that PRODUCTION database isn't a developer's playground. They may create/drop/truncate/whatever in a development database, prepare testing environment for people who will actually test what they've done and, when it's approved, changes are applied to the production database. There's no reason for developer to put their fingers into production.
Previous Topic: Error creating spfile from pfile
Next Topic: Software install 11g on windows
Goto Forum:
  


Current Time: Mon Dec 05 04:53:49 CST 2016

Total time taken to generate the page: 0.11793 seconds