0

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

1 Answer 1

3

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)
as
begin
    execute immediate 'TRUNCATE TABLE ' || tab_name;
end;

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.

2
  • 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
    Commented Sep 18, 2015 at 18:06
  • user b calls this procedure to truncate table : execute a.truncate_my_table('table_1');
    – a1ex07
    Commented Sep 18, 2015 at 19:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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