Re: truncate; drop any table priv

A copy of this was sent to cosmin_ioan_at_msn.com (if that email address didn't require changing) On Wed, 29 Dec 1999 16:49:30 GMT, you wrote:

>hello all;
>
>I want let users truncate **some** tables from a master-user/owner's
>schema but NOT to be able to truncate just any table. If these users
>have "drop any table" priv. then they'll be able to truncate any
>table. Any way to circumvent that?
>

write a stored procedure that does the truncate. have that procedure verify that the currently logged in user is allowed to truncate the table in question. Do the truncate if, raise an error otherwise.

to truncate the table in the procedure you will need to use dbms_sql in 8.0 or beofre or EXECUTE IMMEDIATE in 8.1 and up.

>Thank you,
>
>Cosmin
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Dec 29 1999 - 11:50:00 CST