|NO.Z.00089|——————————|^^ 操作 ^^|——|Linux&MySQL.V04|——|用户授权|取消授权|

一、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@‘%’；

---

 

 

 

 

 

 

 

 

 

---

Walter Savage Landor:strove with none,for none was worth my strife.Nature I loved and, next to Nature, Art:I warm'd both hands before the fire of life.It sinks, and I am ready to depart

                                                                                                                                                   ——W.S.Landor

---