【MYSQL】用户与权限

mysql版本：8.0.39

为什么要管理用户和权限

MySQL 用户权限管理是数据库安全体系的核心组成部分，当有多个用户需要操作数据库时，管理员需要控制哪些用户可以访问哪些数据库和执行哪些操作，否则很容易因为误操作，给数据库带来严重的安全问题

用户

用户信息

MySQL中的用户，都存储在系统数据库mysql的user表中

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| ry-vue             |
| sys                |
| test1              |
| test1backups       |
+--------------------+
7 rows in set (0.00 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

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

字段解释：

host： 表示这个用户可以从哪个主机登陆，如果是localhost，表示只能从本机登陆，如果是%，则其他服务器也可以登录
user： 用户名

创建用户

语法

create user '用户名'@'登陆主机/ip' identified by '密码';

案例

mysql> create user 'view'@'%' identified by 'cf2025';
Query OK, 0 rows affected (0.00 sec)

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

此时便可以使用view用户进行登录了

[root@VM-4-4-centos ~]# mysql -u view -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 34904
Server version: 8.0.39 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+
2 rows in set (0.00 sec)

修改用户密码

语法

ALTER USER '用户'@'主机名' IDENTIFIED BY '新的密码';

案例
修改view用户的密码为：123456

ALTER USER 'view'@'%' IDENTIFIED BY '123456';

删除用户

语法
drop user ‘用户名’@‘主机名’

案例

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

mysql> drop user 'view'@'%';
Query OK, 0 rows affected (0.01 sec)

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

权限

查看用户权限

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

给用户授权

刚创建的用户没有任何权限。需要给用户授权。

语法
grant 权限列表 on 库.对象名 to ‘用户名’@‘登陆位置’ [identified by ‘密码’]

说明：

权限列表，多个权限用逗号分开

grant select on ...
grant select, delete, create on ....
grant all [privileges] on ... -- 表示赋予该用户在该对象上的所有权限

• *.*代表本系统中的所有数据库的所有对象（表，视图，存储过程等）
• 库.* : 表示某个数据库中的所有数据对象(表，视图，存储过程等)
• identified by可选。 如果用户存在，赋予权限的同时修改密码,如果该用户不存在，就是创建用户

案例
终端A:(使用root账号)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| ry-vue             |
| sys                |
| test1              |
| test1backups       |
+--------------------+
7 rows in set (0.01 sec)

mysql> grant select on test1.* to 'view'@'%';
Query OK, 0 rows affected (0.00 sec)

终端B (使用view账号)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
| test1              |
+--------------------+
3 rows in set (0.00 sec)

mysql> use test1
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> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| customer_info   |
| people          |
+-----------------+
2 rows in set (0.01 sec)

mysql> select * from customer_info;
+----+------+------------+-----------+
| id | name | hobby      | is_delete |
+----+------+------------+-----------+
|  1 | wang | 散步       |         0 |
|  2 | lisi | 上网2      |         0 |
|  3 | lisi | 打妖怪2    |         0 |
+----+------+------------+-----------+
3 rows in set (0.00 sec)

刷新权限

如果发现赋权限后，没有生效，执行如下指令

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

回收权限

语法
revoke 权限列表 on 库.对象名 from ‘用户名’@‘登陆位置’；

示例
终端A:(root 账号)

# 回收test1库的所有权限
mysql> revoke all on test1.* from 'view'@'%';
Query OK, 0 rows affected (0.00 sec)

终端B:(view 账号)

# 查看数据库权限，已没有test1库的权限
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+
2 rows in set (0.00 sec)