grant truncate

[j.k.]

Hi,

Is there other way that I can solve the problem as below.
Any helps would be appricated.

grant truncate on table1 to other_users
*
ERROR at line 1:
ORA-00990: missing or invalid privilege

thanks.

[Jean-Philippe Squelbut]

DELETE is pure SQL , it is known in Data Control Lanaguage and so can be
granted as a privilege.
TRUNCATE is unpure SQL, it is an Oracle trick, it is not know in DCL ant
cannot be granted.

So long.

j.k. a écrit dans le message <6v2is9$eog$1...@news.res.ray.com>...

[TurkBear]

Truncate is a 'Oracle specific' variant on the 'delete * from ....' command and
requires that the user be granted delete rights to the table....

so

' grant delete on table1 to other_users '

will also grant truncate to those users

li...@cs.umb.edu (j.k.) wrote:

>Hi,
>
>Is there other way that I can solve the problem as below.
>Any helps would be appricated.
>
>grant truncate on table1 to other_users
> *
>ERROR at line 1:
>ORA-00990: missing or invalid privilege
>
>
>thanks.
>

To reply please remove the 'nospam' part of the address

[Tom Sager]

I'd have to disagree on the GRANT advice. According to the 7.3 SQL Reference
the table must be in the user's schema in order to have TRUNCATE privlilege
or.... the user must have DELETE ANY TABLE system privilege (according to
the books, I've never tried it).

So, if Table ACCOUNT is created in schema SCOTT, and user BOB is granted
DELETE on SCOTT.ACCOUNT, user BOB will be able to perform a DELETE but not a
TRUNCATE on the table.

TurkBear wrote in message <36160091...@news2.mm.com>...

[Joe Brown]

jo...@nospam.mm.com (TurkBear) wrote:

> Truncate is a 'Oracle specific' variant on the 'delete * from ....' command and
> requires that the user be granted delete rights to the table....
>
> so
>
> ' grant delete on table1 to other_users '
>
> will also grant truncate to those users

I thought so too

Tried granting scott delete priv on a demo table.

Scott still has insufficient privileges to truncate the table.

Then granted scott all on the table

Scott still has insufficient privileges to truncate the table.

Reading through system roles and permissions (7.1.3.3.3 Win help
file), the role 'DELETE ANY TABLE' will allow the grantee to delete
from or truncate any table.

But on PO 8.0.4
Scott still has insufficient privileges to truncate the table...

I bet grant dba to scott would cover it though...

--
joebrown
@leading.net