【MySQL进阶之路 | 高级篇】用户与权限管理

Vez'nan的幸福生活

已于 2024-06-17 22:12:19 修改

阅读量291

点赞数 7

文章标签： mysql sql

于 2024-06-17 19:18:19 首次发布

本文链接：https://blog.csdn.net/2301_80912559/article/details/139752470

版权

1. 用户管理

MySQL用户可以分为root用户与普通用户. root用户是超级管理员，拥有所有权限，包括创建用户，删除用户和修改用户密码等管理权限. 而普通用户只拥有被授予的各种权限.
MySQL提供了许多语句来管理用户账号，这些语句可以用来管理包括登录和退出MySQL服务器，创建用户，删除用户，密码管理和权限管理等内容.
MySQL数据库的安全性需要通过账户管理来保证.

(1). 登录MySQL

启动MySQL服务器以后，可以通过mysql命令来登录MySQL服务器.

mysql -h hostIP -P port -u usename -p password -em "sql语句"

-h参数后跟主机名或主机ip. 如localhost主机.不写则默认127.0.0.1(localhost).
-P参数后接MySQL服务的端口号，通过该参数连接到指定端口.MySQL服务器默认端口号为3306.不指明则默认为3306.
-u参数后接用户名，如root用户.
-p参数后跟password密码.

[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 35
Server version: 8.0.37 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

(2). 创建用户

使用create user语句创建用户，'用户名'@'用户地址'.

%表明任何主机都可以登录该账户.

如果将%换为localhost，则只有主机才能登录该账户，而远程的虚拟机不能登录.

create user 'zhang3'@'%' identified by 'abc123';
Query OK, 0 rows affected (0.00 sec)

mysql> use mysql;
Database changed
mysql> select host, user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | li4              |
| %         | root             |
| %         | zhang3           |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
+-----------+------------------+
6 rows in set (0.01 sec)

mysql数据库下的user表记录了用户创建的信息.可以select语句查看.

update更新用户使用场景比较少，此处不再提及.

(3). 删除用户

在MySQL数据库中可以使用drop来删除普通用户，也可以直接在mysql.user表中删除用户.

1). 方式1 : 使用drop方式删除(推荐)

前文create用户zhang3，此处来删除该用户.

drop user 'zhang3'@'%';
Query OK, 0 rows affected (0.02 sec)

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
mysql> select host, user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | li4              |
| %         | root             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | wang5            |
+-----------+------------------+
6 rows in set (0.01 sec)

未指明用户地址时，用户地址默认为%.

2). 方式2 : 使用delete方式删除.

可以使用delete直接将用户信息从mysql.user表中删除.但必须拥有对mysql.user表执行delete操作的权限.

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

mysql> select host, user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | li4              |
| %         | root             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
+-----------+------------------+
5 rows in set (0.00 sec)

执行完delete命令后使用flush privileges使用户生效.

不推荐delete方式删除用户，因为系统会有残余信息，而drop会删除用户以及对应的权限，执行后mysql.user表和mysql.db表的相关记录都消失了.

(4). 设置用户密码

适用于root用户修改自己密码，以及普通用户登录后修改自己的密码.

root用户拥有很高的权限，因此必须保证root用户密码的安全.root用户登录MySQL服务器后，可以通过alter语句(推荐)和set语句来修改密码.

1). 方式1 : alter语句修改.

user()获取当前用户.

mysql> alter user user() identified by 'hx115720517!';
Query OK, 0 rows affected (0.02 sec)

mysql> quit
Bye
[hexuan@localhost ~]$ mysql -uroot -phx115720517!
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.37 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

2). 方式2 : set方式

用户给用户自己设置修改密码.

mysql> set password='hx115710517!';
Query OK, 0 rows affected (0.04 sec)

mysql> quit
Bye
[hexuan@localhost ~]$ mysql -uroot -phx115710517!
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.37 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

3). root用户给普通用户设置密码

mysql> set password for 'hexua' = 'abc';
Query OK, 0 rows affected (0.02 sec)

mysql> quit
Bye
[root@localhost ~]# mysql -u 'hexua' -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.37 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> quit
Bye

2. 权限管理

关于MySQL的权限管理就是MySQL允许你做权力以内的事，但不可以越界.比如只允许你执行select操作，你就不可以执行update操作.只允许你从该台机器连接MySQL服务器，你就不可以从其他机器连接MySQL服务器.

(1). 权限列表

mysql> show privileges;

(2). 授予权限

给用户授予权限的方式有两种，一种是通过把角色赋予用户给用户授权和直接给用户授权. 用户是数据库的使用者，我们可以通过给用户授予访问数据库资源的权限.来控制使用者对数据库的访问，消除安全隐患.

grant 权限1, 权限2,... on 数据库名称.表名称 to 用户名@用户地址 [identified by '密码']

例 :

grant all privileges即添加所有权限.

第一个*指可以在所有数据库操作.

第二个*指可以在所有表中操作.

mysql> create user 'ding1'@'%' identified by 'abc123';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all privileges on *.* to 'ding1'@'%';
Query OK, 0 rows affected (0.02 sec)

mysql> use mydbtest;
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
mysql> select * from mytbl1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | hexuan |
|    2 | iedh   |
+------+--------+
2 rows in set (0.00 sec)

此时授予了all privileges权限的用户并不等同与root用户.因为root用户可以授予其他普通用户的权力，而该用户没有. 如果需要赋予包括grant的权限，则添加参数with grant option即可.

(3). 查看权限.

# 查看当前用户的权限
show grants;
# 查看某个用户的权限
show grants for '主机名'@'主机地址';

mysql> show grants for 'hexua'@'%';
+---------------------------------------------+
| Grants for hexua@%                          |
+---------------------------------------------+
| GRANT USAGE ON *.* TO `hexua`@`%`           |
| GRANT SELECT ON `mydatest`.* TO `hexua`@`%` |
| GRANT INSERT ON `mydbtest`.* TO `hexua`@`%` |
+---------------------------------------------+
3 rows in set (0.00 sec)

(4). 收回权限

收回权限就是取消已经赋予用户的权限. 收回用户不必要的权限可以在一定程度上保证了系统的安全性.MySQL使用revoke语句来取消用户的某些权限.

在将用户账户从user表中删除之前，应该收回相应用户的所有权限.

mysql> revoke select on *.* from 'hexua'@'%';
Query OK, 0 rows affected (0.00 sec)

3. 角色

角色是在MySQL8.0引入的，在MySQL中，角色是权限的集合.可以为角色添加或移除权限.用户可以被赋予角色.同时也被授予角色包含的权限.引入角色的目的是方便管理拥有相同权限的用户.

(1). 创建角色

当用户数量较多，为了避免单独给每个用户授予多个权限，可以先将权限放入角色中，再赋予用户相应的角色.

mysql> create role 'role1';
Query OK, 0 rows affected (0.02 sec)

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
mysql> select host, user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | ding1            |
| %         | hexua            |
| %         | li4              |
| %         | role1            |
| %         | root             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | zhang3           |
+-----------+------------------+
9 rows in set (0.00 sec)

创建的角色可以在mysql.user表中查询到.

(2). 查看角色权限

mysql> grant select , insert on *.* to 'role1'@'%';
Query OK, 0 rows affected (0.03 sec)

mysql> show grants for 'role1'@'%';
+--------------------------------------------+
| Grants for role1@%                         |
+--------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO `role1`@`%` |
+--------------------------------------------+
1 row in set (0.00 sec)

(3). 回收角色权限与删除角色.

操作与回收用户权限与删除用户类似.

(4). 给用户赋予角色

grant '角色名'@'角色地址' to '主机名'@'主机地址';

mysql> create user 'user1' identified by 'abc123';
Query OK, 0 rows affected (0.03 sec)

mysql> grant 'role1' to 'user1'@'%';
Query OK, 0 rows affected (0.03 sec)

mysql> quit
Bye
[root@localhost ~]# mysql -uuser1 -pabc123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 36
Server version: 8.0.37 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(3). 激活权限

激活权限有两种方式.

1). 方式1 : 使用set default role激活角色.

mysql> set default role 'role1'@'%' to 'user1'@'%';

2). 方式2 : 将active_all_roles_on_login设置为on.

修改全局变量 global variables.

(4). 撤销用户的角色

revoke 'role'@'%' from 'user'@'%'.

重登再生效.