mysql 库和库之间的表授权
机器A :172.17.36.39
机器B :172.17.36.42
在A36库上创建db1 database，创建用户shen可以让B42库访问，创建T1表
mysql> create database db1;
Query OK, 1 row affected (0.05 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| lib64 |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
7 rows in set (0.01 sec)
mysql> use db1;
Database changed
mysql> create table t1 (id int ,name varchar(10));
Query OK, 0 rows affected (0.14 sec)
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1 |
+---------------+
mysql> insert into t1 values ('2');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t1 values ('3');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t1 values ('4');
Query OK, 1 row affected (0.01 sec)
mysql> commit;
create user 'shen'@'172.17.36.42' identified by '123456';
grant select (id) on db1.t1 to 'shen'@'172.17.36.42';
在B库上登录
mysql -h 172.17.36.39 -ushen -p
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
+--------------------+
2 rows in set (0.01 sec)
mysql> use db1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1 |
+---------------+
1 row in set (0.01 sec)
mysql> select * from t1;
ERROR 1142 (42000): SELECT command denied to user 'shen'@'172.17.36.42' for table 't1'
mysql> select id from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)
在A上收回权限
mysql> revoke select on db1.* from 'shen'@'172.17.36.42';
Query OK, 0 rows affected (0.03 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30166976/viewspace-2688747/ ，如需转载，请注明出处，否则将追究法律责任。
