PHP Website MySQL User Permissions: Risks & Fixes

TL;DR

Giving a PHP website’s MySQL user CREATE, ALTER and DROP permissions is generally a bad idea. It massively increases the risk of your database being compromised if the website code is hacked. Limit permissions to only what’s absolutely necessary – usually just SELECT, INSERT, UPDATE and DELETE on specific tables.

Why These Permissions Are Dangerous

These permissions allow a hacker who gains control of your PHP website to:

• Create new tables: They could store malicious code or data.
• Alter existing tables: They could modify table structures to inject vulnerabilities or steal information.
• Drop tables: They could delete critical data, effectively destroying your website’s functionality.

Even if the hacker doesn’t immediately understand your database structure, these permissions give them a powerful toolkit for causing damage.

Step-by-Step Solution

1. Identify the User: First, find out which MySQL user is being used by your PHP website. This is usually defined in your website’s configuration file (e.g., config.php or similar). Look for variables like DB_USER, MYSQL_USER, or similar.

   // Example config.php snippet
   define('DB_USER', 'your_website_user');

2. Connect to MySQL as Root: You’ll need root (or an administrator) access to your MySQL server to change permissions. Use a tool like phpMyAdmin, the MySQL command line client, or a database administration GUI.
3. Check Current Permissions: See what permissions the user currently has. Using the MySQL command line:

   SHOW GRANTS FOR 'your_website_user'@'localhost';

   Replace 'your_website_user'@'localhost' with the actual username and host of your website’s MySQL user.

4. Revoke Excessive Permissions: Remove the CREATE, ALTER, and DROP permissions. If the user has global privileges (e.g., on all databases), revoke those first. Then specifically remove these permissions for your website’s database.

   REVOKE CREATE ON your_database.* FROM 'your_website_user'@'localhost';
   REVOKE ALTER ON your_database.* FROM 'your_website_user'@'localhost';
   REVOKE DROP ON your_database.* FROM 'your_website_user'@'localhost';

   Replace your_database with the name of your website’s database.

5. Grant Necessary Permissions: Grant only the permissions needed for your website to function. This typically includes:
   • SELECT: To read data from tables.
   • INSERT: To add new data to tables.
   • UPDATE: To modify existing data in tables.
   • DELETE: To remove data from tables.

   GRANT SELECT, INSERT, UPDATE, DELETE ON your_database.* TO 'your_website_user'@'localhost';

6. Flush Privileges: After making changes to permissions, tell the MySQL server to reload them.

   FLUSH PRIVILEGES;

7. Test Your Website: Thoroughly test all functionality of your website to ensure it still works correctly with the reduced permissions. Pay attention to forms, data submissions, and any areas that interact with the database.
8. Regular Reviews: Periodically review MySQL user permissions (at least every 6 months) to make sure they are still appropriate and haven’t been accidentally changed.

   Important Considerations

   • Least Privilege Principle: Always grant the minimum necessary permissions.
   • Specific Tables: Instead of granting permissions on all tables in a database (your_database.*), consider granting them only to specific tables that your website needs access to. For example, GRANT SELECT ON your_database.users TO 'your_website_user'@'localhost';
   • Host Restriction: Limit the host from which the user can connect (e.g., 'your_website_user'@'localhost' instead of 'your_website_user'@'%'). The ‘%’ allows connections from any host, which is less secure.