用户管理

参考文档

https://dev.mysql.com/doc/refman/8.0/en/access-control.html

MySQL 用户相关库和表

在MySQL中，与用户相关的信息存储在系统数据库中的特定表中。以下是与MySQL用户相关的一些主要系统数据库和表：

mysql数据库：
- user表： 存储MySQL用户的基本信息，如用户名、密码和全局权限。
information_schema数据库：
- SCHEMATA表： 包含有关所有数据库的信息，包括它们的所有者。

注意：在MySQL中，用户和权限信息不仅仅存在于mysql.user表中，还涉及到其他表和数据库。权限信息可能分布在不同的表中，而这些表之间的关系和结构可能在不同的MySQL版本中有所不同。因此，以下提到的表可能会根据你的MySQL版本而有所变化。

可以使用以下SQL查询语句来查看用户和权限信息：

-- 查看用户表信息
SELECT * FROM mysql.user;

-- 查看权限表信息
SELECT * FROM information_schema.SCHEMA_PRIVILEGES;

请注意，要执行上述查询，你需要具有足够的权限来访问这些表的信息。通常，只有具有SELECT权限的用户才能执行这些查询。

在MySQL中，用户权限相关的信息主要分布在mysql数据库的不同表中。以下是一些与用户权限相关的表，以及它们的主要字段：

user表：
- 存储有关MySQL用户的基本信息，如用户名、密码、全局权限等。
- 字段：Host, User, authentication_string, Super_priv, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv 等。
db表：
- 存储有关数据库特定权限的信息，如用户对特定数据库的权限。
- 字段：Host, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Grant_priv 等。
tables_priv表：
- 存储有关表特定权限的信息，如用户对特定表的权限。
- 字段：Host, Db, User, Table_name, Grantor, Timestamp, Table_priv 等。
columns_priv表：
- 存储有关列特定权限的信息，如用户对表中列的权限。
- 字段：Host, Db, User, Table_name, Column_name, Timestamp, Column_priv 等。
procs_priv表：
- 存储有关存储过程的权限信息。
- 字段：Host, Db, User, Routine_name, Routine_priv, Grantor, Timestamp 等。
proxies_priv表：
- 存储有关代理用户的信息。
- 字段：Host, User, Proxied_host, Proxied_user, With_grant 等。

请注意，以上列出的表和字段可能会因MySQL版本而有所不同。可以通过执行如下SQL语句来查看特定表的结构：

DESC mysql.user;
DESC mysql.db;
DESC mysql.tables_priv;
DESC mysql.columns_priv;
DESC mysql.procs_priv;
DESC mysql.proxies_priv;

这将显示每个表的字段及其类型。

用户账号组成结构

mysql 用户账号由 USERNAME 和 HOST 两部分组成

USERNAME

登录的用户名

HOST

HOST 限制此用户可通过哪些远程主机连接mysql服务器
HOST 支持通配符：
- % 匹配任意长度的任意字符
- _ 匹配任意单个字符
HOST 还支持主机名：
- web1.xiangzheng.vip

范例：

'USERNAME'@'HOST'

'user1'@'172.16.0.0/255.255.0.0'

'user2'@'172.16.%.%'

'user3'@'web1.xiangzheng.vip'

'user1'@'192.168.0.17_'

'user5'@'%'

创建用户

创建用户：
- CREATE USER 'username'@'hostname[_|%]'
  - 'username': 新用户的用户名。
  - 'hostname': 允许访问的主机，可以是具体的IP地址或主机名，也可以使用通配符 % 表示所有主机，或使用-匹配单个字符。
创建用户并同时设置密码（但有些新MySQL版本不支持）：
- CREATE USER 'USERNAME'@'HOST[_|%]' IDENTIFIED BY 'password';
注意：新建用户的默认权限为 USAGE（只能看到很少的内容）

范例：

CREATE USER 'azheng'@'%';
FLUSH PRIVILEGES;

为创建的用户设置密码

SET PASSWORD FOR 'USERNAME'@'HOST[_|%]' = 'password';

范例：

SET PASSWORD FOR 'azheng'@'%' = '123456';
FLUSH PRIVILEGES;

查询用户

在MySQL中，你可以使用以下SQL查询语句来查看当前存在的用户：

SELECT user, host FROM mysql.user;

这条SQL语句将返回mysql.user表中的用户和主机信息。每个用户都有一个关联的主机，这是为了允许不同主机上的同一用户有不同的权限。

如果你想查看用户的其他信息，比如权限等，你可以使用如下语句：

SHOW GRANTS FOR 'username'@'hostname';

请将 'username' 替换为实际的用户名，将 'hostname' 替换为实际的主机名。这将显示指定用户在指定主机上的权限信息。

用户授权

MySQL 用户授权是管理数据库访问权限的关键部分。通过授权，你可以定义哪些用户可以访问数据库服务器，以及这些用户对数据库和表的具体操作权限。以下是MySQL用户授权的一些关键概念和常用命令：

参考文档：

GRANT 授予权限

使用 GRANT 命令来授予用户权限。语法如下：

GRANT privileges ON database.table TO 'username'@'hostname';

privileges: 指定用户被授予的权限，例如 SELECT, INSERT, UPDATE, DELETE, ALL PRIVILEGES 等。
database: 要授权的数据库，可以使用 * 表示所有数据库。
table: 要授权的表，可以使用 * 表示所有表。

范例：

# 给 azheng 用户赋予在 test 数据库上的全部权限：
GRANT ALL PRIVILEGES ON test.* TO 'azheng'@'%';


# 在修改用户权限后，需要刷新权限以使更改生效。使用 `FLUSH PRIVILEGES;` 命令来刷新权限。
FLUSH PRIVILEGES;

privileges 类型

在MySQL中，privileges 是用于指定用户被授予的权限的关键字。

在 GRANT 语句中，你可以组合这些权限来满足具体的应用场景。例如，SELECT, INSERT 表示同时授予用户查询和插入的权限。

以下是一些常见的权限，你可以在 GRANT 语句中使用它们：

USAGE #无授权，新创建的用户默认为此授权模式

ALL PRIVILEGES # 表示赋予用户所有权限，包含可以给其他用户进行授权。

SELECT [(col1,col2,...)] # 允许用户从表中检索数据。

INSERT [(col1,col2,...)] # 允许用户向表中插入新行。

UPDATE [(col1,col2,...)] # 允许用户修改表中的现有行。

DELETE # 允许用户从表中删除行，DELETE 是删除整条记录 而不是某条字段 所以不可以针对字段授权

CREATE # 允许用户创建新数据库或表。

DROP # 允许用户删除数据库或表。

DROP INDEX

ALTER # 允许用户修改表结构。

INDEX # 允许用户创建或删除索引。

CREATE TEMPORARY TABLES # 允许用户创建临时表。

CREATE VIEW # 允许用户创建视图。

SHOW VIEW # 允许用户查看表的创建视图权限。

SHOW DATABASES

CREATE ROUTINE # 允许用户创建存储过程和函数。

EXECUTE # 允许用户执行存储过程。

CREATE USER # 允许用户创建、修改和删除其他用户，允许使用ALTER USER、CREATE ROLE、 CREATE USER、 DROP ROLE、 DROP USER、 RENAME USER和 REVOKE ALL PRIVILEGES语句。

FILE # 允许用户读写文件。

PROCESS # 允许用户查看所有进程。

RELOAD # 允许用户执行 `FLUSH` 语句。

SHUTDOWN # 允许用户关闭MySQL服务器。

SUPER # 允许用户执行一些特殊的系统级任务。

WITH GRANT OPTION # 能将自己获得的权限转赠给其他用户

REPLICATION SLAVE # 主从复制相关授权，将此权限授予副本用于连接到当前服务器作为其复制源服务器的帐户，使帐户能够使用 SHOW REPLICAS（或在 MySQL 8.0.22 之前 SHOW SLAVE HOSTS）SHOW RELAYLOG EVENTS、 和SHOW BINLOG EVENTS语句请求对复制源服务器上的数据库进行的更新。使用mysqlbinlog选项 --read-from-remote-server ( -R)、 --read-from-remote-source和 也需要此权限--read-from-remote-master

REPLICATION CLIENT # 主从复制相关授权，允许使用SHOW MASTER STATUS、 SHOW REPLICA STATUS和SHOW BINARY LOGS语句

LOCK TABLES

1

实现授权语法

GRANT

#语法
GRANT priv_type [(column_list)],... ON [object_type] priv_level TO 'user'@'host' [IDENTIFIED BY 'password'] [WITH GRANT OPTION];

#说明
priv_type #ALL [PRIVILEGES]
object_type #TABLE | FUNCTION | PROCEDURE
priv_level #*(所有库) 或 *.*  或 db_name.* 或 db_name.tbl_name 或 tbl_name(当前库的表) 或 db_name.routine_name(指定库的函数,存储过程,触发器)
with_option # GRANT OPTION
 | MAX_QUERIES_PER_HOUR count
 | MAX_UPDATES_PER_HOUR count
 | MAX_CONNECTIONS_PER_HOUR count
 | MAX_USER_CONNECTIONS count

实现授权范例

授权someuser用户在somehost主机上可以登录，对mydb库中的mytabl表中的col1字段具有SELECT权限，对col2和col3字段具有INSERT权限

GRANT SELECT (col1), INSERT (col2,col3) ON mydb.mytbl TO 'someuser'@'somehost‘;

授权test账号在10.0.0.0/24这个网段可以登录，在hellodb数据库中具有最大的权限

GRANT ALL ON hellodb.* TO test@'10.0.0.%';

创建并授权admin这个账号在10.0.0.0/24这个网段中可以登录，对所有库中的所有表中具有最大权限，并且设置密码为azheng，其权限具有传染性（能将自己获得的权限转赠给其他用户，这样才能真正意义上的称为管理员，否则不加这个选项就相当于只能具有查看和修改库的最大权限，而不能创建用户并为其设置权限）

GRANT ALL ON *.* TO admin@'10.0.0.%' identified by 'azheng' WITH GRANT OPTION;

创建并授权test2这个账号在10.0.0.0/24这个网段中可以登录，对hellodb库中的students表中的stduid和name列具有查看的权力

GRANT SELECT(stuid,name) ON hellodb.students TO test2@'10.0.0.%' identified by 'azheng';

SHOW GRANTS 查看权限

使用 SHOW GRANTS 命令来查看用户的权限。语法如下：

SHOW GRANTS FOR 'username'@'hostname';

范例：

# 查看指定用户的授权信息
SHOW GRANTS FOR 'azheng'@'%';

# 查看当前用户的授权信息，CURRENT_USER()为函数
SHOW GRANTS FOR CURRENT_USER();

REVOKE 取消授权

使用 REVOKE 命令来撤销用户权限。

REVOKE privileges ON database.table FROM 'username'@'hostname';

授权示例：

-- 创建新用户并授予权限
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE ON database.* TO 'newuser'@'localhost';
FLUSH PRIVILEGES;

-- 查看用户权限
SHOW GRANTS FOR 'newuser'@'localhost';

-- 修改密码
SET PASSWORD FOR 'newuser'@'localhost' = PASSWORD('newpassword');

-- 撤销权限
REVOKE INSERT ON database.* FROM 'newuser'@'localhost';
FLUSH PRIVILEGES;

1

取消授权语法

REVOKE

REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] priv_level FROM user [, user] ...

取消授权范例

REVOKE DELETE ON testdb.* FROM 'testuser'@'172.16.0.%';

用户重命名

RENAME USER old_user_name TO new_user_name;

删除用户

DROP USER 'USERNAME'@'HOST'
注意：不要把系统自带的账号删除，否则有可能会出问题，如：mysql.infoschema、mysql.session、mysql.sys账号等

范例：

# 删除默认的空用户
DROP USER ''@'localhost';

# 删除 azheng 用户
DROP USER azheng;

修改用户密码

使用 ALTER USER 或 SET PASSWORD 命令来修改用户密码。

ALTER USER 'username'@'hostname' IDENTIFIED BY 'new_password';
FLUSH PRIVILEGES;

或者

SET PASSWORD FOR 'username'@'hostname' = PASSWORD('new_password');
FLUSH PRIVILEGES;

SET PASSWORD FOR
老版本 密码保存在mysql.user表的 password字段中
中期版本 密码保存在mysql.user表的 authentication_string 和 password字段中，如果authentication_string和password字段都存在密码则 authentication_string优先生效
最新版本 密码保存在mysql.user表的 authentication_string字段中

通过 SQL 语句修改

#方法1，建议使用
SET PASSWORD FOR 'user'@'host' = 'password';
#或
ALTER USER 'user'@'host' IDENTIFIED BY 'password'
#更改当前用户的密码：
ALTER USER USER() IDENTIFIED BY 'password';


--------------------------------------------------------------------------

#方法2 慎用!（因为是通过直接修改表来修改密码）而且不加WHERE条件会导致所有用户的密码都被修改，
#PASSWORD其实是一个函数，可以将输入的密码生成加密的字符串
UPDATE mysql.user SET password=PASSWORD('password') WHERE clause;
或
#mariadb 10.3
update mysql.user set authentication_string=password('ubuntu') WHERE user='mage';

#此方法需要执行下面指令才能生效：
FLUSH PRIVILEGES;

通过 mysqladmin 命令修改

#修改root密码
mysqladmin –uroot –pcentos password '12345'

关闭 root 免密登陆

# 将 'your_password' 替换为您要设置的实际密码。
ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY 'your_password';
FLUSH PRIVILEGES;

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

MySQL 8.0 版本引入了密码策略，该错误是因为新密码不符合默认的密码策略要求。MySQL 8.0 默认要求密码包含大小写字母、数字和特殊字符，并且长度不小于 8 个字符。

解决此问题的方法之一是选择一个符合密码策略的密码。尝试使用包含大写字母、小写字母、数字和特殊字符的复杂密码。例如：

ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY 'StrongPassword123!';
FLUSH PRIVILEGES;

如果您仍然想使用较简单的密码，可以修改密码策略。请注意，这可能会降低系统的安全性。以下是修改密码策略的方法：

SET GLOBAL validate_password.policy = 0;

然后再次尝试更改密码：

ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY 'root';

完成后，如果需要，可以将密码策略重新设置为默认值：

SET GLOBAL validate_password.policy = 1;

请记住，选择更复杂和安全的密码对于生产环境是很重要的。

修改用户授权主机范围

mysql> update mysql.user set Host='localhost' WHERE user='wordpress';

#或
mysql> update mysql.user set Host='127.0.0.1' WHERE user='wordpress';

—

FLUSH PRIVILEGES

执行 FLUSH PRIVILEGES; 是在修改用户权限或修改密码后刷新MySQL权限缓存的一种常见做法。

这是因为MySQL服务器在内存中缓存了授权信息，当你修改了用户权限或密码时，这些更改可能不会立即生效，直到刷新了权限缓存。

FLUSH PRIVILEGES; 语句会强制重新加载用户权限，使得最新的更改生效。这通常在以下情况下使用：

创建新用户或更改用户权限： 当你创建新用户、修改用户权限或撤销权限时，执行 FLUSH PRIVILEGES; 会确保服务器立即生效。
```
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT ON database.* TO 'newuser'@'localhost';
FLUSH PRIVILEGES;
```
修改密码： 如果你使用 ALTER USER 或 SET PASSWORD 命令修改了用户密码，执行 FLUSH PRIVILEGES; 将确保新密码立即生效。
```
ALTER USER 'existinguser'@'localhost' IDENTIFIED BY 'newpassword';
FLUSH PRIVILEGES;
```

请注意，对于一些修改，如 GRANT 或 REVOKE 权限，MySQL 8.0 版本之后，不再需要总是执行 FLUSH PRIVILEGES;，因为这些更改会自动刷新缓存。但是，执行 FLUSH PRIVILEGES; 仍然是一种保守的做法，可以确保你的更改立即生效。

—

实战案例

创建用户并授权

GRANT ALL ON wordpress.* TO wordpress@'10.0.0.%' identified by 'azheng';

**注意：**新版本的MySQL不支持创建用户和授权一同操作，需要使用下面的创建用户再授权方法

MariaDB [(none)]> GRANT ALL ON wordpress.* TO wordpress@'10.0.0.%' identified by 'azheng';
Query OK, 0 rows affected (0.001 sec)
----------------------------------------------------------------------------------
mysql> GRANT ALL ON wordpress.* TO wordpress@'10.0.0.%' identified by 'azheng';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by 'azheng'' at line 1

创建用户再授权

#创建用户并设置密码
CREATE USER 'wordpress'@'10.0.0.%' IDENTIFIED BY 'azheng';

#创建用户后设置密码
SET PASSWORD FOR 'wordpress'@'10.0.0.%' = 'azheng';

#授权
GRANT ALL ON wordpress.* TO wordpress@'10.0.0.%';

找回 root 密码

MySQL

5.7

MYSQL5.7.9版本前的修改方式和mariadb基本一致

8.0

# 停止MySQL服务
systemctl stop mysql.service


# 修改MySQL配置文件
# vim /etc/my.cnf
[mysqld]
skip-grant-tables # 忽略授权表，配合skip-networking关闭网络功能来使用                              
skip-networking # 关闭网络功能，只能在本机登陆，否则任何人都可以随意登录（其实就是关闭3306端口）


# 启动MySQL服务
systemctl start mysql.service


# 将user表root用户的authentication_string清空
mysql -uroot
UPDATE mysql.user SET authentication_string = '' WHERE user = 'root';
FLUSH privileges;
select user,host,authentication_string from mysql.user where user='root'; # 验证


# 修改密码
alter mysql.user root@localhost identified by 'P@ssw0rd';


# 还原配置重启服务
...

MariaDB

修改配置文件并重启服务

# vim /etc/my.cnf
[mysqld]
skip-grant-tables # 忽略授权表，配合skip-networking关闭网络功能来使用                              
skip-networking # 关闭网络功能，只能在本机登陆，否则任何人都可以随意登录（其实就是关闭3306端口）
...

# systemctl restart mariadb

在本机进行登录并修改密码

# mysql
#mariadb 新版
MariaDB [(none)]> update mysql.user set authentication_string=password('ubuntu') where user='root';
#mariadb 旧版
MariaDB [(none)]> update mysql.user set password=password('ubuntu') where user='root';

#修改完毕后刷新数据
MariaDB [(none)]> flush privileges;

恢复配置文件并重启服务

# vim /etc/my.cnf
[mysqld]
#skip-grant-tables                                                          
#skip-networking

# systemctl restart mariadb

测试登录

# mysql -uroot -pubuntu

—

实现流程

1.启动mysqld进程时，为其使用如下选项：

--skip-grant-tables #忽略授权表，配合skip-networking关闭网络功能来使用，否则任何人都可以随意登录
--skip-networking #关闭网络功能（其实就是关闭3306端口）

2.使用UPDATE命令修改管理员密码，SET PASSWORD FOR不可以修改吗？？？？

3.关闭mysqld进程，移除上述两个选项，重启mysqld

注意事项

开启忽略授权表选项后将无法通过正常方式修改密码

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

—

密码管理问题汇总

ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal number
这种方式可应用于修改新版本的MySQL用户密码？

# 查询密码对应的十六进制码
select password('12345');

# 使用十六进制码进行设置或修改
SET PASSWORD FOR 'wp-user'@'%' = '*00A51F3F48415C7D4E8908980D443C29C69B60C9';