SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Addict
    Join Date
    Jun 2006
    Posts
    220
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Preventing truncate operation

    I want to deactivate truncate operation on a mysql table. So even if someone tries to execute a truncate operation that would fail. How to do that?

  2. #2
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,069
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Depending on what version of MySQL you are running you either have to deny the DELETE privilege or the DROP privilege.
    http://stackoverflow.com/questions/4...-in-phpmyadmin
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  3. #3
    SitePoint Addict
    Join Date
    Jun 2006
    Posts
    220
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My MySQL version is less than 5.1.16. So ideally I need to deny the DELETE privilege. Doing so it won't be possible to delete records from that table anymore which is not possible for my application. I only need to stop the execution of truncate command. What is the alternate approach.

  4. #4
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    This could be done by creating a stored procedure which performs the delete operation on the table. Set the SQL SECURITY of the stored procedure to DEFINER. The definer of the stored procedure should have delete access on the table. Revoke delete privilege on the table and grant EXECUTE on the stored procedure to the user invoking the stored procedure.


Bookmarks

Posting Permissions

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