Re: guideline for creating temporary (working) table

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.
>
> 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 --