Denying access for specific host to specific database on mysql

Question

We have a production MySQL server with the following grants:

mysql> show grants for the_db;
+------------------------------------------------------------------------------------------------------------+
| Grants for db_user@%                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'db_user'@'%' IDENTIFIED BY PASSWORD '*A236932DB5549260BDC088C4BC2F0C6DB04424D7' |
| GRANT ALL PRIVILEGES ON `xydb`.* TO 'db_user'@'%'                                                      |
| GRANT SELECT ON `xyie-db`.* TO 'db_user'@'%'                                                           |
| GRANT SELECT ON `supportdb`.* TO 'db_user'@'%'                                                         |
| GRANT SELECT ON `xbs`.* TO 'db_user'@'%'                                                               |
+------------------------------------------------------------------------------------------------------------+

Is it possible to block access to these databases for a specific host? We have a new server coming online that will be using other test databases on the same server. I don't want the new server to be able to accidentally do anything on the production databases.

I understand I can use REVOKE access, but I'm not sure if that requires altering the existing GRANTS to be more specific to the hosts that they allow. I don't want to guess as the production dbs here are in constant use and accidentally blocking access for the live host would be bad.

Ideally I need to be able to just say:

BLOCK ACCESS ON 'xydb'.* TO 'db_user'@'192.168.1.4'

Answer 1

I'm not sure if you can do it using REVOKE, but you can definitely do it by inserting an entry into the relevant table in the mysql schema:

INSERT INTO mysql.db (Host,Db) VALUES ('192.0.2.42','xydb');
INSERT INTO mysql.db (Host,Db) VALUES ('host.example.com','xydb');
FLUSH PRIVILEGES;

The first two lines do the heavy lifting of inserting into the DB; it's best to put both the IP and hostname in there, for clarity, while still ensuring that even if reverse lookups aren't working, the IP will still be blocked. The FLUSH PRIVILEGES is required because MySQL doesn't look in the tables every time it needs to know something -- it caches the info in memory. You need to tell MySQL to flush its cache.

Answer 2

If the xydb could have a different mysql password on the production and test DB servers, then you can give the mysql user a "wrong" password on the live server

mysql> SELECT VERSION();
+-------------------------+
| VERSION()               |
+-------------------------+
| 5.1.73-0ubuntu0.10.04.1 |
+-------------------------+

To recreate your situation:

mysql> SHOW GRANTS FOR db_user@`localhost`;
ERROR 1141 (42000): There is no such grant defined for user 'db_user' on host 'localhost'
mysql> SHOW GRANTS FOR db_user@`%`;
ERROR 1141 (42000): There is no such grant defined for user 'db_user' on host '%'

mysql> CREATE DATABASE xydb;
mysql> GRANT USAGE ON *.* TO 'db_user'@'%' IDENTIFIED BY '1234';
mysql> GRANT ALL PRIVILEGES ON `xydb`.* TO 'db_user'@'%';
mysql> SHOW GRANTS FOR db_user@`%`;
+--------------------------------------------------------------------------------------------------------+
| Grants for db_user@%                                                                                   |
+--------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'db_user'@'%' IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' |
| GRANT ALL PRIVILEGES ON `xydb`.* TO 'db_user'@'%'                                                      |
+--------------------------------------------------------------------------------------------------------+

mysql> FLUSH PRIVILEGES;

This results with the user being able to login from localhost

$ mysql -u db_user -p1234 --host localhost xydb -e 'SHOW DATABASES'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| xydb               |
+--------------------+

And then a user is added specifically for the host that will be blacklisted.

mysql> GRANT USAGE ON *.* TO 'db_user'@'localhost' IDENTIFIED BY 'AAAAAA';
mysql> SHOW GRANTS FOR db_user@`localhost`;
+----------------------------------------------------------------------------------------------------------------+
| Grants for db_user@localhost                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'db_user'@'localhost' IDENTIFIED BY PASSWORD '*4F1779C9918AA4ADD1DDB16A274A8D098DDCC0D0' |
+----------------------------------------------------------------------------------------------------------------+

and then the user can no longer login (unless of course he gives the proper password)

mysql -u db_user -p1234 --host localhost xydb -e 'SHOW DATABASES'
ERROR 1045 (28000): Access denied for user 'db_user'@'localhost' (using password: YES)