How to Revoke Delete Permission from a MySQL User with All Privileges

Learn how to effectively remove delete permissions from a MySQL user without losing other important privileges using various strategies. --- This video is based on the question https://stackoverflow.com/q/67430987/ asked by the user 'Pioz' ( https://stackoverflow.com/u/302005/ ) and on the answer https://stackoverflow.com/a/67431346/ provided by the user 'Akina' ( https://stackoverflow.com/u/10138734/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions. Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Revoke only delete from user with all privileges on MySQL Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l... The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license. If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com. --- How to Revoke Delete Permission from a MySQL User with All Privileges Managing access permissions in MySQL can sometimes feel overwhelming, especially when dealing with users that have ALL PRIVILEGES. In this guide, we'll explore a common scenario: how to revoke the delete permission from a user with extensive database access without having to strip them of all their privileges. The Initial Problem Let's say you have a MySQL user named foo who has been granted all privileges on a specific database called mydatabase. You want to ensure the user can perform most database operations but prevent them from deleting records from a particular table, mytable. You might attempt to revoke the delete permissions with the following command: [[See Video to Reveal this Text or Code Snippet]] Unfortunately, this leads to an error: [[See Video to Reveal this Text or Code Snippet]] This is because the REVOKE command can only remove privileges that have already been explicitly granted. When you grant ALL PRIVILEGES, the DELETE permission is bundled with many others, and MySQL does not track DELETE as an independent privilege for revocation. Understanding Privilege Management To understand how to properly revoke privileges, we need to recognize that: GRANT: This command adds rows into the privileges table, assigning specified permissions to a user. REVOKE: This command deletes rows associated with specific privileges from the privileges table. Steps to Revoke DELETE Permission There are a few strategies you can implement to remove the DELETE privilege effectively: Create a Specific Privilege List: Re-grant all the necessary privileges to foo, except for the DELETE permission on mytable. This involves explicitly specifying each privilege you want to grant. [[See Video to Reveal this Text or Code Snippet]] Grant DELETE on All Other Tables Except mytable: This option can be complex but involves granting DELETE permission on every other table in the mydatabase, while ensuring mytable is excluded. [[See Video to Reveal this Text or Code Snippet]] Remove ALL PRIVILEGES and Re-add Specific Grants: This is a more drastic option, where you remove all privileges first and then carefully add all needed ones back in, excluding DELETE. [[See Video to Reveal this Text or Code Snippet]] An Alternative Approach: Using Triggers If you're looking for a simpler, programming-oriented solution, consider leveraging database triggers. You can create a trigger to prevent the deletion on mytable for the user foo. Here’s an example of how you could implement this: [[See Video to Reveal this Text or Code Snippet]] Important Note: Be aware that this trigger approach does not prevent deletions through cascaded foreign key actions. It’s essential to ensure that your application logic aligns with this solution to prevent unintended data modifications by users. Conclusion Removing permissions effectively while managing extensive privileges can indeed be complex in MySQL. By understanding the implications of the GRANT and REVOKE commands, you can devise strategies that suit your specific needs. Whether you choose to explicitly list required privileges or implement triggers, ensure your database remains secure and functional. If you're still confused or have questions, feel free to reach out. Happy coding!