In order to truncate a table of other user, the DROP ANY TABLE system privilege is required. Without granting this powerful privilege, instead, a procedure is created and granted to the user who can truncate other user’s tables.
In this example, user B is able to truncate table TEST of user A without needing “DROP ANY TABLE” system privilege.
1) In schema A, create a procedure called “truncate_tab”:
SQL>show user USERA SQL>create or replace procedure truncate_tab (tab_name IN varchar2) as begin execute immediate 'truncate table '||tab_name ; end; /
2) Grant execute on procedure truncate_tab to user B:
SQL>show user USERA SQL>grant execute on a.truncate_tab to B; Grant succeeded.
3) Truncate table A.TEST by user A:
SQL>show user USERB SQL> exec a.truncate_tab('TEST'); PL/SQL procedure successfully completed.