mysql的用户登录和权限问题（以mysql5.6为例)

运维实战课程

于 2024-09-23 08:00:00 发布

阅读量913

点赞数 24

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_34953582/article/details/142441162

版权

mysql的用户登录和权限问题（以mysql5.6为例)

如果对运维课程感兴趣，可以在b站上、csdn或微信视频号上搜索我的账号：运维实战课程，可以关注我，学习更多免费的运维实战技术视频

一、mysql用root用户在客户端工具navicat远程登录mysql （ip:192.168.234.129）

1.root用户在navicat客户端的登录mysql(本地登录和远程登录密码设置相同)

刚安装完mysql后的用户状态：

[root@bogon ~]# mysql -uroot -p123

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| test |

+--------------------+

mysql> use mysql

mysql> select Host,User,Password from user;

+-----------+------+-------------------------------------------+

| Host | User | Password |

+-----------+------+-------------------------------------------+

| localhost | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |

| bogon | root | *6F8A7EC1464B6E448787B6353642E7E79041F1F9 |

| 127.0.0.1 | root | *6F8A7EC1464B6E448787B6353642E7E79041F1F9 |

| ::1 | root | *6F8A7EC1464B6E448787B6353642E7E79041F1F9 |

+-----------+------+-------------------------------------------+

此时用navicat客户端不能使用root用户和密码123进行登录mysql数据库,如下报错：

重新给root用户授权，授权密码和登录密码一样（都是123）

mysql> select Host,User,Password from user;

+-----------+------+-------------------------------------------+

| Host | User | Password |

+-----------+------+-------------------------------------------+

| localhost | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |

| bogon | root | *6F8A7EC1464B6E448787B6353642E7E79041F1F9 |

| 127.0.0.1 | root | *6F8A7EC1464B6E448787B6353642E7E79041F1F9 |

| ::1 | root | *6F8A7EC1464B6E448787B6353642E7E79041F1F9 |

+-----------+------+-------------------------------------------+

mysql> grant all on *.* to 'root'@'%' identified by '123'; #重新给root用户授权

mysql> flush privileges;

mysql> select Host,User,Password from user;

+-----------+------+-------------------------------------------+

| Host | User | Password |

+-----------+------+-------------------------------------------+

| localhost | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |

| bogon | root | *6F8A7EC1464B6E448787B6353642E7E79041F1F9 |

| 127.0.0.1 | root | *6F8A7EC1464B6E448787B6353642E7E79041F1F9 |

| ::1 | root | *6F8A7EC1464B6E448787B6353642E7E79041F1F9 |

| % | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |

+-----------+------+-------------------------------------------+

此时用navicat客户端可以使用root用户和密码123进行登录mysql数据库，如下：

2.root用户在navicat客户端的登录mysql(本地登录和远程登录密码设置不相同)

刚安装完mysql后的用户状态：

[root@bogon ~]# mysql -uroot -p123

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| test |

+--------------------+

mysql> use mysql

mysql> select Host,User,Password from user;

+-----------+------+-------------------------------------------+

| Host | User | Password |

+-----------+------+-------------------------------------------+

| localhost | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |

| bogon | root | *6F8A7EC1464B6E448787B6353642E7E79041F1F9 |

| 127.0.0.1 | root | *6F8A7EC1464B6E448787B6353642E7E79041F1F9 |

| ::1 | root | *6F8A7EC1464B6E448787B6353642E7E79041F1F9 |

+-----------+------+-------------------------------------------+

此时用navicat客户端不能使用root用户和密码123进行登录mysql数据库,如下报错：

重新给root用户授权，授权密码和登录密码不同（本地登录密码:123不变，远程登录是:456）

mysql> select Host,User,Password from user;

+-----------+------+-------------------------------------------+

| Host | User | Password |

+-----------+------+-------------------------------------------+

| localhost | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |

| bogon | root | *6F8A7EC1464B6E448787B6353642E7E79041F1F9 |

| 127.0.0.1 | root | *6F8A7EC1464B6E448787B6353642E7E79041F1F9 |

| ::1 | root | *6F8A7EC1464B6E448787B6353642E7E79041F1F9 |

+-----------+------+-------------------------------------------+

mysql> grant all on *.* to 'root'@'%' identified by '456'; #重新给root用户授权

mysql> flush privileges;

mysql> select Host,User,Password from user;

+-----------+------+-------------------------------------------+

| Host | User | Password |

+-----------+------+-------------------------------------------+

| localhost | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |

| bogon | root | *6F8A7EC1464B6E448787B6353642E7E79041F1F9 |

| 127.0.0.1 | root | *6F8A7EC1464B6E448787B6353642E7E79041F1F9 |

| ::1 | root | *6F8A7EC1464B6E448787B6353642E7E79041F1F9 |

| % | root | *531E182E2F72080AB0740FE2F2D689DBE0146E04 |

+-----------+------+-------------------------------------------+

注意：

1)本地登录试用用户问题

[root@bogon ~]# mysql -uroot -p123 #本地使用本地登录密码能登录

mysql> quit

[root@bogon ~]# mysql -uroot -p456 #本地使用远程密码不能登录

Warning: Using a password on the command line interface can be insecure.

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

[root@bogon ~]# mysql -h192.168.234.129 -uroot -p456 #本地作为远程客户端登录，使用远程密码能登录

mysql> quit

2)也可授权其他用户登录数据库（和root授权类似）

二、mysql授权用户并限制权限问题（*.*表示所有库的所有表，也可指定库和表：库.表)

1.mysql创建普通用户，并只给用户授予只读权限（所有库所有表都只能读,不能修改）

刚安装完mysql后的用户状态：

[root@bogon ~]# mysql -uroot -p123

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| test |

+--------------------+

mysql> use mysql

mysql> select Host,User,Password from user;

+-----------+------+-------------------------------------------+

| Host | User | Password |

+-----------+------+-------------------------------------------+

| localhost | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |

| bogon | root | *6F8A7EC1464B6E448787B6353642E7E79041F1F9 |

| 127.0.0.1 | root | *6F8A7EC1464B6E448787B6353642E7E79041F1F9 |

| ::1 | root | *6F8A7EC1464B6E448787B6353642E7E79041F1F9 |

+-----------+------+-------------------------------------------+

mysql> grant all on *.* to 'root'@'%' identified by '123'; #重新给root用户授权,不设置root远程登录也可，直接使用下面的普通用户远程登录即可

mysql> flush privileges;

mysql> select Host,User,Password from user;

+-----------+------+-------------------------------------------+

| Host | User | Password |

+-----------+------+-------------------------------------------+

| localhost | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |

| bogon | root | *6F8A7EC1464B6E448787B6353642E7E79041F1F9 |

| 127.0.0.1 | root | *6F8A7EC1464B6E448787B6353642E7E79041F1F9 |

| ::1 | root | *6F8A7EC1464B6E448787B6353642E7E79041F1F9 |

| % | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |

+-----------+------+-------------------------------------------+

此时用navicat客户端可以使用root用户和密码123进行登录mysql数据库

mysql> create user dbreader identified by '789'; #创建用户,没有任何权限,也可没有这句直接使用下面也可

mysql> grant select on *.* to 'dbreader'@'%' identified by '789'; #给普通用户仅授予查询权限

mysql> flush privileges;

mysql> select Host,User,Password from user;

+-----------+----------+-------------------------------------------+

| Host | User | Password |

+-----------+----------+-------------------------------------------+

| localhost | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |

| bogon | root | *6F8A7EC1464B6E448787B6353642E7E79041F1F9 |

| 127.0.0.1 | root | *6F8A7EC1464B6E448787B6353642E7E79041F1F9 |

| ::1 | root | *6F8A7EC1464B6E448787B6353642E7E79041F1F9 |

| % | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |

| % | dbreader | *846AEC788124A4D732D51692E35E9DE488607F86 |

+-----------+----------+-------------------------------------------+

使用普通用dbreader/789远程登录数据库，查看权限，权限只读，如下：

此后，dbreader用户对数据库中所有库的所有表只有读的权限，不能修改，不能删除（解释一下，不再演示）

2.mysql创建普通用户，并给用户授予读写等所有权限（所有库所有表都有所有权限,但无授权权限）

mysql> grant all on *.* to 'dbuser'@'%' identified by '789'; #给普通用户授予所有权限

3.mysql创建普通用户，并给用户授予读写等所有权限（所有库所有表都有所有权限,且有授权权限）

mysql> grant all on *.* to 'dbuser'@'%' identified by '789' with grant option; #授予所有权限且有授权权限

mysql> flush privileges;

查看用户权限：可以用任何一个授权用户用navicat登录，查看mysql库中的user表里的各用户的权限即可。

如果对运维课程感兴趣，可以在b站上、csdn或微信视频号上搜索我的账号：运维实战课程，可以关注我，学习更多免费的运维实战技术视频

运维实战课程

关注

24
点赞
踩
19

收藏

觉得还不错? 一键收藏
打赏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫