Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.misc -> Re: guideline for creating temporary (working) table

Re: guideline for creating temporary (working) table

From: Joel Garry <>
Date: 10 Feb 2004 16:14:46 -0800
Message-ID: <> (Mark D Powell) wrote in message news:<>...
> Daniel Morgan <> wrote in message news:<1075663639.53789_at_yasure>...
> > Ed Avis wrote:
> > > (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 :-)


-- is bogus.
Received on Tue Feb 10 2004 - 18:14:46 CST

Original text of this message