Truncate - Must be owner of table

[nnani]

Hello All,

I am trying to truncate table which is owned by different user.

Code: Select all

Truncate table test1;

ROLLBACK 3989:  Must be owner of relation test1

What permission/privileges do we need to be able to truncate a table owned by different user.
Is it just "TRUNCATE" ? Is the Truncate not included in the "USAGE" Privilege granted to user.

[JimKnicely]

Hi,

In Vertica only a table owner can truncate a table.

Check out the thread:

http://www.vertica-forums.com/viewtopic.php?f=5&t=396

Oracle has the DROP ANY TABLE privilege which permits a non table owner to truncate a table, but that privilege is a dangerous privilege to grant to a non admin user. A work around in Oracle was to create a stored procedure owned by the table owner that does the actual truncate. Then we'd grant the execute privilege on the procedure to the user that needed to truncate the table.

I wonder if we can do something similar in Vertica using an external procedure? I bet we can

[nnani]

Thanks Jim,

I will surely try this option of External Procedure.

Also, Now that the External Procedure topic is out,
Did any of you tried the program given in Programing guide for External Procedure.
That example does not work and throws a error every time.

[JimKnicely]

FYI...

http://www.vertica-forums.com/viewtopic.php?f=63&t=1329