I want a user to be able to truncate a table in another users schema. I have given this user ALTER priviledges on the table, but he still cannot TRUNCATE.
Is there any way I can give a user priviledges to TRUNCATE a table in another users schema?
02-07-2001, 12:32 PM
privilege to truncate
U can use
Grant drop any table to username;
So that , the user can truncate other schema tables
02-07-2001, 03:27 PM
can you try with
grant delete on schemaname.tablename to username? if you grant drop any table to the user, he will be able to drop all tables in any schema, which is not what you want, I guess.
02-07-2001, 04:24 PM
grant delete, I am sure, will not work since
truncate is ddl instead of dml.
02-07-2001, 06:24 PM
I don't think you want to grant the user DROP ANY TABLE.
You can create a procedure (as table owner) that truncates the table, and grant execute on the procedure to the other user. The table owner needs CREATE TABLE privilege to execute this procedure, granted directly and not through a role. You can use dynamic SQL to truncate the table.