Privilege needed to TRUNCATE a table

[Josh]

Hello,

Anyone know what privilege I need to grant to a user so that it can TRUNCATE a table owned by another user?

Here is the error I am getting:

Code: Select all

cognos=> TRUNCATE TABLE user_hist;
ROLLBACK 3989:  Must be owner of relation user_hist

Thanks

[becky]

Josh,

According to the SQL Reference Manual, the privileges are:

Table owner or USAGE privilege on schema that contains the table. Schema owner cannot truncate a table.

But I don't understand the part "Schema owner cannot truncate a table."

[Josh]

Thanks, Becky!

I read that information too in the documentation. But my user (cognos) does have the USAGE privilege on the schema containing the table but when I try to truncate the table, I get that error about not being the owner which like you said is really confusing because the doc save the owner can't truncate the table. Weird.

[JimKnicely]

Hi,

When you describe a table there is an owner:

Example:

Code: Select all

dbadmin=> \dt my_stats;
                   List of tables
    Schema    |   Name   | Kind  | Owner  | Comment
--------------+----------+-------+--------+---------
 sys_stats    | my_stats | table | l_sys  |
(1 row)

From my unserstanding only super users and table owners can truncate a table. In my example, other than super users, only the l_sys user can truncate the sys_stats.my_stats table.

You can change the owner of a table with the ALTER TABLE command. For instance:

ALTER TABLE <<table_name>> OWNER TO <<new_owner>>;

[scutter]

Also be aware that 5.1 behavior in this area is different than 6.0.

--Sharon

[becky]

Scutter,

On page 145 of the 5.1 Admin. Guide it says the required privilege needed to truncate a table is "Table owner".

But on page 133 of the 6.0 Admin. Guide it says the required privilege needed to truncate a table is "USAGE privilege on the schema that contains the table".

But in 6.0 when I try an truncate a table as a user only having the USAGE privilege I can't truncate the table... The user has to be the owner.

I'm curious what you meant when you wrote

Also be aware that 5.1 behavior in this area is different than 6.0.

The functionality seems to be the same even though the docs say it should be different - unless I'm missing something

[scutter]

Ah, so there's a doc bug in 6.0. I had read that reference to USAGE and thought they had loosened the permission requirement for TRUNCATE in 6.0. At least in 6.0 you can use ALTER TABLE to change the owner if you need to.