Wednesday, June 22, 2011

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?

3 comments:

  1. Just a nitpick.
    There are several actions that result in a change to mysql.*, and these changes don't go to the binary log as inserts, updates, or deletes.
    Creating or modifying an event (for the event scheduler) or a stored routine will affect mysql.event and mysql.proc. Filtering off the mysql database has no effect on these operations.

    ReplyDelete
  2. The difference on the slave occurred because changes were made directly on the slave -- this makes sense; I don't see it as directly relates to issues with GRANT.

    The cure should be setting the read_only flag on the slave, and verifying the user does not have the SUPER privilege.

    ReplyDelete
  3. Admonished is forearmed! So it is with payday advances. In the event that you realize what this sort of loan will cost you, you will be in a superior position to measure the advantages and disadvantages of settling on it. Along these lines you will likewise know precisely what sum you have to pay back to the payday credit organizations. Payday Loans Chicago

    ReplyDelete