常用管理命令

目录

• mysql学习笔记：三.账号和权限管理
  • 1.mysql权限工作原理
    • • 1）连接核实阶段
      • 2）请求核实阶段
  • 2.权限生效时间
  • 3.权限说明
  • 4.相关授权操作
    • 查看所有用户
    • 创建用户
    • 修改密码
    • 用户授权
    • 查看用户权限
    • 取消用户授权
    • 删除用户
    • 总结

mysql学习笔记：三.账号和权限管理

1.mysql权限工作原理

MySQL 权限表在数据库启动时载入内存，用户通过身份认证后，系统会在内存中进行相应权限的存取。当 MySQL 允许一个用户执行各种操作时，它将首先核实该用户向 MySQL 服务器发送的连接请求，然后确认用户的操作请求是否被允许。

当用户进行连接时，MySQL 实现权限控制主要有以下两个阶段：

1）连接核实阶段

登录 MySQL 服务器时，客户端连接请求中会提供用户名称、主机地址和密码，MySQL 服务器会使用 user 表中的 Host、User 和 authentication_string （MySQL 5.7 版本之前是 Password）字段执行身份检查。

只有客户端请求的主机名和用户名在 user 表中有匹配的记录，并且密码正确时，MySQL 服务器才会通过身份认证，接受连接，否则拒绝连接。

MySQL 通过 IP 地址和用户名联合进行身份认证。例如 MySQL 安装后默认创建的用户 root@localhost，表示用户 root 只能从本地（localhost）进行连接时才能通过认证。此用户从其它任何主机对数据库进行连接时都将被拒绝。也就是说，用户名相同，IP 地址不同，MySQL 则将其视为不同的用户。

服务器接受连接后进入请求核实阶段等待用户请求。如果连接核实没有通过，服务器则完全拒绝访问。

2）请求核实阶段

建立连接后，服务器进入请求核实阶段，对在此连接上的每个请求，服务器都会检查用户是否有足够的权限来执行它。这正是授权表中的权限列发挥作用的地方。

权限按照以下权限表的顺序得到数据库权限：user→db→tables_priv→columns_priv→procs_priv。在这几个权限表中，权限范围依次递减，全局权限覆盖局部权限。

请求核实的过程如下所示：

1）用户向 MySQL 发出操作请求。

2）MySQL 首先检查 user 表，匹配 User、Host 字段值，查看请求的全局权限在 user 表中是否被授权。授权则允许操作执行，如果指定的权限在 user 表中没有被授权。MySQL 将检查 db 表。

3）db 表是下一安全层级，其中的权限限定于数据库层级，在该层级的 SELECT 权限允许用户查看指定数据库的所有表中的数据。

MySQL 检查 db 权限表中的权限信息，匹配 User、Host 字段值，查看请求的数据库级别的权限在 db 表中是否被授权。授权则允许操作执行，否则 MySQL 继续向下查找。

4）MySQL 检查 tables_priv 权限表中的权限信息，匹配 User、Host 字段值，查看请求的数据表级别的权限在 tables_priv 表中是否被授权。授权则允许操作执行，否则 MySQL 继续向下查找。

5）MySQL 检查 columns_priv 权限表中的权限信息，匹配 User、Host 字段值，查看请求的列级别的权限在 columns_priv 表中是否被授权。授权则允许操作执行，否则 MySQL 继续向下查找。

6）如果所有权限表都检查完毕，还是没有找到允许的权限操作，那么 MySQL 将返回错误信息，即用户请求的操作不能执行，操作失败。

提示：上面提到 MySQL 通过向下层级的顺序检查权限表，但并不意味着所有的权限都要执行该过程。例如，一个用户登录到 MySQL 服务器之后只执行对 MySQL 的管理操作，此时只涉及管理权限，因此 MySQL 只检查 user 表。

2.权限生效时间

用户及权限信息放在库名为mysql的库中，mysql启动时，这些内容被读进内存并且从此时生效，所以如果通过直接操作这些表来修改用户及权限信息的，需要 重启mysql 或者执行 flush privileges; 才可以生效。

用户登录之后，mysql会和当前用户之间创建一个连接，此时用户相关的权限信息都保存在这个连接中，存放在内存中，此时如果有其他地方修改了当前用户的权限，这些变更的权限会在下一次登录时才会生效。

3.权限说明

grant <权限1>,<权限2>,… on <数据库名称>.<表名> to '用户'[@'<主机>'] [identified by <'密码'>] [with grant option];

with grant option：表示该用户可以将自己拥有的权限授权给别人。

权限	权限级别	说明
ALL PRIVILEGES	ALL	ALL，所有权限
CREATE	数据库、表或索引	创建数据库、表或索引权限
DROP	数据库或表	删除数据库或表权限
GRANT OPTION	数据库、表或保存的程序	赋予权限选项
REFERENCES	数据库或表	建立外键关系权限
ALTER	表	更改表，比如添加字段、索引等
DELETE	表	删除数据权限
INDEX	表	索引权限
INSERT	表	插入权限
SELECT	表	查询权限
UPDATE	表	更新权限
CREATE VIEW	视图	创建视图权限
SHOW VIEW	视图	查看视图权限
ALTER ROUTINE	存储过程	更改存储过程权限
CREATE ROUTINE	存储过程	创建存储过程权限
EXECUTE	存储过程	执行存储过程权限
FILE	服务器主机上的文件访问	文件访问权限
CREATE TEMPORARY TABLES	服务器管理	创建临时表权限
LOCK TABLES	服务器管理	锁表权限
CREATE USER	服务器管理	创建用户权限
PROCESS	服务器管理	查看进程权限
RELOAD	服务器管理	执行flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload等命令的权限
REPLICATION CLIENT	服务器管理	复制权限
REPLICATION SLAVE	服务器管理	复制权限
SHOW DATABASES	服务器管理	查看数据库权限
SHUTDOWN	服务器管理	关闭数据库权限
SUPER	服务器管理	执行kill线程权限

4.相关授权操作

查看所有用户

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

创建用户

语法：

create user 'username'[@'host'] [identified by '密码'];

说明：

主机名默认为%，比指定主机名时表示任意主机都能连接，但一般指定主机

密码可以省略

这里（'USERNAME'@'HOST'）的HOST用于限制此用户可通过哪些主机远程连接mysql程序，其值可为：

• IP地址，如：172.32.12.125

• 通配符

  • %：匹配任意长度的任意字符，常用于设置允许从任何主机登录

  • _：匹配任意单个字符

示例：配置zhangsan用户只能在本地登录

mysql> create user zhangsan@localhost identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> \q
Bye
[root@localhost local]# mysql -uzhangsan -p123456
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 3
Server version: 5.7.30 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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>

修改密码

方法1：

#给其他人修改密码
SET PASSWORD FOR '用户名'@'主机' = PASSWORD('密码');

#给自己修改密码
set password = password('密码');

方法2：

mysql推荐方式：

#修改user表来改密码
use mysql;
update user set authentication_string = password('123456') where user = 'root' and host = '%';
flush privileges;

#也可以使用alter修改
#改表之后必须flush privileges;刷新权限表
#5.7中user表中的authentication_string字段表示密码，老的一些版本中密码字段是password。

#mariadb用以下方式
use mysql;
UPDATE user SET password=password("New-password") WHERE user='root';
flush privileges;
exit;

用户授权

创建用户后要给用户授权

语法：

grant privileges ON database.table TO 'username'[@'host'] [with grant option]

grant说明：

• privileges（权限列表），可以是all，也可以是select,insert等等，多个权限用逗号隔开。
• ON用来指定权限针对哪些库和表，格式为库名.表名，*.*表示所有库的所有表。
• TO表示将权限赋予某个用户, 格式为 username@host ，@前面为用户名，@后面接限制的主机，可以是IP、IP段、域名以及%，%表示任何地方。
• WITH GRANT OPTION 这个选项表示该用户可以将自己拥有的权限授权给别人。
• 可以使用grantg重复给用户添加权限，相互叠加
• 切记'username'@'host'这个整体被视为一个用户标识

示例：

#给本地登录的zhangsan授权所有权限
grant all on *.* to 'zhangsan'@'localhost';

#给本地登录的zhangsan授权select和update权限
grant select,update on seata.* to 'zhangsan'@'localhost';

用户不存在时不能授权，例如：

mysql> grant all on *.* to 'zhangsan'@'%';
ERROR 1133 (42000): Can't find any matching row in the user table

创建用户的同时授权

mysql> grant all on *.* to 'zhangsan'@'%' identified by '13456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

查看用户权限

查看当前用户授权信息

mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

查看指定用户的授权信息

mysql> show grants for zhangsan@localhost ;
+-------------------------------------------------------+
| Grants for zhangsan@localhost                         |
+-------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'zhangsan'@'localhost' |
+-------------------------------------------------------+
1 row in set (0.00 sec)

取消用户授权

语法：

revoke priv_type,... ON db_name.table_name FROM 'username'@'host';

#查看zhangsan用户权限
mysql> show grants for zhangsan@localhost ;
+-------------------------------------------------------+
| Grants for zhangsan@localhost                         |
+-------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'zhangsan'@'localhost' |
+-------------------------------------------------------+
1 row in set (0.00 sec)


#删除其insert权限
mysql> revoke insert on *.* from zhangsan@localhost;
Query OK, 0 rows affected, 1 warning (0.00 sec)


#查看剩余权限
mysql> show grants for zhangsan@localhost\G
*************************** 1. row ***************************
Grants for zhangsan@localhost: GRANT SELECT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'zhangsan'@'localhost'
1 row in set (0.00 sec)

删除用户

方法1：

drop user '用户名'[@'主机']

示例:

drop user 'zhangsan'@'localhost';
flush privileges;

drop的方式删除用户之后，用户下次登录就会起效。

方法2：

通过删除mysql.user表数据的方式删除

delete from user where user='用户名' and host='主机';
flush privileges;

通过表的方式删除的，需要调用 flush privileges; 刷新权限信息（权限启动的时候在内存中保

存着，通过表的方式修改之后需要刷新一下).

总结

mysql服务进程启动时会读取mysql库中的所有授权表至内存中：

• GRANT或REVOKE等执行权限操作会保存于表中，mysql的服务进程会自动重读授权表，并更新至内存中
• 对于不能够或不能及时重读授权表的命令，可手动让mysql的服务进程重读授权表

mysql> FLUSH PRIVILEGES;

mysql中用户和权限的信息在库名为mysql的库中，启动时加载至内存

也就是说通过命令操作用户和权限的不需要刷新权限，通过修改表来修改 用户和权限需要刷新，因为它们是存放在内存中的