truncate privilege

[rommel]

Hi,

Can an ordinary user truncate other user's tables?

[gandolf989]

Well according to tahiti.oracle.com:

To truncate a table or cluster, the table or cluster must be in your schema or you must have DROP ANY TABLE system privilege.

You can create a schema owner that has the drop any table privilege, and then create a package under that user that can truncate a table in any schema and then grant execute to those users that need that privilege. You can even log that a user truncated a table with that package. But why do you want users in one schema truncating data in another?

[rommel]

Hi,

We have batch jobs that is being run every 5pm up to 7am and this is turned over to the night shift job schedulers/operators. They have
their own scheduler/operator IDs in the database where synonyms of
the prod tables are create/granted to. We have lots of big temporary
transaction tables and to initilize them using delete takes time.
So it is safe to grant drop any table to this sched's id?

[gandolf989]

I would assume so. I would just limit who has access to that privilege and let people access it through packages, with logging and error handling, where the package specifically truncates only those tables that need to be truncated.