0

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'
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.

  • Is it safe to just put stuff directly into that table? – John Hunt Aug 12 '15 at 7:57
  • Yep. That's all GRANT/REVOKE do anyway. – womble Aug 12 '15 at 7:57
  • It worked perfectly, thanks. It'd be handy to know if/when the test server is trying to access the database - I had a quick look in the log but this isn't showing... any ideas where to look? – John Hunt Aug 12 '15 at 8:23
  • Best to ask that as a separate question. In no small part because, off the top of my head, I don't know. (grin) – womble Aug 12 '15 at 8:25
1

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.