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
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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.
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.