DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: truncate privilege

  1. #1
    Join Date
    Jan 2002
    Posts
    146

    truncate privilege

    Hi,

    Can an ordinary user truncate other user's tables?

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    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?

  3. #3
    Join Date
    Jan 2002
    Posts
    146
    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?

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width