Re: Truncate not working

bwskiles_at_adpc.purdue.edu wrote:
>
> I'm stumped and need help rather quickly to get a monthly refresh going.
>
> There's a table, COMPLETE.PAYROLL, and a user, OPERATIONS, that needs to truncate
> the table. OPERATIONS has been granted the DELETE ANY TABLE system privilege
> by SYS, and also has insert, select, update, delete rights to the table.
>
> When OPERATIONS issues the "truncate table complete.payroll;" command, it gets the
> insufficient privileges error (ORA-01031). I also granted the COMPLETE user the
> DELETE ANY TABLE privilege with admin option, and had COMPLETE grant the DELETE
> ANY TABLE privilege to OPERATIONS. Same error.
>
> OPERATIONS can successfully issue the "delete from complete.payroll;" command, but
> as there are a number of tables to be truncated, each with several indexes, this
> is not sufficient. The only way in which OPERATIONS has been able to truncate the
> table is if I grant it the DBA role. This is *not* an option!
>
> We are running 7.2.3 on an HP-UX platform. Any quick insight would be greatly
> appreciated!
>
> TIA.
>
> -- brad skiles,
> Purdue University

In versions subsequent to 7.0 the "DROP ANY TABLE" privilege is required to truncate a table in another schema. The DBA role has the DROP ANY TABLE privilege. If you do not wish to grant the DROP ANY TABLE privilege to OPERATIONS ( you might have good reason not to!), try creating a procedure that will truncate a table. Grant "EXECUTE" on this procedure to OPERATIONS.

Good Luck,
Naresh.

P.S...
The last time I called WWS about this, I was given to understand that a brand new privilege "TRUNCATE ANY TABLE" has been announced by ORACLE and can be expected to show up within the year. Incidentally the 7.2 addendum for Server Administrator's Guide has mentioned that DELETE ANY TABLE will no longer work. Received on Wed Jun 05 1996 - 00:00:00 CEST