Re: TRUNCATE TABLE problem...

joe_at_access1.digex.net (Joe Nardone) wrote:

>Oracle documentation says that to be able to TRUNCATE
>a table outside of one's own schema that the account
>must have DELETE ANY TABLE. We granted DELETE ANY
>TABLE to B and tried the same two TRUNCATE commands
>(TRUNCATE TALBE FOO, TRUNCATE TABLE A.FOO) result
>in the same two errors.

>Second question: What do I have to grant to B (can
>it be to B, or to a role B has?) so it can truncate
>a table owned by A? (The public synonym thing is
>a problem I can live with, this isn't.)

   Instead of using delete any table, I believe you have to use drop any table. Remember, truncate is a DDL statement, not DML. This is not really a good thing to do. If you have access to Compuserve, there is an example you can download from the Oracle support forum to create a stored proc that will allow truncation of a table without having to grant drop any table to users. I'm not sure if it is in there, but another suggestion was to make it "smart". Rather than hardcoding the tables, make it dynamic and have the user pass the table name as a parameter, and have the proc check a table with all the available table names for truncation.

The only way I have gotten truncate to work with a public synonym is in SQL*Loader. I don't think Oracle allows it with the truncate table command.

Frank

PS - The usual CYA stuff.
>Joe
>--
>
>=------------------------------------------------------------------=
>Joe Nardone <joe_at_access.digex.net>
>"Now that I have my coffee, I'm ready to watch radar."
 

>-----BEGIN PGP PUBLIC KEY BLOCK-----
>Version: 2.6.2
 

>mQBtAzCg5QoAAAEDANvEmc4OAT8wntnCv8UPjyD2kohKVGa1CU21PLpYljzl3AE7
>pldpNNscKSoE//aVMKYBO+tRFTTsPNi0qZ6CDlEBXTgJAiszoHh1qCzZyYj7FTBL
>YoUr+3sk9rU8Pma6AQAFEbQiSm9lIE5hcmRvbmUgPGpvZUBhY2Nlc3MuZGlnZXgu
>bmV0Pg==
>=1ocE
>-----END PGP PUBLIC KEY BLOCK-----
Received on Wed Dec 20 1995 - 00:00:00 CET