Truncate - Must be owner of table

Moderator: NorbertKrupa

Post Reply
User avatar
nnani
Master
Master
Posts: 302
Joined: Fri Apr 13, 2012 6:28 am
Contact:

Truncate - Must be owner of table

Post by nnani » Tue Sep 10, 2013 10:50 am

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.
nnani........
Long way to go

You can check out my blogs at vertica-howto

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1811
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Truncate - Must be owner of table

Post by JimKnicely » Tue Sep 10, 2013 1:29 pm

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 :D
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

User avatar
nnani
Master
Master
Posts: 302
Joined: Fri Apr 13, 2012 6:28 am
Contact:

Re: Truncate - Must be owner of table

Post by nnani » Tue Sep 10, 2013 4:01 pm

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.
nnani........
Long way to go

You can check out my blogs at vertica-howto

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1811
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Truncate - Must be owner of table

Post by JimKnicely » Tue Sep 10, 2013 7:52 pm

Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica Database Development”