MySQL Forums
Forum List  »  Replication

Re: How to block connections to Slave DBS
Posted by: Rick James
Date: July 21, 2013 12:58PM

The default installation allows only root@localhost to connect to a mysqld.

If you copied the `mysql` database from the Master when you created the Slaves, then you copied any GRANTs into the Slave. There is no straightforward (and easy and safe) way to remove all the extraneous GRANTs.

You do need to keep two GRANTs on the slave -- one for root for admin purposes, one for replication. The latter should have been created something like
GRANT REPLICATION SLAVE ON *.* TO foo@'master-host-name-or-ip' IDENTIFIED BY 'password'.
Note that only REPLICATION SLAVE is needed.

You can find a list of users via
SELECT user, host FROM mysql.user;
Then you could tediously do
REVOKE USAGE FROM 'name'@'host';
but avoid root and the replication account.

SET SESSION sql_log_bin=0;
TRUNCATE TABLE mysql.db;
would remove the ability for anyone to get to particular tables, if granted via
GRANT ... ON dbname.* ...;
All your users have that kind of GRANT, correct?

This _might_ generate the REVOKEs needed for those with global access:
SELECT CONCAT("REVOKE USAGE ON *.* FROM '", user, "'@'", host, "';\n")
FROM mysql.user
WHERE user NOT IN ('root', '', 'foo')
AND Select_priv = 'Y';

CAVEAT!: I have not tested any of this. Use with caution!

Note:
mysql> SHOW GRANTS FOR ''@'localhost';
+--------------------------------------+
| Grants for @localhost                |
+--------------------------------------+
| GRANT USAGE ON *.* TO ''@'localhost' |
+--------------------------------------+
This is a catchall. It says that others can't do anything. But does not help in your situation. (Note that I left it intact.)

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: How to block connections to Slave DBS
994
July 21, 2013 12:58PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.