MySQL User Permissions: A Practical Guide

In the world of database management, controlling user access is crucial for maintaining data integrity and security. This blog post will walk you through a real-world scenario of setting up MySQL user permissions, including the process, potential pitfalls, and debugging steps.

The Scenario

Imagine you're a database administrator for a company that has several databases:

• Original databases: products, customers, orders, analytics
• Copy databases: products_copy, customers_copy, orders_copy, analytics_copy

Your task is to set up permissions for a user named 'analyst' with the following requirements:

1. The analyst should be able to view databases and tables from the original databases.
2. The analyst should not be able to edit the original databases.
3. The analyst should not be able to create new databases.
4. The analyst should have full access (view, edit, delete, create tables) to the '_copy' databases.

Let's dive into how we can achieve this using MySQL's GRANT and REVOKE statements.

Step 1: Initial Setup

First, we need to connect to the MySQL server with an administrative account:

mysql -h hostname -P port -u admin -p

Replace 'hostname', 'port', and 'admin' with your actual server details and admin username.

Step 2: Create the User

If the user doesn't already exist, we need to create it:

CREATE USER 'analyst'@'%' IDENTIFIED BY 'password';

Replace 'password' with a strong, secure password.

Step 3: Grant Necessary Permissions

Now, let's grant the required permissions:

-- Grant SELECT on original databases
GRANT SELECT ON products.* TO 'analyst'@'%';
GRANT SELECT ON customers.* TO 'analyst'@'%';
GRANT SELECT ON orders.* TO 'analyst'@'%';
GRANT SELECT ON analytics.* TO 'analyst'@'%';

-- Grant all privileges on copy databases
GRANT ALL PRIVILEGES ON products_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON customers_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON orders_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON analytics_copy.* TO 'analyst'@'%';

-- Grant global privileges
GRANT PROCESS, SHOW DATABASES ON *.* TO 'analyst'@'%';

-- Apply the changes
FLUSH PRIVILEGES;

Step 4: Verify Permissions

After setting up the permissions, it's crucial to verify them:

SHOW GRANTS FOR 'analyst'@'%';

Debugging and Troubleshooting

Issue 1: Too Many Privileges

In our scenario, we initially encountered an issue where 'analyst' had too many privileges:

mysql> SHOW GRANTS FOR 'analyst'@'%';
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for analyst@%                                                                                                                                                                                                                                                                                                              |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO "analyst"@"%" WITH GRANT OPTION |
| GRANT REPLICATION_APPLIER,ROLE_ADMIN ON *.* TO "analyst"@"%" WITH GRANT OPTION                                                                                                                                                                                                                                                    |
...
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Solution:

To fix this, we revoked all privileges and then granted only the necessary ones:

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'analyst'@'%';
GRANT PROCESS, SHOW DATABASES ON *.* TO 'analyst'@'%';
-- Then re-grant the specific permissions as shown in Step 3

Issue 2: Missing Permissions on Copy Databases

After fixing the excessive privileges, we noticed that the permissions for the copy databases were missing:

mysql> SHOW GRANTS FOR 'analyst'@'%';
+-----------------------------------------------------+
| Grants for analyst@%                                |
+-----------------------------------------------------+
| GRANT PROCESS, SHOW DATABASES ON *.* TO "analyst"@"%"|
| GRANT SELECT ON "products".* TO "analyst"@"%"       |
| GRANT SELECT ON "customers".* TO "analyst"@"%"      |
| GRANT SELECT ON "orders".* TO "analyst"@"%"         |
| GRANT SELECT ON "analytics".* TO "analyst"@"%"      |
+-----------------------------------------------------+

Solution:

We added the missing grants for the copy databases:

GRANT ALL PRIVILEGES ON products_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON customers_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON orders_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON analytics_copy.* TO 'analyst'@'%';
FLUSH PRIVILEGES;

Final Result

After applying all these changes and fixes, the final grants should look like this:

mysql> SHOW GRANTS FOR 'analyst'@'%';
+-----------------------------------------------------+
| Grants for analyst@%                                |
+-----------------------------------------------------+
| GRANT PROCESS, SHOW DATABASES ON *.* TO "analyst"@"%"|
| GRANT SELECT ON "products".* TO "analyst"@"%"       |
| GRANT SELECT ON "customers".* TO "analyst"@"%"      |
| GRANT SELECT ON "orders".* TO "analyst"@"%"         |
| GRANT SELECT ON "analytics".* TO "analyst"@"%"      |
| GRANT ALL PRIVILEGES ON "products_copy".* TO "analyst"@"%"|
| GRANT ALL PRIVILEGES ON "customers_copy".* TO "analyst"@"%"|
| GRANT ALL PRIVILEGES ON "orders_copy".* TO "analyst"@"%"|
| GRANT ALL PRIVILEGES ON "analytics_copy".* TO "analyst"@"%"|
+-----------------------------------------------------+

Conclusion

Setting up proper MySQL user permissions can be tricky, but it's a crucial aspect of database management. By carefully using GRANT and REVOKE statements, and always verifying the results, you can create a secure and functional environment for your users.

Remember these key points:

1. Always start with the principle of least privilege.
2. Use SHOW GRANTS to verify permissions after making changes.
3. Be careful with global privileges (ON .).
4. Don't forget to FLUSH PRIVILEGES after making changes.

By following these guidelines and the steps outlined in this post, you'll be well-equipped to manage MySQL user permissions effectively.