MySQL privileges and replication

This is a response on MySQL security: inconsistencies and Less known facts about MySQL user grants.

As far as I know the privilege to grant PROXY privileges is also not very well understood. I blogged about that some time ago.

In addion to the already highlighted issues with GRANT replication and grants can very well create an unwanted situation:

master> SHOW GRANTS FOR 'user'@'host'\G
*************************** 1. row ***************************
Grants for user@host: GRANT USAGE ON *.* TO 'user'@'host' IDENTIFIED BY PASSWORD '*4994A78AFED55B0F529C11C436F85458C1F8D4C2'
*************************** 2. row ***************************
Grants for user@host: GRANT SELECT, INSERT, UPDATE, DELETE ON `somedb`.* TO 'user'@'host'
2 rows in set (0.00 sec)

master> GRANT SELECT,INSERT,UPDATE,DELETE ON anotherdb.* TO 'user'@'host';
Query OK, 0 rows affected (0.00 sec)

master> SHOW GRANTS FOR 'user'@'host'\G
*************************** 1. row ***************************
Grants for user@host: GRANT USAGE ON *.* TO 'user'@'host' IDENTIFIED BY PASSWORD '*4994A78AFED55B0F529C11C436F85458C1F8D4C2'
*************************** 2. row ***************************
Grants for user@host: GRANT SELECT, INSERT, UPDATE, DELETE ON `somedb`.* TO 'user'@'host'
*************************** 3. row ***************************
Grants for user@host: GRANT SELECT, INSERT, UPDATE, DELETE ON `anotherdb`.* TO 'user'@'host'
3 rows in set (0.00 sec)

And on the slave:

slave> SHOW GRANTS FOR 'user'@'host'\G
*************************** 1. row ***************************
Grants for user@host: GRANT USAGE ON *.* TO 'user'@'host'
*************************** 2. row ***************************
Grants for user@host: GRANT SELECT, INSERT, UPDATE, DELETE ON `anotherdb`.* TO 'user'@'host'
2 rows in set (0.00 sec)

This could have happened if the user was dropped on the slave to prevent it from inserting on a ro-slave. The cure for this issue is setting sql_mode to NO_AUTO_CREATE_USER.

Another issue is mysql-proxy. MySQL proxy can be used for simple read/write splitting, failover and a lot more. But it could also make your database less secure:

• root is limited to localhost to prevent remote logins
• mysql proxy is installed on the database machine
• if you connect from a remote machine to mysql-proxy this will be proxied to mysql and the host will be localhost.

And the questions for the readers are:

• Do you allow access on your read-only slaves?
• Do you replicate mysql.* or not?