Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: guideline for creating temporary (working) table
Mark.Powell_at_eds.com (Mark D Powell) wrote in message news:<2687bb95.0402071727.6a6221bc_at_posting.google.com>...
> Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1075663639.53789_at_yasure>...
> > Ed Avis wrote:
> > > rgaffuri_at_cox.net (Ryan Gaffuri) writes:
> > >
> > >
> > >>Reason for not having truncate in production is because you might
> > >>truncate the wrong table.
> > >
> > >
> > > This doesn't make any sense. If you have delete permission on a
> > > table, then it is equally easy to 'delete from wrong_table'. To
> > > protect against such mistakes you'd have to deny permission for both
> > > delete and truncate. There may be good reasons to grant one and not
> > > the other, but avoiding such mistakes is not one of them.
> >
> > While I agree with your statement in general there is a far greater
> > danger with truncate than with delete.
> >
> > As DJ states a rollback is possible from a delete but not with a
> > truncate. But in addition, and perhaps more importantly, I can flashback
> > from a delete after commit but can not with a truncate until 10g.
> >
> > Missing from all of this, however, is that no one should ever be allowed
> > to perform an unrestricted delete or a truncation in a production
> > database. If the design allows either of these actions the design is bad
> > by definition.
Therefore, the design of SQL is bad. :-)
> >
> > Murphy's law has not been repealed.
>
> As a rule I do not think customers should have direct access to a
> production environemt. This would prevent them from directly issuing
> a delete or truncate statement to begin with. All update actions
> would have to be through a designed application.
>
> Also since the application ID should not be the application object
> owner, nor a DBA, or granted delete any table the only way it can
> issue a truncate is through a provided stored procedure. This
> provides a means if used to limit the range of the truncate command to
> expected target tables.
>
> We do not allow the end-user direct access to Oracle nor access to the
> UNIX prompt. There is no valid reason for an end-user to ever need
> either.
>
> -- Mark D Powell --
I agree with Dan and Mark. I've just never seen it totally implemented. Usually there is a direct inverse relationship between the sophistication of the users and the arrogance of the system management, with a corollary that they should be able to go directly in and "fix" things. "I su, therefore I am"
And yes, I'm as guilty of that sort of arrogance as anyone.
I notice Mark is much more careful than Dan about not excluding DBA's :-)
jg
-- @home.com is bogus. http://www.showcase.netins.net/web/clevad/weird.htmReceived on Tue Feb 10 2004 - 18:14:46 CST