During database audits, we frequently encounter situations where existing MySQL users have excessive privileges. The challenge lies in modifying these privileges without:

• Knowing user passwords
• Disrupting existing connections
• Triggering authentication issues

These are the fundamental commands for privilege management:

-- Grant specific privilege on a database
GRANT SELECT, INSERT ON database.* TO 'username'@'host';

-- Revoke unnecessary privileges
REVOKE DELETE, DROP ON database.* FROM 'username'@'host';

-- Grant all privileges on specific tables
GRANT ALL PRIVILEGES ON database.table_name TO 'username'@'host';

Case 1: Reducing Over-Privileged Users

For a developer who only needs read access:

-- Remove all privileges first
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'dev_user'@'%';

-- Grant minimal required privileges
GRANT SELECT ON production.* TO 'dev_user'@'%';
GRANT EXECUTE ON PROCEDURE production.get_data TO 'dev_user'@'%';

Case 2: Implementing Principle of Least Privilege

For a reporting user who only needs aggregated data:

-- Create restricted views first
CREATE VIEW reporting.sales_summary AS 
SELECT region, SUM(amount) FROM sales.sensitive_data GROUP BY region;

-- Then grant access only to the view
GRANT SELECT ON reporting.* TO 'report_user'@'internal';

Checking Current Privileges

Before making changes, always verify current privileges:

SHOW GRANTS FOR 'username'@'host';

-- For detailed analysis
SELECT * FROM mysql.db WHERE User='username' AND Host='host';

Using Wildcards and Database Patterns

For managing multiple similar databases:

-- Grant to all test databases
GRANT ALL PRIVILEGES ON test%.* TO 'qa_user'@'%';

-- Restrict to specific schema pattern
GRANT SELECT ON client\_%.* TO 'analyst'@'%';

• Always document privilege changes in your change management system
• Implement regular privilege audits with automated scripts
• Consider using MySQL roles (available in MySQL 8.0+) for easier management
• Test privilege changes in staging before production

If privileges don't seem to take effect:

-- Flush privileges to ensure changes are active
FLUSH PRIVILEGES;

-- Check for conflicting privileges
SHOW GRANTS FOR 'username'@'host';

Remember that privilege changes take effect for new connections. Existing connections maintain their current privileges until re-authentication.

When dealing with existing MySQL users, you'll primarily use these SQL commands:

-- Grant specific privileges on a database
GRANT SELECT, INSERT ON database_name.* TO 'username'@'host';

-- Revoke unnecessary privileges
REVOKE DELETE, DROP ON database_name.* FROM 'username'@'host';

-- Apply changes immediately
FLUSH PRIVILEGES;

Before making changes, check existing privileges with:

SHOW GRANTS FOR 'username'@'host';

-- For comprehensive audit across all users:
SELECT * FROM mysql.user;
SELECT * FROM mysql.db;
SELECT * FROM mysql.tables_priv;

-- Case 1: Downgrading admin to read-only
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'dev_user'@'%';
GRANT SELECT ON production_db.* TO 'dev_user'@'%';

-- Case 2: Restricting to specific tables
GRANT SELECT, INSERT ON db_name.table1 TO 'report_user'@'localhost';

-- Case 3: Removing global privileges
REVOKE SUPER, PROCESS ON *.* FROM 'legacy_app'@'192.168.%';

• Always specify the host when modifying privileges
• Use wildcards (%) judiciously
• Document changes in your database change log
• Consider creating roles for common privilege sets (MySQL 8.0+)

If changes don't take effect:

1. Verify you're using FLUSH PRIVILEGES (unless using MySQL 8.0+ with automatic loading)
2. Check for overlapping privilege rules
3. Confirm the user@host combination matches exactly