This content has been marked as final. Show 2 replies
>> how to give a truncate grants for a table to particular user?
Normally a user can truncate his own schema table, but that user can't truncate another user's schema table, moreover, truncate is not a DML privilege to grant, rather it's DDL command, so, to truncate another user's schema table, that user has to have the DROP ANY TABLE (This is not at all suggestable).
>> after creating that procedures we have to give execute procedure grants to that user?
To grant the execute privilege on procedure,
grant execute on <procedure_name> to <user>
>> but i came to know partially that apart from creating procedure and giving exec grants there is something which can de done thru dictionary_accesscibility_07 view or something.
This is wrong.
Read the following link notes.
Drop any table privilege is used to truncate the table.( Delete any table privs is not used to truncate the table)
Its not good way.so we go to create procedure script method.
grant drop any table to test_trunc;
truncate table test_table.test;
1. Create the procedure to truncate the table.
create or replace procedure truncate_table (
dbms_output.put_line('Truncating Table : '|| table_name ||
' Storage : '|| storage_type);
crsor := dbms_sql.open_cursor;
dbms_sql.parse(crsor, 'truncate table '|| table_name ||
' '|| storage_type ,dbms_sql.v7);
rval := dbms_sql.execute(crsor);
2. Grant execute on truncate_table to <user_name>;