Is it possible to give truncate rights to user A to truncate tables owned by user B?

But I should be able to truncate each table of B by A as per wish.

Some procedures would be needed I suppose? Kindly help!!

Platform - Oracle


You can give DROP ANY TABLE system privilege to A, then he will be able to truncate everything in any schema (not just in B's though) .

If you don't want to do that, a simple stored procedure created by B will do the job (procedure or function in Oracle are by default executed with definer rights) . Then you give need execute permission to A on this procedure. For instance,

create procedure truncate_my_table(tab_name varchar2)
    execute immediate 'TRUNCATE TABLE ' || tab_name;

You may also want to add parameter validation (for example, I'd do DBMS_ASSERT.ENQUOTE_NAME(DBMS_ASSERT.QUALIFIED_SQL_NAME(tab_name)) ) , error handling and logging to the procedure.

  • What is that tab name? Is it the table name? And why do we mention as varchar there? What if the user has 1000 tables? And user b needs to truncate only some tables of it? – tesla747 Sep 18 '15 at 18:06
  • user b calls this procedure to truncate table : execute a.truncate_my_table('table_1'); – a1ex07 Sep 18 '15 at 19:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.