Re: insufficient privileges when tryning to truncate table.

The documentation is wrong. Truncation, under the covers is really ddl, not dml. You need to grant DROP any table to a user to allow them to truncate a table in another schema. Unless this doesn't bother you as a scary security problem, go ahead and do it. I have heard rumors of a new truncate authority, but haven't seen anything concrete yet.

Another option would be to create a stored procedure that uses dynamic sql (can't do regular ddl in a stored procedure) to truncate the table and grant execute on the sp to the whomever you want to allow truncate to. As an added security measure, you could set up a table containing userid/schema/table to validate against (this user can truncate this table in this schema). If you have access to Metalink (free to metal support customers), I know there is an example of the truncate stored procedure (w/o the security extension) available. You should just be able to search on truncate procedure to find it.

HTH, Frank
On Tue, 23 Jun 1998 08:20:26 GMT, rune.slinning_at_bigfoot.com wrote:

>Hi!
>
>I have an Oracle 7.3.3 installation with three schemas, adb, cdb and dfo. I
>have defined 3 users with the same name as the schemas which owns the tables
>in their schemas.
>
>From SQL plus, logged in as user adb, I tried to truncate a table in schema
>cdb, I'll get the follwing error message:
>
>SQL> truncate table cdb.unit;
>truncate table cdb.unit
> *
>ERROR at line 1:
>ORA-01031: insufficient privileges
>
>SQL>
>
>According to the Oracle manuals, the users must be granted delete table to be
>able to truncate the table. The adb user is given the following grant:
>
>grant select any table to adb;
>grant delete any table to adb;
>grant update any table to adb;
>grant insert any table to adb;
>
>Do anybody know why user adb can't truncate tables in schema cdb?
>
>Rune
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/ Now offering spam-free web-based newsreading
Received on Tue Jun 23 1998 - 07:12:04 CDT