0

I have read here Permissions for truncating a table that you need to grant ALTER permissions to the user(s) for them to be able to truncate a table. However, I am still having issues truncating the table with the user(s) being granted this role.

enter image description here

enter image description here

Any ideas why this is?

PS. I myself as the owner of the table am able to truncate by the way. Just no one else with ALTER permissions.

Community
  • 1
  • 1
Magic Mick
  • 1,475
  • 1
  • 21
  • 32
  • 1
    You can find the answer here: https://dba.stackexchange.com/questions/52828/what-permissions-are-necessary-for-truncating-a-table – Leila May 17 '17 at 04:38

1 Answers1

2

The link you are referring to is applicable to MS SQL Server. The link in the comment by Leila is also for SQL Server.

For Oracle the users must have the DROP ANY TABLE system privilege.

This in turn may not be what you want, as this system privilege is too destructive.

Tom Kyte has a solution for your problem:

Sure, this is what stored procedures are all about.

To selectively give someone the abilitly to truncate a specific table, or all of the tables owned by some schema, you would code:

create or replace procedure do_the_truncate as begin execute immediate
'truncate table T'; end;

or (any of the tables owned by some schema, or if that schema has the drop any table priv ANY table)

create or replace procedure do_the_truncate( p_tname in varchar2 ) as
begin execute immediate 'truncate table ' || p_tname; end;

and then just grant execute on that procedure to any user that needs to run that command. Since stored procedures run with the base privs of the OWNER of the procedure, you do not need any powerful privs like "drop any table" to truncate that table.

You can further enhance the do_the_truncate stored procedure to have a list of allowed tables to be truncated to tighten the security in your system

cha
  • 10,301
  • 1
  • 18
  • 26