MySQL 数据库赋权

1、进入数据库，查看数据库账户

# 进入数据库
mysql –u root –p
---> 输入密码...

# 使用 mysql 库
use mysql; 

# 展示 mysql 库中所有表
show tables; 

# 查看数据库中 用户地址 和 用户
select host,user from user;

2、新增用户

insert into user (Host,User,Password)values('localhost','zhengying',password('123456'));

# 查询验证用户新增成功
select host,user from user;

3、查看用户权限

# 查看 MySQL 数据库中所有用户
mysql> select distinct concat('User: ''',user,'''@''',host,''';') as query from mysql.user;

+---------------------------------------+
| query                                 |
+---------------------------------------+
| User: 'root'@'127.0.0.1';             |
| User: ''@'localhost';                 |
| User: 'evan'@'localhost';             |
| User: 'root'@'localhost';             |
| User: 'zhengying'@'localhost';        |
| User: ''@'localhost.localdomain';     |
| User: 'root'@'localhost.localdomain'; |
+---------------------------------------+

# 查看 MySQL 数据库中 zhengying 用户的权限
mysql> show grants for 'zhengying'@'localhost';
# error 报错提示 zhengying 用户没有权限
ERROR 1141 (42000): There is no such grant defined for user 'zhengying' on host 'localhost'

4、对用户赋权

• 当 权限1，权限2，权限3，权限n 被 all privileges 或者 all 代替，表示赋予用户全部权限
• 当 数据库名称.表名称 被 *.* 代替，表示赋予用户操作服务器上所有数据库所有表的权限
• 用户地址可以是 localhost，也可以是 IP 地址，机器名字，域名，还可以用 "%" 表示从任何地址连接
• 连接口令 不能为空，否则创建失败

mysql> grant 权限1,权限2,…权限n on 数据库名称.表名称 to "用户名"@"用户地址" identified by "连接口令";

# 给用户赋所有权限
mysql> grant all privileges on *.* to "zhengying"@"%" identified by "123456";

# 刷新权限
mysql> flush privileges;  

mysql> grant select,insert,update,delete,create,drop on user.t_user to "zhengying"@"localhost" identified by "123456";
# 给本机用户 zhengying 分配可对数据库 user 的 t_user 表进行select,insert,update,delete,create,drop 等操作的权限，并设定口令为 123456

# 刷新权限
mysql> flush privileges;

mysql> grant all privileges on user.* to "zhengying"@"localhost" identified by "123456";
# 给本机用户 zhengying 分配可对数据库 user 所有表进行所有操作的权限，并设定口令为 123456

# 刷新权限
mysql> flush privileges;

# 查看用户权限
mysql> show grants for 'zhengying'@'localhost';

+-------------------------------------------------------------------------------------------------------------------+
| Grants for zhengying@localhost                                                                                    |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON `user`.* TO 'zhengying'@'localhost'                                                       |
+-------------------------------------------------------------------------------------------------------------------+

5、取消用户权限

• revoke select on *.* from 'evan'@'localhost';　　取消用户 evan 的 select 权限
• revoke all on *.* from 'evan'@'localhost';　　取消用户 evan 的所有权限

mysql> show grants for 'evan'@'localhost';
+--------------------------------------------------------------------------------------------------------------+
| Grants for evan@localhost                                                                                    |
+--------------------------------------------------------------------------------------------------------------+
| GRANT SELECT ON *.* TO 'evan'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+--------------------------------------------------------------------------------------------------------------+

# 取消用户 evan 的 select 权限
mysql> revoke select on *.* from 'evan'@'localhost';

mysql> flush privileges;

mysql> show grants for 'evan'@'localhost';
+-------------------------------------------------------------------------------------------------------------+
| Grants for evan@localhost                                                                                   |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'evan'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+-------------------------------------------------------------------------------------------------------------+