主数据库:
mysql> select Host,user from mysql.user;
+----------------+---------------+
| Host | user |
+----------------+---------------+
| % | query_all |
| % | query_dev |
| % | root |
| % | zjzc_dba |
| % | zjzc_limit_cn |
| 10.171.243.55 | quartz_app |
| 10.171.243.55 | zjzc_app |
| 10.252.148.164 | quartz_app |
| 10.252.148.164 | zjzc_app |
| 127.0.0.1 | root |
| ::1 | root |
| jrhdpt01 | root |
| localhost | root |
+----------------+---------------+
13 rows in set (0.00 sec)
可以看到zjzc_app 和quartz_app 本地无法登陆:
mysql> show grants for 'zjzc_app'@'10.252.148.164';
+----------------------------------------------------------------------------------------------------------------------+
| Grants for zjzc_app@10.252.148.164 |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zjzc_app'@'10.252.148.164' IDENTIFIED BY PASSWORD '*6AC5E71C51484F1CC759EA9E254C573E67F23AB5' |
| GRANT ALL PRIVILEGES ON `zjzc`.* TO 'zjzc_app'@'10.252.148.164' |
+----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
GRANT USAGE ON *.* TO 'zjzc_app'@'10.252.148.164' IDENTIFIED BY PASSWORD 'zjzc,uDagJ.515';
从数据库 内网地址 10.175.204.46 :
mysql> select Host,user from mysql.user;
+-----------+------+
| Host | user |
+-----------+------+
| 127.0.0.1 | root |
| ::1 | root |
| jrhdpt02 | root |
| localhost | root |
+-----------+------+
4 rows in set (0.00 sec)
mysql> GRANT USAGE ON *.* TO 'zjzc_app'@'10.252.148.164' IDENTIFIED BY 'zjzc,uDagJ.515';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'zjzc_app'@'10.252.148.164';
+----------------------------------------------------------------------------------------------------------------------+
| Grants for zjzc_app@10.252.148.164 |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zjzc_app'@'10.252.148.164' IDENTIFIED BY PASSWORD '*6AC5E71C51484F1CC759EA9E254C573E67F23AB5' |
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
USAGE 只有连接的权限:
---给zjzc_app在zjzc数据库上的所有权限
GRANT ALL PRIVILEGES ON 'zjzc'.* TO 'zjzc_app'@'10.252.148.164'
mysql> GRANT ALL PRIVILEGES ON zjzc.* TO 'zjzc_app'@'10.252.148.164' ;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'zjzc_app'@'10.252.148.164';
+----------------------------------------------------------------------------------------------------------------------+
| Grants for zjzc_app@10.252.148.164 |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zjzc_app'@'10.252.148.164' IDENTIFIED BY PASSWORD '*6AC5E71C51484F1CC759EA9E254C573E67F23AB5' |
| GRANT ALL PRIVILEGES ON `zjzc`.* TO 'zjzc_app'@'10.252.148.164' |
+----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)