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','wordpress',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: 'wordpress'@'localhost';        |
| User: ''@'localhost.localdomain';     |
| User: 'root'@'localhost.localdomain'; |
+---------------------------------------+

# 查看 MySQL 数据库中 wordpress 用户的权限
mysql> show grants for 'wordpress'@'localhost';
# error 报错提示 wordpress 用户没有权限
ERROR 1141 (42000): There is no such grant defined for user 'wordpress' 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 "wordpress"@"%" identified by "123456";

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

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

# 刷新权限
mysql> flush privileges;

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

+-------------------------------------------------------------------------------------------------------------------+
| Grants for wordpress@localhost                                                                                    |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON `user`.* TO 'wordpress'@'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' |
+-------------------------------------------------------------------------------------------------------------+
版权声明:免费收录优质站点 发表于 3月 28, 2022 1:01 上午。
转载请注明:MySQL 数据库赋权 | 九欧导航

相关文章

暂无评论

暂无评论...