Re: How to prevent deletes when DELETE ANY TABLE granted?

John Verbil wrote:
> Todd Verstraten wrote:
> >
> > What about creating a stored procedure to do the truncation and NOT
> > granting delete any table. Then you can grant execute as needed and get
 a
> > degree of control within the proc.
> >
> > ....
>
> I liked your idea of an essentially declarative method rather than
> procedural, but unfortunately, two problems crop up with this idea:
>
> 1. You cannot to TRUNCATEs in PL/SQL, only SQL*Plus (at least on 7.2.3)

Yes you can. Using the supplied package DBMS_SQL you can do TRUNCATE's in PL/SQL from 7.1 up.

> 2. Even if you could, the person executing the stored procedure must
> have DELETE ALL TABLE in order to do a TRUNCATE.

No he doesn't. De procedure is always executed in de security-domain of de owner of de procedure. So the procedure-owner needs the delete all table privilege, the person executing the procedure only needs the execute privilege.

Ofcourse you may use any method you want but Todd's idea sure was a very good one.

Frank Klasens

Frank.Klasens_at_nlmstfsc.origin.nl

All expressed opinions are mine etc. Received on Mon Dec 16 1996 - 00:00:00 CST