MySQL数据库系列(4)—–用户管理语句

在了解mysql数据库的用户管理语句之前,先来了解一下mysql的用户账号格式,如下:

username@host

上述格式表示,username对应的用户能够通过哪个host登录mysql.

host: 此mysql用户能够通过哪些客户端主机IP登录当前服务器上的mysql服务,对于mysql来说,host可以是主机名,也可以是ip地址,但是,mysql认为主机名和IP地址属于不同的主机;

我们也可以使用通配符,描述客户端所在的host,常用通配符如下:

_: 表示任意单个字符

%: 表示任意长度的任意字符(单边,即一个%只表示左边通配或者右边通配)

使用通配符匹配主机的示例如下:

root@'10.1.%.%'

上述配置表示root用户可以通过10.1这个网段远程登录。

查询用户

​ 切换到mysql库，查询user表，得到当前库中的用户名和其能够远程登录时所在的host
对应的报错:远程连接MYSQL提示Host is not allowed to connect to this MySQL server

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
## 注意mysql5.7.5即以前版本的user表中密码字段为password,而mysql5.7中修改为authentication_string
mysql> select user,host,authentication_string from user;
+--------+-----------+-------------------------------------------+
| user   | host      | authentication_string                                  |
+--------+-----------+-------------------------------------------+
| root   | localhost | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
| root   | %         | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
| root   | ::1       | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
| zabbix | %         | *DEEF4D7D88CD046ECA02A80393B7780A63E7E789 |
+--------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)
# % 是通配符  表示所有host

创建用户

方法一: 使用create user命令创建mysql用户,示例如下,示例中分别创建了两个用户,

• 创建liupenggc用户时没有设定密码.
• 创建liupeng用户时设定了密码.

mysql> create user 'liupenggc'@'www.liupeng.mobi';
Query OK, 0 rows affected(0.00 sec)

mysql> create user 'liupeng'@'www.liupeng.mobi' identified by '123123';
Query OK, 0 rows affected (0.00 sec)

方法二: 直接在mysql库的user表中插入一条记录,这种方法使用较少,而且这种方法在设置了严格的sql-mode以后.

无法使用.

如下示例直接在mysql.user表中插入了一条记录,添加了一个liupeng用户,密码为123123,liupeng用户可以通过192.168.%.%网段登录mysql.

mysql> insert into mysql.user(user,host,authentication_string) values ('liupeng','192.168.%.%',password('123123'));

注意:如果当前mysql数据库的sql-mode设置为了严格检查(如TRADITIONAL(traditional)),则无法使用上述方法插入数据,如果sql-mode已经设置为严格检查,使用上述语句时可能会出现类似如下错误

EOORO 1364(HY000):Field 'ssl_cipher' doesn't have a default value;

方法三: 在授权数据库的同时,如果对应的用户不存在,那么数据库将会自动创建对应的用户,比如,管理员想要liupeng用户可以访问liupengDB数据库,但是目前liupeng用户并不存在,当我们使用类似如下语句授权时,

‘liupeng@127.0.0.1’用户将会自动被创建

grant all on liupengDB.* to liupeng@127.0.0.1 identified by '123123';

删除用户

​ 我们通常使用drop命令删除用户或者直接删除mysql.user表中对应用户的记录.

这两种方法都能够删除用户,但是drop删除的更加彻底,我们一个一个来说.

删除用户方法一:

​ 使用delete语句删除mysql.user表中的用户对应的记录,但是使用此方法会有相关信息残留,示例如下:

mysql> delete from mysql.user where user='liupeng' and host='localhost';
Query OK, 1 row affected(0.00 sec)

上述中,使用了delete语句删除了user表中liupeng@localhost用户对应的记录,也就相当于删除了’liupeng@localhost’用户,但是如果某些数据库的权限已经授权给了”liupeng@localhost”用户,那么,在mysql.db表中,”liupeng.localhost”用户对应的权限将不会被删除,当管理员想要再次创建了”liupeng@localhost”用户时,则会出现问题,如果管理员想要使用create user命令再次创建”liupeng@localhost”,可能会出现如下错误:

mysql> create user "liupeng@localhost";
ERROR 1396(HY000): Operation CREATE USER failed for "liupeng@localhost"

出现上述错误的原因有可能就是之前使用delete语句删除过同名账户,此时,有两种方法可以解决上述问题.

​ 第一种:

​ 我们可以删除mysql.db表中原”liupeng@localhost”用户残留的权限数据后,使用flush privileges命令刷新,然后再次使用上述create user 命令创建新的”liupeng@localhost”用户即可,其实就是将原”liupeng@localhost”用户残留的数据删除干净(相当于使用drop user命令删除了用户).

​ 第二种:

​ 使用drop user命令删除对应用户,使用此方法将会同时删除mysql.user表与mysql.db表中的数据,也就是说,用户与用户对应的所有权限将被删除.

mysql> drop user liupeng@localhost
Query OK, 0 rows affected (0.00 sec)

使用drop user命令不会出现使用delete语句删除用户时的情况,但是如果为了保留用户的权限,也可以选择delete删除对应的记录.

重命名用户(账户)

rename user OldName to NewName;

重命名后的用户的密码以及权限不会发生改变,只有用户名会发生改变.

mysql> select user,host, from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| liupenggc        | %         |
| debian-sys-maint | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| phpmyadmin       | localhost |
| root             | localhost |
+------------------+-----------+
6 rows in set (0.07 sec)

mysql> rename user liupenggc@localhost to liupenggc@localhost;