Best Practices for Granting and Revoking MySQL User Privileges

MySQL privileges decide what your apps, tools, and admins can do after they connect. A single account with broad access can turn a small bug or leaked password into full database exposure.

Use GRANT, REVOKE, and SHOW GRANTS with the principle of least privilege: each account should have only the permissions it needs, from only the hosts it uses.

Understanding MySQL Privileges

Before diving into GRANT and REVOKE, it's crucial to understand the different scopes and types of privileges available in MySQL. Privileges define what actions a user can perform and on which database objects.

MySQL privileges can be categorized by their scope:

• Global Privileges (*.*): Apply to all databases and tables on the MySQL server. Examples include SUPER, PROCESS, RELOAD, CREATE USER.
• Database Privileges (database_name.*): Apply to all tables and objects within a specific database. Examples include SELECT, INSERT, UPDATE, DELETE, CREATE, DROP.
• Table Privileges (database_name.table_name): Apply to all columns within a specific table. Examples include SELECT, INSERT, UPDATE, DELETE, ALTER.
• Column Privileges (database_name.table_name.column_name): Apply to specific columns within a table. This is less common but useful for highly granular control.
• Routine Privileges (database_name.routine_name): Apply to stored procedures and functions, controlling EXECUTE and ALTER ROUTINE.
• Proxy Privileges: Allow one user to act as another, useful for applications that manage user identities.

Some common specific privileges include:

• SELECT: Read data from tables.
• INSERT: Add new rows to tables.
• UPDATE: Modify existing rows in tables.
• DELETE: Remove rows from tables.
• CREATE: Create databases, tables, or indexes.
• DROP: Delete databases, tables, or indexes.
• ALTER: Modify table structures.
• INDEX: Create or drop indexes.
• REFERENCES: Establish foreign key constraints.
• CREATE VIEW, SHOW VIEW: Manage views.
• CREATE ROUTINE, ALTER ROUTINE, EXECUTE: Manage and execute stored procedures and functions.
• FILE: Read or write files on the server host (highly powerful, use with extreme caution).
• GRANT OPTION: Allows a user to grant their own privileges to other users. This is a very powerful privilege and should be granted sparingly.

The GRANT Command: Granting Privileges Securely

The GRANT command is used to assign privileges to a MySQL user. When granting privileges, it's crucial to consider the principle of least privilege – only grant what is absolutely necessary.

Basic Syntax

The general syntax for the GRANT command is:

GRANT privileges ON object TO 'user'@'host' [WITH GRANT OPTION];

• privileges: A comma-separated list of privileges (e.g., SELECT, INSERT).
• object: Specifies the scope (e.g., *.* for global, database_name.*, database_name.table_name).
• 'user'@'host': The user account, including the username and the host from which they can connect. The host can be an IP address, hostname, or a wildcard (% for any host, localhost for local connections).
• WITH GRANT OPTION: (Optional) Allows the user to grant the specified privileges to other users.

Create or change passwords with CREATE USER and ALTER USER. Older MySQL releases allowed GRANT ... IDENTIFIED BY, but modern MySQL syntax keeps account creation separate.

Practical Examples

Let's walk through some common scenarios.

1. Creating a New User and Granting Global Read-Only Access (Highly Discouraged)

   CREATE USER 'global_reader'@'localhost' IDENTIFIED BY 'StrongPass123!';
   GRANT SELECT ON *.* TO 'global_reader'@'localhost';
   

   Warning: Granting SELECT on *.* gives access to all databases and tables. This is generally too broad for application users and should be avoided unless absolutely necessary for specific administrative tasks.

2. Granting Full Access to a Specific Database for an Application User

   A common scenario for an application user needing to manage data within its own database.

   CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'AppPassSecure!';
   GRANT SELECT, INSERT, UPDATE, DELETE ON `myapp_db`.* TO 'app_user'@'localhost';
   

   Here, app_user can perform basic CRUD operations only within the myapp_db database, but not create new tables or modify schema.

3. Granting Read-Only Access to a Specific Table

   For a reporting tool that only needs to read from a particular table.

   CREATE USER 'report_tool'@'%' IDENTIFIED BY 'ReportSecret!';
   GRANT SELECT ON `sales_db`.`orders` TO 'report_tool'@'%';
   

   The '%' host allows the report_tool to connect from any host, but only with SELECT access on the orders table in sales_db.

4. Granting GRANT OPTION (Use with Extreme Caution)

   If an administrator needs to delegate privilege management for a specific database.

   CREATE USER 'db_admin'@'localhost' IDENTIFIED BY 'AdminPass#456';
   GRANT ALL PRIVILEGES ON `inventory_db`.* TO 'db_admin'@'localhost' WITH GRANT OPTION;
   

   The db_admin can now grant any privileges on inventory_db to other users. This is a powerful privilege that bypasses central control and should be used only when unavoidable.

Tips for Granting Privileges

• Principle of Least Privilege: Always grant the minimum set of privileges required for a user or application to function. Avoid ALL PRIVILEGES unless for a dedicated database administrator account.
• Specific Hosts: Restrict user connections to specific IP addresses or hostnames ('user'@'192.168.1.10' or 'user'@'appserver.example.com') instead of '%' where possible.
• Separate Users: Create separate user accounts for different applications or services, even if they access the same database. This isolates potential security breaches.
• No root for Applications: Never use the root user account for your applications. Create dedicated, least-privileged users.

The REVOKE Command: Revoking Privileges Effectively

The REVOKE command is used to remove privileges from a MySQL user. It's just as important as GRANT for maintaining a secure database environment, especially when roles change or applications are decommissioned.

Basic Syntax

The general syntax for the REVOKE command is:

REVOKE privileges ON object FROM 'user'@'host';

• privileges: A comma-separated list of privileges to revoke.
• object: The scope from which to revoke (must match the scope where privileges were granted).
• 'user'@'host': The user account from which to revoke privileges.

Practical Examples

1. Revoking DELETE Privilege from an Application User

   If an application no longer needs to delete data, or if you want to downgrade its permissions.

   REVOKE DELETE ON `myapp_db`.* FROM 'app_user'@'localhost';
   

   Now, app_user can still SELECT, INSERT, and UPDATE, but cannot DELETE within myapp_db.

2. Revoking GRANT OPTION from a Delegated Admin

   If db_admin no longer needs to manage other users' permissions for inventory_db.

   REVOKE GRANT OPTION ON `inventory_db`.* FROM 'db_admin'@'localhost';
   

   Note: To revoke GRANT OPTION, you must explicitly specify GRANT OPTION in the REVOKE statement.

3. Revoking All Privileges on a Specific Database

   To remove all privileges a user has on a particular database.

   REVOKE ALL PRIVILEGES ON `old_db`.* FROM 'old_app'@'%';
   

   Warning: REVOKE ALL PRIVILEGES on *.* will revoke all global privileges, which can include SUPER, CREATE USER, etc. Be careful when using this scope globally.

4. Dropping a User Account

   When a user or application is no longer needed, it's best to remove the user entirely.

   DROP USER 'report_tool'@'%';
   

   This command removes the user and all their associated privileges.

Tips for Revoking Privileges

• Match Scope: When revoking, ensure the object scope (*.*, database_name.*, etc.) precisely matches how the privilege was originally granted. If you granted SELECT on database_name.*, you must revoke it from database_name.*, not database_name.table_name.
• Verify: Always use SHOW GRANTS FOR 'user'@'host'; after granting or revoking privileges to confirm the changes.
• Skip unnecessary flushes: FLUSH PRIVILEGES is not needed after GRANT, REVOKE, CREATE USER, or DROP USER; MySQL applies those statements immediately.
• Consider cascading effects: If a user with GRANT OPTION has granted privileges to others, revoking their GRANT OPTION does not automatically revoke the privileges they granted. You would need to revoke those separately.

Best Practices for MySQL User Privilege Management

Implementing a robust privilege management strategy is crucial for database security.

1. Principle of Least Privilege (PoLP)

This is the golden rule. Grant only the absolute minimum privileges required for a user or application to perform its intended function. For example:

• A reporting tool needs SELECT.
• A web application typically needs SELECT, INSERT, UPDATE, DELETE.
• An ETL process might need INSERT, UPDATE, DELETE, CREATE TABLE, DROP TABLE (but only on specific staging schemas).

2. Dedicated User Accounts

• Avoid Shared Accounts: Each application, service, or administrative user should have its own unique MySQL user account. This aids in auditing and tracking activity.
• No root for Applications: Never configure your applications to connect as the root user. The root user has unrestricted access and should only be used for critical administrative tasks by human administrators.

3. Strong Passwords and Password Rotation

• Enforce strong, unique passwords for all MySQL user accounts. Utilize MySQL's password validation plugins if available.
• Implement a regular password rotation policy, especially for highly privileged accounts.

4. Host Restrictions

• Limit user connections to specific IP addresses or hostnames whenever possible. Replace '%' with localhost, an application server's IP, or a network subnet ('user'@'192.168.1.%'). This prevents unauthorized access attempts from unknown locations.

5. Regular Audits and Reviews

• Periodically review all user accounts and their associated privileges. Remove any obsolete accounts or unnecessary privileges.
• Use SHOW GRANTS FOR 'user'@'host'; to inspect permissions.
• Consider automated tools for auditing large environments.

6. Document Permissions

• Maintain clear documentation of your database users, their roles, and the privileges granted to each. This helps in maintaining consistency and facilitates security audits.

7. Separate Development, Staging, and Production Environments

• Never use production credentials in development or staging environments. Each environment should have its own set of distinct users and privileges.

8. Avoid GRANT OPTION Unless Absolutely Necessary

• Granting WITH GRANT OPTION delegates privilege management to that user, which can bypass central security policies. Reserve this for highly trusted administrative users only and on the most restrictive scope possible.

Viewing Current Privileges

To verify the privileges assigned to a user, use the SHOW GRANTS command:

SHOW GRANTS FOR 'username'@'host';

Example:

SHOW GRANTS FOR 'app_user'@'localhost';

Output might look like:

+-------------------------------------------------------------+
| Grants for app_user@localhost                               |
+-------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'app_user'@'localhost'                |
| GRANT SELECT, INSERT, UPDATE ON `myapp_db`.* TO 'app_user'@'localhost' |
+-------------------------------------------------------------+

The GRANT USAGE ON *.* line indicates that the user has no global privileges, only the ability to connect.

Takeaway

Keep MySQL privilege management narrow and reviewable. Create dedicated accounts, restrict hosts, grant only the needed actions on the smallest useful scope, and check every change with SHOW GRANTS. When an app or person no longer needs access, revoke the privilege or drop the account instead of leaving stale credentials behind.