Re: Permission to TRUNCATE table

"DJ" <nospamplease_at_goaway.com> wrote in message news:<2uhlb.548$vr5.250_at_newsfep4-winn.server.ntli.net>...
> "Elmo" <DoNoSpam_at_NoSpam.org> wrote in message
> news:bn45d8$19s6$1_at_f04n12.cac.psu.edu...
> > I have set up a ROLE and assigned permissions to INSERT, SELECT,
> > DELETE, and UPDATE tables to that ROLE.
> >
> > I have a user with that ROLE.
> >
> > The user cannot TRUNCATE TABLE_NAME but can DELETE FROM TABLE_NAME.
> >
> > What permission/rights needs to be granted in order to TRUNCATE the
> > table?
> >
> > Anyone with a clue? I'm fresh out.
> >
>
> is it through a procedure? could well need to explicitly grant truncate to
> that user

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 -- Received on Wed Oct 22 2003 - 08:20:57 CDT