一、Mysql 用户授权
### --- 授予用户全部权限
~~~ 张三是没有对atyanqi授权你库的权限
[root@server22 ~]# mysql -uzhangsan -p123123 -h 20.20.20.21
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
2 rows in set (0.00 sec)
### --- 给已存在用户授权
Mysql>select user from mysql.user;
### --- 使用root登录权限修改用户授权
mysql> grant all on atyanqi.* to zhangsan@'%';
Query OK, 0 rows affected (0.00 sec)
### --- 在20.20.20.22客户端张三用户查看授权结果
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| atyanqi |
| test |
+--------------------+
3 rows in set (0.00 sec)
### --- 创建用户并授权
mysql> grant all on atyanqi.* to lisi@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
[root@server22 ~]# mysql -ulisi -p123456 -h 20.20.20.21
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| atyanqi |
| test |
+--------------------+
3 rows in set (0.00 sec)
二、取消用户授权
### --- 取消 abc 用户的删除库、表、表中数据的权限
~~~ 查看指定用户的授权
mysql> show grants for lisi@'%';
+-----------------------------------------------------------------------------------------------------+
| Grants for lisi@% |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lisi'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT ALL PRIVILEGES ON `atyanqi`.* TO 'lisi'@'%' |
+-----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
### --- 取消删除权限(登录 atyanqi 测试)
mysql> revoke drop,delete on atyanqi.* from lisi@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for lisi@'%';
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for lisi@% |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lisi'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT SELECT, INSERT, UPDATE, CREATE, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `atyanqi`.* TO 'lisi'@'%' |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
Mysql>show grants for atyanqi@‘%’;