TRUNCATE TABLE problem...

From: Joe Nardone <joe_at_access1.digex.net>
Date: 1995/12/15
Message-ID: <4aqf5b$1cr_at_news4.digex.net>#1/1


I'm having a problem (well, two, one major, one minor) trying to TRUNCATE a table owned by another user.

(same problem in both 7.0.16 and 7.2.2)

User A owns the table (A.FOO), and user B needs to be able to TRUNCATE it.

Logged in as user B, TRUNCATE TABLE FOO results in table or view does not exist. (FOO is a public synonym to A.FOO, and A.FOO has been granted SELECT, INSERT, UPDATE, DELETE to B via a role which has been set.)

Logged in as user B, TRUNCATE TABLE A.FOO results in insufficient privileges.

First question: Why doesn't TRUNCATE follow the public synonym, even though it is valid and B can see A.FOO after the role has been set?

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

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 Fri Dec 15 1995 - 00:00:00 CET

Original text of this message