数据库权限管理
数据库权限管理
MySQL 是一个多用户数据库，具有功能强大的访问控制系统，可以为不同用户指定不同权限。我们使用的是 root 用户，该用户是超级管理员，拥有所有权限，包括创建用户、删除用户和修改用户密码等管理权限。为了实际项目的需要，可以创建拥有不同权限的普通用户。
1、Mysql权限表
MySQL 在安装时会自动创建一个名为 mysql 的数据库，mysql 数据库中存储的都是用户权限表。用户登录以后，MySQL 会根据这些权限表的内容为每个用户赋予相应的权限。user 表是 MySQL 中最重要的一个权限表，用来记录允许连接到服务器的账号信息。需要注意的是，在 user 表里启用的所有权限都是全局级的，适用于所有数据库。user 表中的字段大致可以分为 4 类，分别是用户列、权限列、安全列和资源控制列，下面主要介绍这些字段的含义。
1.1、用户列
用户列存储了用户连接 MySQL 数据库时需要输入的信息。需要注意的是 MySQL 5.7 版本不再使用 Password 来作为密码的字段，而改成了 authentication_string。
| 字段名 | 字段类型 | 是否为空 | 默认值 | 说明 |
|---|---|---|---|---|
| Host | char(60) | NO | 无 | 主机名 |
| User | char(32) | NO | 无 | 用户名 |
| authentication_string | text | YES | 无 | 密码 |
用户登录时，如果这 3 个字段同时匹配，MySQL 数据库系统才会允许其登录。创建新用户时，也是设置这 3 个字段的值。修改用户密码时，实际就是修改 user 表的 authentication_string 字段的值。因此，这 3 个字段决定了用户能否登录。
1.2、权限列
权限列的字段决定了用户的权限，用来描述在全局范围内允许对数据和数据库进行的操作。
权限大致分为两大类，分别是高级管理权限和普通权限：
- 高级管理权限主要对数据库进行管理，例如关闭服务的权限、超级权限和加载用户等；
- 普通权限主要操作数据库，例如查询权限、修改权限等。
user 表的权限列包括 Select_priv、Insert_ priv 等以 priv 结尾的字段，这些字段值的数据类型为 ENUM，可取的值只有 Y 和 N：Y 表示该用户有对应的权限，N 表示该用户没有对应的权限。从安全角度考虑，这些字段的默认值都为 N。
| 字段名 | 字段类型 | 是否为空 | 默认值 | 说明 |
|---|---|---|---|---|
| Select_priv | enum('N','Y') | NO | N | 是否可以通过SELECT 命令查询数据 |
| Insert_priv | enum('N','Y') | NO | N | 是否可以通过 INSERT 命令插入数据 |
| Update_priv | enum('N','Y') | NO | N | 是否可以通过UPDATE 命令修改现有数据 |
| Delete_priv | enum('N','Y') | NO | N | 是否可以通过DELETE 命令删除现有数据 |
| Create_priv | enum('N','Y') | NO | N | 是否可以创建新的数据库和表 |
| Drop_priv | enum('N','Y') | NO | N | 是否可以删除现有数据库和表 |
| Reload_priv | enum('N','Y') | NO | N | 是否可以执行刷新和重新加载MySQL所用的各种内部缓存的特定命令，包括日志、权限、主机、查询和表 |
| Shutdown_priv | enum('N','Y') | NO | N | 是否可以关闭MySQL服务器。将此权限提供给root账户之外的任何用户时，都应当非常谨慎 |
| Process_priv | enum('N','Y') | NO | N | 是否可以通过SHOW PROCESSLIST命令查看其他用户的进程 |
| File_priv | enum('N','Y') | NO | N | 是否可以执行SELECT INTO OUTFILE和LOAD DATA INFILE命令 |
| Grant_priv | enum('N','Y') | NO | N | 是否可以将自己的权限再授予其他用户 |
| References_priv | enum('N','Y') | NO | N | 是否可以创建外键约束 |
| Index_priv | enum('N','Y') | NO | N | 是否可以对索引进行增删查 |
| Alter_priv | enum('N','Y') | NO | N | 是否可以重命名和修改表结构 |
| Show_db_priv | enum('N','Y') | NO | N | 是否可以查看服务器上所有数据库的名字，包括用户拥有足够访问权限的数据库 |
| Super_priv | enum('N','Y') | NO | N | 是否可以执行某些强大的管理功能，例如通过KILL命令删除用户进程；使用SET GLOBAL命令修改全局MySQL变量，执行关于复制和日志的各种命令。（超级权限） |
| Create_tmp_table_priv | enum('N','Y') | NO | N | 是否可以创建临时表 |
| Lock_tables_priv | enum('N','Y') | NO | N | 是否可以使用LOCK TABLES命令阻止对表的访问/修改 |
| Execute_priv | enum('N','Y') | NO | N | 是否可以执行存储过程 |
| Repl_slave_priv | enum('N','Y') | NO | N | 是否可以读取用于维护复制数据库环境的二进制日志文件 |
| Repl_client_priv | enum('N','Y') | NO | N | 是否可以确定复制从服务器和主服务器的位置 |
| Create_view_priv | enum('N','Y') | NO | N | 是否可以创建视图 |
| Show_view_priv | enum('N','Y') | NO | N | 是否可以查看视图 |
| Create_routine_priv | enum('N','Y') | NO | N | 是否可以更改或放弃存储过程和函数 |
| Alter_routine_priv | enum('N','Y') | NO | N | 是否可以修改或删除存储函数及函数 |
| Create_user_priv | enum('N','Y') | NO | N | 是否可以执行CREATE USER命令，这个命令用于创建新的MySQL账户 |
| Event_priv | enum('N','Y') | NO | N | 是否可以创建、修改和删除事件 |
| Trigger_priv | enum('N','Y') | NO | N | 是否可以创建和删除触发器 |
| Create_tablespace_priv | enum('N','Y') | NO | N | 是否可以创建表空间 |
如果要修改权限，可以使用 GRANT 语句为用户赋予一些权限，也可以通过 UPDATE 语句更新 user 表的方式来设置权限。
1.3、安全列
安全列主要用来判断用户是否能够登录成功。
| 字段名 | 字段类型 | 是否为空 | 默认值 | 说明 |
|---|---|---|---|---|
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | 支持ssl标准加密安全字段 | |
| ssl_cipher | blob | NO | 支持ssl标准加密安全字段 | |
| x509_issuer | blob | NO | 支持x509标准字段 | |
| x509_subject | blob | NO | 支持x509标准字段 | |
| plugin | char(64) | NO | mysql_native_password | 引入plugins以进行用户连接时的密码验证，plugin创建外部/代理用户 |
| password_expired | enum('N','Y') | NO | N | 密码是否过期 (N 未过期，y 已过期) |
| password_last_changed | timestamp | YES | 记录密码最近修改的时间 | |
| password_lifetime | smallint(5) unsigned | YES | 设置密码的有效时间，单位为天数 | |
| account_locked | enum('N','Y') | NO | N | 用户是否被锁定（Y 锁定，N 未锁定） |
注意：即使 password_expired 为“Y”，用户也可以使用密码登录 MySQL，但是不允许做任何操作。
通常标准的发行版不支持 ssl，读者可以使用 SHOW VARIABLES LIKE "have_openssl" 语句来查看是否具有 ssl 功能。如果 have_openssl 的值为 DISABLED，那么则不支持 ssl 加密功能。
1.4、资源控制列
资源控制列的字段用来限制用户使用的资源。
| 字段名 | 字段类型 | 是否为空 | 默认值 | 说明 |
|---|---|---|---|---|
| max_questions | int(11) unsigned | NO | 0 | 规定每小时允许执行查询的操作次数 |
| max_updates | int(11) unsigned | NO | 0 | 规定每小时允许执行更新的操作次数 |
| max_connections | int(11) unsigned | NO | 0 | 规定每小时允许执行的连接操作次数 |
| max_user_connections | int(11) unsigned | NO | 0 | 规定允许同时建立的连接次数 |
以上字段的默认值为 0，表示没有限制。一个小时内用户查询或者连接数量超过资源控制限制，用户将被锁定，直到下一个小时才可以在此执行对应的操作。可以使用 GRANT 语句更新这些字段的值。
2、MySQL创建用户
MySQL 在安装时，会默认创建一个名为 root 的用户，该用户拥有超级权限，可以控制整个 MySQL 服务器。
在对 MySQL 的日常管理和操作中，为了避免有人恶意使用 root 用户控制数据库，我们通常创建一些具有适当权限的用户，尽可能地不用或少用 root 用户登录系统，以此来确保数据的安全访问。
MySQL 提供了以下 3 种方法创建用户。
-
使用 CREATE USER 语句创建用户
-
在 mysql.user 表中添加用户
-
使用 GRANT 语句创建用户
2.1、使用 CREATE USER 语句创建用户
可以使用 CREATE USER 语句来创建 MySQL 用户，并设置相应的密码。其基本语法格式如下：
CREATE USER <用户> [ IDENTIFIED BY [ PASSWORD ] 'password' ] [ ,用户 [ IDENTIFIED BY [ PASSWORD ] 'password' ]]
2.1.1、用户
指定创建用户账号，格式为 user_name'@'host_name。这里的user_name是用户名，host_name为主机名，即用户连接 MySQL 时所用主机的名字。如果在创建的过程中，只给出了用户名，而没指定主机名，那么主机名默认为“%”，表示一组主机，即对所有主机开放权限。
2.1.2、IDENTIFIED BY子句
用于指定用户密码。新用户可以没有初始密码，若该用户不设密码，可省略此子句。
2.1.3、PASSWORD 'password'
PASSWORD 表示使用哈希值设置密码，该参数可选。如果密码是一个普通的字符串，则不需要使用 PASSWORD 关键字。
案例：
mysql> CREATE USER 'test1'@'localhost' IDENTIFIED BY 'test1';
Query OK, 0 rows affected (19.46 sec)
mysql> SELECT password('test1');
+-------------------------------------------+
| password('test1') |
+-------------------------------------------+
| *06C0BF5B64ECE2F648B5F048A71903906BA08E5C |
+-------------------------------------------+
1 row in set (0.06 sec)
"*06C0BF5B64ECE2F648B5F048A71903906BA08E5C"就是 test1 的哈希值。下面创建用户 test1，SQL 语句和执行过程如下：
mysql> CREATE USER 'test2'@'localhost'IDENTIFIED BY PASSWORD '*06C0BF5B64ECE2F648B5F048A71903906BA08E5C';
Query OK, 0 rows affected (0.04 sec)
2.2、使用 INSERT 语句新建用户
可以使用 INSERT 语句将用户的信息添加到 mysql.user 表中，但必须拥有对 mysql.user 表的 INSERT 权限。通常 INSERT 语句只添加 Host、User 和 authentication_string 这 3 个字段的值。
mysql> INSERT INTO mysql.user(Host, User, authentication_string, ssl_cipher, x509_issuer, x509_subject) VALUES ('localhost', 'test3', PASSWORD('test3'), '', '', '');
Query OK, 1 row affected, 1 warning (0.00 sec)
-- 使用test3登录MySQL
root@756ad135dd2e:/# mysql -utest3 -ptest3
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test3'@'localhost' (using password: YES)
-- 无法登录，使用INSERT创建的用户需要使用FLUSH PRIVILEGES;刷新权限。
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
mysql> exit
Bye
root@756ad135dd2e:/# mysql -utest3 -ptest3
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 61
Server version: 5.7.35 MySQL Community Server (GPL)
Copyright (c) 2000, 2021, 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.3、使用GRANT语句新建用户
虽然 CREATE USER 和 INSERT INTO 语句都可以创建普通用户，但是这两种方式不便授予用户权限。于是 MySQL 提供了 GRANT 语句。
GRANT priv_type ON database.table TO user [IDENTIFIED BY [PASSWORD] 'password']
其中：
-
priv_type 参数表示新用户的权限
-
database.table 参数表示新用户的权限范围，即只能在指定的数据库和表上使用自己的权限
-
user 参数指定新用户的账号，由用户名和主机名构成
-
IDENTIFIED BY 关键字用来设置密码
-
password 参数表示新用户的密码
mysql> GRANT SELECT ON *.* TO 'test4'@localhost IDENTIFIED BY 'test4';
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'test5'@localhost IDENTIFIED BY 'test5';
Query OK, 0 rows affected, 2 warnings (0.00 sec)
-- ALL PRIVILEGES : 所有的权限
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
3、MySQL修改用户
在 MySQL 中，我们可以使用 RENAME USER 语句修改一个或多个已经存在的用户账号。
语法格式如下：
RENAME USER <旧用户> TO <新用户>
其中：
- <旧用户>：系统中已经存在的 MySQL 用户账号。
- <新用户>：新的 MySQL 用户账号。
mysql> RENAME USER 'test5'@'localhost' TO 'test6'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT Host,User FROM mysql.user;
+-----------+---------------+
| Host | User |
+-----------+---------------+
| % | root |
| % | test6 |
+-----------+---------------+
9 rows in set (0.00 sec)
mysql> exit
Bye
root@756ad135dd2e:/# mysql -utest6 -ptest5
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 64
Server version: 5.7.35 MySQL Community Server (GPL)
Copyright (c) 2000, 2021, 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>
4、MySQL删除用户
在 MySQL 数据库中，可以使用 DROP USER 语句删除用户，也可以直接在 mysql.user 表中删除用户以及相关权限。
4.1、使用 DROP USER 语句删除普通用户
格式：
DROP USER <用户1> [ , <用户2> ]…
案例：
mysql> DROP USER test6;
Query OK, 0 rows affected (0.00 sec)
4.2、使用DELETE语句删除普通用户
可以使用 DELETE 语句直接删除 mysql.user 表中相应的用户信息，但必须拥有 mysql.user 表的 DELETE 权限。
格式：
DELETE FROM mysql.user WHERE Host='hostname' AND User='username';
Host 和 User 这两个字段都是 mysql.user 表的主键。因此，需要两个字段的值才能确定一条记录。
案例：
mysql> DELETE FROM mysql.user WHERE Host='localhost' AND User='test4';
Query OK, 1 row affected (0.00 sec)
5、MySQL查看权限
在 MySQL 中，可以通过查看 mysql.user 表中的数据记录来查看相应的用户权限，也可以使用 SHOW GRANTS 语句查询用户的权限。
格式：
SHOW GRANTS FOR 'username'@'hostname';
案例：
mysql> SHOW GRANTS FOR 'test3'@'localhost';
+-------------------------------------------+
| Grants for test3@localhost |
+-------------------------------------------+
| GRANT USAGE ON *.* TO 'test3'@'localhost' |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW GRANTS FOR 'root'@'localhost';
+---------------------------------------------------------------------+
| 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)
6、MySQL授权
授权就是为某个用户赋予某些权限。例如，可以为新建的用户赋予查询所有数据库和表的权限。MySQL 提供了 GRANT 语句来为用户设置权限。
在 MySQL 中，拥有 GRANT 权限的用户才可以执行 GRANT 语句，其语法格式如下：
GRANT priv_type [(column_list)] ON database.table
TO user [IDENTIFIED BY [PASSWORD] 'password']
[, user[IDENTIFIED BY [PASSWORD] 'password']] ...
[WITH with_option [with_option]...]
其中：
-
priv_type 参数表示权限类型；
-
columns_list 参数表示权限作用于哪些列上，省略该参数时，表示作用于整个表；
-
database.table 用于指定权限的级别；
-
user 参数表示用户账户，由用户名和主机名构成，格式是“'username'@'hostname'”；
-
IDENTIFIED BY 参数用来为用户设置密码；
-
password 参数是用户的新密码。
WITH 关键字后面带有一个或多个 with_option 参数。这个参数有 5 个选项，详细介绍如下：
-
GRANT OPTION：被授权的用户可以将这些权限赋予给别的用户；
-
MAX_QUERIES_PER_HOUR count：设置每个小时可以允许执行 count 次查询；
-
MAX_UPDATES_PER_HOUR count：设置每个小时可以允许执行 count 次更新；
-
MAX_CONNECTIONS_PER_HOUR count：设置每小时可以建立 count 个连接;
-
MAX_USER_CONNECTIONS count：设置单个用户可以同时具有的 count 个连接。
MySQL 中可以授予的权限有如下几组：
-
列权限，和表中的一个具体列相关。例如，可以使用 UPDATE 语句更新表 students 中 name 列的值的权限。
-
表权限，和一个具体表中的所有数据相关。例如，可以使用 SELECT 语句查询表 students 的所有数据的权限。
-
数据库权限，和一个具体的数据库中的所有表相关。例如，可以在已有的数据库 mytest 中创建新表的权限。
-
用户权限，和 MySQL 中所有的数据库相关。例如，可以删除已有的数据库或者创建一个新的数据库的权限。
对应地，在 GRANT 语句中可用于指定权限级别的值有以下几类格式：
-
*：表示当前数据库中的所有表。
-
.：表示所有数据库中的所有表。
-
db_name.*：表示某个数据库中的所有表，db_name 指定数据库名。
-
db_name.tbl_name：表示某个数据库中的某个表或视图，db_name 指定数据库名，tbl_name 指定表名或视图名。
6.1、权限类型
授予数据库权限时，<权限类型>可以指定为以下值：
| 权限名称 | 对应user表中的字段 | 说明 |
|---|---|---|
| SELECT | Select_priv | 表示授予用户可以使用 SELECT 语句访问特定数据库中所有表和视图的权限。 |
| INSERT | Insert_priv | 表示授予用户可以使用 INSERT 语句向特定数据库中所有表添加数据行的权限。 |
| DELETE | Delete_priv | 表示授予用户可以使用 DELETE 语句删除特定数据库中所有表的数据行的权限。 |
| UPDATE | Update_priv | 表示授予用户可以使用 UPDATE 语句更新特定数据库中所有数据表的值的权限。 |
| REFERENCES | References_priv | 表示授予用户可以创建指向特定的数据库中的表外键的权限。 |
| CREATE | Create_priv | 表示授权用户可以使用 CREATE TABLE 语句在特定数据库中创建新表的权限。 |
| ALTER | Alter_priv | 表示授予用户可以使用 ALTER TABLE 语句修改特定数据库中所有数据表的权限。 |
| SHOW VIEW | Show_view_priv | 表示授予用户可以查看特定数据库中已有视图的视图定义的权限。 |
| CREATE ROUTINE | Create_routine_priv | 表示授予用户可以为特定的数据库创建存储过程和存储函数的权限。 |
| ALTER ROUTINE | Alter_routine_priv | 表示授予用户可以更新和删除数据库中已有的存储过程和存储函数的权限。 |
| INDEX | Index_priv | 表示授予用户可以在特定数据库中的所有数据表上定义和删除索引的权限。 |
| DROP | Drop_priv | 表示授予用户可以删除特定数据库中所有表和视图的权限。 |
| CREATE TEMPORARY TABLES | Create_tmp_table_priv | 表示授予用户可以在特定数据库中创建临时表的权限。 |
| CREATE VIEW | Create_view_priv | 表示授予用户可以在特定数据库中创建新的视图的权限。 |
| EXECUTE ROUTINE | Execute_priv | 表示授予用户可以调用特定数据库的存储过程和存储函数的权限。 |
| LOCK TABLES | Lock_tables_priv | 表示授予用户可以锁定特定数据库的已有数据表的权限。 |
| ALL 或 ALL PRIVILEGES 或 SUPER | Super_priv | 表示以上所有权限/超级权限 |
6.2、用户授权
使用 GRANT 语句创建一个新的用户 test7，密码为 test7。用户 testUser 对所有的数据有查询、插入权限，并授予 GRANT 权限。SQL 语句和执行过程如下。
mysql> GRANT SELECT,INSERT ON *.* TO 'test7'@'localhost' IDENTIFIED BY 'test7' WITH GRANT OPTION;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> SHOW GRANTS FOR 'test7'@'localhost';
+----------------------------------------------------------------------+
| Grants for test7@localhost |
+----------------------------------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO 'test7'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------+
1 row in set (0.00 sec)
注：常用的权限
SELECT, SHOW VIEW ： 数据库可读权限。
6.3、删除用户权限
在 MySQL 中，可以使用 REVOKE 语句删除某个用户的某些权限（此用户不会被删除），在一定程度上可以保证系统的安全性。例如，如果数据库管理员觉得某个用户不应该拥有 DELETE 权限，那么就可以删除 DELETE 权限。
6.3.1、删除用户某些特定的权限
格式：
REVOKE priv_type [(column_list)]...
ON database.table
FROM user [, user]...
REVOKE 语句中的参数与 GRANT 语句的参数意思相同。其中：
-
priv_type 参数表示权限的类型
-
column_list 参数表示权限作用于哪些列上，没有该参数时作用于整个表上
-
user 参数由用户名和主机名构成，格式为'username'@'hostname'
6.3.2、删除特定用户的所有权限
格式：
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...
删除用户权限需要注意以下几点：
- REVOKE 语法和 GRANT 语句的语法格式相似，但具有相反的效果。
- 要使用 REVOKE 语句，必须拥有 MySQL 数据库的全局 CREATE USER 权限或 UPDATE 权限。
mysql> REVOKE INSERT ON *.* FROM 'test7'@'localhost';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SHOW GRANTS FOR 'test7'@'localhost';
+--------------------------------------------------------------+
| Grants for test7@localhost |
+--------------------------------------------------------------+
| GRANT SELECT ON *.* TO 'test7'@'localhost' WITH GRANT OPTION |
+--------------------------------------------------------------+
1 row in set (0.00 sec)
7、MySQL登录和退出
启动 MySQL 服务后，可以使用以下命令来登录。
mysql -h hostname|hostlP -p port -u username -p DatabaseName -e "SQL语句"
-- 退出
exit
quit
\q
对上述参数说明如下：
-
-h：指定连接 MySQL 服务器的地址。可以用两种方式表示，hostname 为主机名，hostIP 为主机 IP 地址。
-
-p：指定连接 MySQL 服务器的端口号，port 为连接的端口号。MySQL 的默认端口号是 3306，因此如果不指定该参数，默认使用 3306 连接 MySQL 服务器。
-
-u：指定连接 MySQL 服务器的用户名，username 为用户名。
-
-p：提示输入密码，即提示 Enter password。
-
DatabaseName：指定连接到 MySQL 服务器后，登录到哪一个数据库中。如果没有指定，默认为 mysql 数据库。
-
-e：指定需要执行的 SQL 语句，登录 MySQL 服务器后执行这个 SQL 语句，然后退出 MySQL 服务器。
root@756ad135dd2e:/# mysql -uroot -p123456 -e "DESC db1.db01";
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| name | varchar(20) | YES | | NULL | |
| id | int(11) | NO | PRI | NULL | auto_increment |
| sex | varchar(20) | YES | | NULL | |
| addr | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
8、修改用户密码
在 MySQL 中，root 用户拥有很高的权限，不仅可以修改自己的密码，还可以修改其他用户的密码。
8.1、使用SET语句修改普通用户的密码
在 MySQL 中，只有 root 用户可以通过更新 MySQL 数据库来更改密码。使用 root 用户登录到 MySQL 服务器后，可以使用 SET 语句来修改普通用户密码。
mysql> SET PASSWORD FOR 'test7'@'localhost' = PASSWORD ('test07');
Query OK, 0 rows affected, 1 warning (0.00 sec)
8.2、使用UPDATE语句修改普通用户的密码
使用 root 用户登录 MySQL 服务器后，可以使用 UPDATE 语句修改 MySQL 数据库的 user 表的 authentication_string 字段，从而修改普通用户的密码。
mysql> UPDATE mysql.user SET authentication_string = PASSWORD("test007") WHERE User = "test7" AND Host = "localhost";
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
8.3、使用 GRANT 语句修改普通用户密码
除了前面介绍的方法，还可以在全局级别使用 GRANT USAGE 语句指定某个账户的密码而不影响账户当前的权限。需要注意的是，使用 GRANT 语句修改密码，必须拥有 GRANT 权限。一般情况下最好使用该方法来指定或修改密码。
mysql> GRANT USAGE ON *.* TO 'test7'@'localhost' IDENTIFIED BY 'test0007';
Query OK, 0 rows affected, 2 warnings (0.00 sec)
8.4、使用mysqladmin命令在命令行修改root密码
root 用户可以使用 mysqladmin 命令来修改密码。
mysqladmin -u username -h hostname -p password "newpwd"
语法参数说明如下：
-
usermame 指需要修改密码的用户名称，在这里指定为 root 用户；
-
hostname 指需要修改密码的用户主机名，该参数可以不写，默认是 localhost；
-
password 为关键字，而不是指旧密码；
-
newpwd 为新设置的密码，必须用双引号括起来。如果使用单引号会引发错误，可能会造成修改后的密码不是你想要的。
root@756ad135dd2e:/# mysqladmin -uroot -p123456 password test00007
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
root@756ad135dd2e:/# mysql -uroot -ptest00007;
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 87
Server version: 5.7.35 MySQL Community Server (GPL)
Copyright (c) 2000, 2021, 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>
8.5、修改MySQL数据库的user表
因为所有账户信息都保存在 user 表中，因此可以直接通过修改 user 表来改变 root 用户的密码。
mysql> UPDATE mysql.user set authentication_string = PASSWORD ("123456") WHERE User = "root" and Host="localhost";
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 1
-- 结果显示，密码修改成功。而且使用了FLUSH PRIVILEGES;语句加载权限。退出后就必须使用新密码来登录了。
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
root@756ad135dd2e:/# mysql -uroot -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 90
Server version: 5.7.35 MySQL Community Server (GPL)
Copyright (c) 2000, 2021, 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>
8.6、使用SET语句修改root用户的密码
SET PASSWORD 语句可以用来重新设置其他用户的登录密码或者自己使用的账户的密码。
mysql> SET PASSWORD = PASSWORD("test07");
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> exit
Bye
root@756ad135dd2e:/# mysql -uroot -ptest07;
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 91
Server version: 5.7.35 MySQL Community Server (GPL)
Copyright (c) 2000, 2021, 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>
8.7、root密码忘记了，如何重置？
当我们忘记用户密码时，我们可以通过跳过授权表的方式重置root密码。
root@756ad135dd2e:/# cat /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
skip_grant_tables
root@756ad135dd2e:/# mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.35 MySQL Community Server (GPL)
Copyright (c) 2000, 2021, 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> UPDATE mysql.user set authentication_string = PASSWORD ("123456") WHERE User = "root" and Host="localhost";
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
-- 删除skip_grant_tables
root@756ad135dd2e:/# mysql -uroot -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 4
Server version: 5.7.35 MySQL Community Server (GPL)
Copyright (c) 2000, 2021, 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>
案例：创建用户test01,只给可查和可写权限。
mysql> GRANT SELECT, SHOW VIEW ON *.* TO test001@"%" IDENTIFIED BY "test001";
Query OK, 0 rows affected, 1 warning (0.05 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
案例：创建用户test02,只给可写权限。
mysql> GRANT INSERT ON *.* TO test02@"%" IDENTIFIED BY "test02";
Query OK, 0 rows affected, 1 warning (0.05 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
