MySQL（四）用户与权限管理

合集 - MySQL高级(30)

1.MySQL（一）Linux下MySQL的安装2023-02-162.MySQL（二）字符集、比较规则与规范2023-02-163.MySQL（三）数据目录2023-02-16

4.MySQL（四）用户与权限管理2023-02-16

5.MySQL（五）配置文件、系统变量与MySQL架构2023-03-176.MySQL（六）存储引擎2023-03-177.MySQL（七）索引2023-03-178.MySQL（八）哈希索引、AVL树、B树与B+树的比较2023-03-179.MySQL（九）InnoDB行格式2023-04-0310.MySQL（九）InnoDB数据结构2023-04-0311.MySQL（十）表空间结构：区、段与碎片区2023-04-0312.MySQL（十一）索引的分类和创建原则2023-04-0513.MySQL（十二）索引使用的情况分析2023-04-0614.MySQL（十三）MySQL性能分析工具：慢查询日志与PROFILE查询成本2023-04-0715.MySQL（十四）分析查询语句Explain 七千字总结2023-04-1416.MySQL（十五）分析优化器的查询计划：Trace2023-07-1817.MySQL（十六）索引优化：索引失效的情况分析2023-04-2918.MySQL（十七）查询优化（一）2023-05-0219.MySQL（十七）查询优化（二）与数据库主键设计2023-05-0220.MySQL（十八）MySQL事务(一)：事务的概述与使用2023-05-0421.MySQL（十八）MySQL事务(二)：事务的隔离级别2023-05-0922.MySQL（十九）MySQL事务日志（一）RedoLog2023-05-0923.MySQL（十九）MySQL事务日志（二）UndoLog2023-05-0924.MySQL（二十）锁（一）锁的概述与S、X锁2023-05-0925.MySQL（二十）锁（二）表锁、行锁与页级锁2023-05-0926.MySQL（二十）锁（三）乐观锁与悲观锁、显示隐式锁和其他锁2023-05-0927.MySQL（二十一）MVCC多版本并发控制2023-07-1828.MySQL（二十二）其他数据库日志（一）通用查询日志和错误日志2023-05-0929.MySQL（二十二）其他数据库日志（二）bin log二进制日志与中继日志2023-05-0930.【完结撒花】MySQL（二十三）主从复制2023-05-10

收起

用户与权限管理

---

👤用户管理

MySQL用户分为普通用户和root用户，提供了许多语句来管理包括登录、退出MySQL服务器、创建用户、删除用户、密码管理和权限管理等内容。

⛵登录MySQL服务器

mysql -h hostName|hostIP -P port -u userName -p DataBaseName -e "SQL语句"

创建用户

查看当前用户：

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             |
+-----------+------------------+
| %         | root             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
+-----------+------------------+
4 rows in set (0.00 sec)
 

创建命令：

create user ''@'' identified by ''

mysql> create user 'hikaru'@'%' identified by 'password';
Query OK, 0 rows affected (0.04 sec)
mysql> use mysql;
Database changed
mysql> select host, user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | hikaru           |
| %         | root             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
+-----------+------------------+
5 rows in set (0.00 sec)
 

• mysql数据库的user表是由host和user组成的联合主键

  所以再创建一个限制本地连接的用户也是ok的：

  mysql> create user 
      -> 'hikaru'@'localhost' identified by 'password';
  Query OK, 0 rows affected (0.00 sec)
  mysql> select host, user from user;
  +-----------+------------------+
  | host      | user             |
  +-----------+------------------+
  | %         | hikaru           |
  | %         | root             |
  | localhost | hikaru           |
  | localhost | mysql.infoschema |
  | localhost | mysql.session    |
  | localhost | mysql.sys        |
  +-----------+------------------+
  6 rows in set (0.00 sec)
   

修改用户

mysql> update user set user='tod4' where user='hikaru' and host='localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select host, user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | hikaru           |
| %         | root             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | tod4             |
+-----------+------------------+
6 rows in set (0.00 sec)
 

❓相当于直接对表的记录进行修改，属于DDL语句，需要刷新权限：

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

删除用户

Drop

mysql> drop user 'tod4';
ERROR 1396 (HY000): Operation DROP USER failed for 'tod4'@'%'
mysql> drop user 'tod4'@'localhost';
Query OK, 0 rows affected (0.00 sec)
 

Delete（DDL语句需要刷新权限）

delete from user where user = xxx and host = xxx;
flush privileges;

不推荐使用Delete删除用户，因为创建的用户不仅在user表中也在其他表中添加了记录，因此只删除user表会有信息残留

修改用户密码

修改自己的密码

① 使用Alter语句修改普通用户密码（推荐）

ALTER USER USER() IDENTITY BY 'new_password';

② 使用set语句修改普通用户密码

SET PASSWORD='new_password'

修改其他用户的密码

① 使用Alter语句修改普通用户密码（推荐）

ALTER USER 'username'@'host' IDENTIFIED BY 'new_password'

② 使用set语句修改普通用户密码

SET PASSWORD FOR 'username'@'host' IDENTIFIED BY 'new_password'

👤权限管理

---

允许做MySQL赋予权限以内的事情，不可以越界。比如只可以执行select、只允许从某一台机器上登录MySQL等等。

权限列表

mysql> show privileges;
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Privilege               | Context                               | Comment                                               |
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Alter                   | Tables                                | To alter the table                                    |
| Alter routine           | Functions,Procedures                  | To alter or drop stored functions/procedures          |
| Create                  | Databases,Tables,Indexes              | To create new databases and tables                    |
| Create routine          | Databases                             | To use CREATE FUNCTION/PROCEDURE                      |
| Create temporary tables | Databases                             | To use CREATE TEMPORARY TABLE                         |
| Create view             | Tables                                | To create new views                                   |
| Create user             | Server Admin                          | To create new users                                   |
| Delete                  | Tables                                | To delete existing rows                               |
| Drop                    | Databases,Tables                      | To drop databases, tables, and views                  |
| Event                   | Server Admin                          | To create, alter, drop and execute events             |
| Execute                 | Functions,Procedures                  | To execute stored routines                            |
| File                    | File access on server                 | To read and write files on the server                 |
| Grant option            | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess   |
| Index                   | Tables                                | To create or drop indexes                             |
| Insert                  | Tables                                | To insert data into tables                            |
| Lock tables             | Databases                             | To use LOCK TABLES (together with SELECT privilege)   |
| Process                 | Server Admin                          | To view the plain text of currently executing queries |
| Proxy                   | Server Admin                          | To make proxy user possible                           |
| References              | Databases,Tables                      | To have references on tables                          |
| Reload                  | Server Admin                          | To reload or refresh tables, logs and privileges      |
| Replication client      | Server Admin                          | To ask where the slave or master servers are          |
| Replication slave       | Server Admin                          | To read binary log events from the master             |
| Select                  | Tables                                | To retrieve rows from table                           |
| Show databases          | Server Admin                          | To see all databases with SHOW DATABASES              |
| Show view               | Tables                                | To see views with SHOW CREATE VIEW                    |
| Shutdown                | Server Admin                          | To shut down the server                               |
| Super                   | Server Admin                          | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.   |
| Trigger                 | Tables                                | To use triggers                                       |
| Create tablespace       | Server Admin                          | To create/alter/drop tablespaces                      |
| Update                  | Tables                                | To update existing rows                               |
| Usage                   | Server Admin                          | No privileges - allow connect only                    |
+-------------------------+---------------------------------------+-------------------------------------------------------+
31 rows in set (0.00 sec)

权限分布	可能设置的权限
表权限
列权限
过程权限

查看授权

看自己的：

show grants;

看别人的：

show grants for '...'@'...';

授予权限

授予权限分为两种：角色授予用户和直接给用户授权

直接给用户授权

grant select, update on dbtest.* to 'test'@'%';

grant all privileges on *.* to '';

all privileges并不意味着和root等同的权限，如赋予别人权限的权限就没有给

需要使用WITH GRANT OPTION选项

收回权限

revoke all privileges on *.* from '...'@'...'

权限表

有缘再补。。。

🎛️访问控制

有缘再补。。

角色管理 8.0新特性

创建角色

create role 'rolename'@'host';

不写登录主机名默认%

给角色赋予权限

grant * on * to 'rolename'@'host';

查看角色权限

show grants for 'rolename'@'host';

收回角色权限

revoke * on * from 'rolename'@'host';

删除角色

drop role 'rolename';

向用户授予角色

grant 'rolename'@'host' to 'username'@'host';

Mysql创建了角色之后都是没有被激活的，也就是不能用，必须要手动激活才能获得相应的权限

激活角色

激活角色有两种方式

①set default role

set default role all to 'username'@'host';

②将active_all_roles_on_login设置为ON

show variables like 'activate';
set global active_all_roles_on_login ON;

撤销角色

revoke 'rolename'@'host' from 'username'@'host';