how to give a truncate grants for a table to particular user?
while i was trying to find in net i got some procedures.
after creating that procedures we have to give execute procedure grants to that 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.
i m not sure about that.
so if anybody know pls let me know.
thanks in advance..
>> 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.
Murali, 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. Eg:
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>;