Re: Permission to TRUNCATE table

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1066831917.433571_at_yasure>...
> Mark D Powell wrote:
>
> >There is no grantable truncate table privilege. By default only the
> >owner or a DBA may truncate a table.
> >
> >To get around this limitation create a procedure owned by the table
> >owner or a DBA id that issues the truncate command via an execute
> >immediate. To prevent truncating the wrong table it would be wise to
> >include a check against a list of authorized to be truncated tables.
> >
> >HTH -- Mark D Powell --
> >
> >
> The permission to TRUNCATE a table in another schema is DROP ANY TABLE.
>
> --
> Daniel Morgan
> damorgan_at_x.washington.edu
> (replace 'x' with a 'u' to reply)
>
> --

Technically true, but I find it undesirable to grant drop any table so I have modified my advice to take this option into account.

There is no directly grantable truncate table object privilege. By default only the owner of a table or a DBA may truncate a table. The ability to truncate a table is granted through the drop any table privilege; however, this privilege obviously includes the ability to drop the table.

To provide the ability to efficiently clear out a table's contents without allowing the table and its associated indexes, grants, and triggers from being dropped create a procedure owned by the table owner or an id that has drop any table privilege that issues the truncate command via an execute immediate statement. To prevent truncating the wrong table it would be wise to include a check against a list of authorized to be truncated tables within the routine.

• Mark D Powell --