Re: Permission to TRUNCATE table

Mark D Powell wrote:

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

I agree: I would never grant that privilege to any schema or user.

The method I use is to put TRUNCATE into a procedure and then grant execute on the procedure
as appropriate.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)