【数据库系列教程】MySQL用户管理和用户权限

一、用户管理

1.用户名格式

用户名@客户端地址
客户端地址：
- IP地址 admin@192.168.1.1
- 主机名 admin@node01.linux.com
- 网段 admin@192.168.1.%
- 所有主机 admin@%
- 本机 admin@localhost

2.存储用户的表 mysql.user

user 用户名
host 登录地址
(5.7版本以后)authentication_string 密码
(5.6版本之前)password 密码

mysql> select user, host, authentication_string from mysql.user;
+-----------+-----------+-------------------------------------------+
| user      | host      | authentication_string                     |
+-----------+-----------+-------------------------------------------+
| root      | localhost | *F00AFD2CA41EF081905BE7FC7051A0D3D53B49AD |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+-----------+-----------+-------------------------------------------+

3.创建用户(只能登录MySQL)

命令格式

create user 用户名@客户端地址 identified by '密码'

创建只允许本机登录的MySQL用户admin

mysql> create user 'admin'@"localhost" identified by "WWW.1.com";
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user;
+-----------+-----------+
| user      | host      |
+-----------+-----------+
| admin     | localhost |
| mysql.sys | localhost |
| root      | localhost |
+-----------+-----------+
3 rows in set (0.00 sec)

创建远程连接用户admin

mysql> create user 'admin'@'192.168.140.11' identified by 'WWW.1.com';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;	#刷新用户表信息
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

远程用户验证

[root@localhost ~]# mysql -uadmin -pWWW.1.com -h <数据库服务器地址>

在数据库服务器查看都有哪些用户连接

mysql> show processlist;   
+--------+--------+----------------------+------+---------+------+----------+------------------+
| Id     | User   | Host                 | db   | Command | Time | State    | Info             |
+--------+--------+----------------------+------+---------+------+----------+------------------+
| 509975 | root   | localhost            | NULL | Query   |    0 | starting | show processlist |
| 509976 | martin | 192.168.140.11:33616 | NULL | Sleep   |   54 |          | NULL             |
+--------+--------+----------------------+------+---------+------+----------+------------------+

创建所有地址都可连接的admin用户

mysql> create user 'admin'@'%' identified by 'WWW.1.com';	#%为通配符
Query OK, 0 rows affected (0.00 sec)

4.删除用户

命令格式

drop user 用户名@客户端地址

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

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

5.修改用户密码

方法1

set password for 用户@地址 = PASSWORD("密码");	#PASSWORD()是MySQL自带的函数

mysql> set password for 'admin'@'192.168.140.11' = PASSWORD("WWW.2.com");
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

方法2

update 库.表 set authentication_string=PASSWORD("密码") where 条件

mysql> update mysql.user set authentication_string=PASSWORD("WWW.3.com") where user="admin" and host="192.168.140.11";
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

方法3(一般用于重置root密码)

编辑配置文件

[root@localhost ~]# vim /etc/my.cnf

[mysqld]	#添加以下内容
skip-grant-tables=1	#跳过授权表

重启服务

[root@localhost ~]# systemctl restart mysqld

回到Mysql使用update更新密码

[root@localhost ~]# mysql -uroot -p	#此时登录是不需要密码的

mysql> update mysql.user set authentication_string=PASSWORD("WWW.1.com") where user="root" and host="localhost";
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

删除刚刚配置文件添加的字段

[root@localhost ~]# vim /etc/my.cnf

重启MySQl服务

[root@localhost ~]# systemctl restart mysqld

二、用户权限管理

1.查看用户权限

mysql> show grants for 'admin'@'192.168.140.11';
+------------------------------------------------+
| Grants for admin@192.168.140.11                |
+------------------------------------------------+
| GRANT USAGE ON *.* TO 'admin'@'192.168.140.11' |
+------------------------------------------------+
1 row in set (0.00 sec)

USAGE：默认最小的权限

2.用户授权

几个基本权限：
- create 创建
- drop 删除
- select 查询
- update 更新
- delete 删除
- insert 插入
all 所有权限

命令格式

grant 权限,权限,权限 on 库名.表名 to 用户名 [identified by "密码"]
#[]内容为可写可不写
#在MySQL5.x版本可以使用上述格式命令直接创建用户，并且授权权限

mysql> grant select on jiaowu.tutors to 'admin'@'192.168.140.11';
mysql> flush privileges;

mysql> show grants for 'admin'@'192.168.140.11';

3.撤销权限(回收权限)

命令格式

revoke delete on 库.表 from 用户@地址;

mysql> revoke delete on jiaowu.students from 'admin'@"192.168.140.1";
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'admin'@"192.168.140.1";
+----------------------------------------------------------------------+
| Grants for admin@192.168.140.1                                       |
+----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'admin'@'192.168.140.1'                        |
| GRANT SELECT, DELETE ON `jiaowu`.`tutors` TO 'admin'@'192.168.140.1' |
| GRANT SELECT ON `jiaowu`.`students` TO 'admin'@'192.168.140.1'       |
+----------------------------------------------------------------------+
3 rows in set (0.00 sec)