It's crucial to create a dedicated MySQL user with minimal permissions for the MCP server. Never use the root account or a user with full administrative privileges.

-- Connect as root or administrator
CREATE USER 'mcp_user'@'localhost' IDENTIFIED BY 'your_secure_password';

Basic read-only access (recommended for exploration and analysis):

-- Grant SELECT permission only
GRANT SELECT ON your_database.* TO 'mcp_user'@'localhost';

Standard access (allows data modification but not structural changes):

-- Grant data manipulation permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON your_database.* TO 'mcp_user'@'localhost';

Advanced access (includes ability to create temporary tables for complex queries):

-- Grant additional permissions for advanced operations
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES 
ON your_database.* TO 'mcp_user'@'localhost';

FLUSH PRIVILEGES;

1. Network Access

   • Restrict the user to connecting only from localhost if the MCP server runs on the same machine
   • If remote access is needed, specify exact IP addresses rather than using wildcards

2. Query Restrictions

   • Consider using VIEWs to further restrict data access
   • Set appropriate max_queries_per_hour, max_updates_per_hour limits:

   ALTER USER 'mcp_user'@'localhost' 
   WITH MAX_QUERIES_PER_HOUR 1000
   MAX_UPDATES_PER_HOUR 100;

3. Data Access Control

   • Grant access only to specific tables when possible
   • Use column-level permissions for sensitive data:

   GRANT SELECT (public_column1, public_column2) 
   ON your_database.sensitive_table TO 'mcp_user'@'localhost';

4. Regular Auditing

   • Enable MySQL audit logging for the MCP user
   • Regularly review logs for unusual patterns
   • Periodically review and adjust permissions

When setting up the MCP server, use these restricted credentials in your environment:

MYSQL_USER=mcp_user
MYSQL_PASSWORD=your_secure_password
MYSQL_DATABASE=your_database
MYSQL_HOST=localhost

To monitor the MCP user's database usage:

-- Check current connections
SELECT * FROM information_schema.PROCESSLIST 
WHERE user = 'mcp_user';

-- View user privileges
SHOW GRANTS FOR 'mcp_user'@'localhost';

-- Check resource limits
SELECT * FROM mysql.user 
WHERE user = 'mcp_user' AND host = 'localhost';

1. Regular Password Rotation

   • Change the MCP user's password periodically
   • Use strong, randomly generated passwords
   • Update application configurations after password changes

2. Permission Review

   • Regularly audit granted permissions
   • Remove unnecessary privileges
   • Keep permissions as restrictive as possible

3. Access Patterns

   • Monitor query patterns for potential issues
   • Set up alerts for unusual activity
   • Maintain detailed logs of database access

4. Data Protection

   • Consider encrypting sensitive columns
   • Use SSL/TLS for database connections
   • Implement data masking where appropriate