第13章 MySQL用户管理

13.1 权限表
13.1.1 user表

mysql> DESC mysql.user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| User                   | char(32)                          | NO   | PRI |                       |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| File_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| 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                     |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N                     |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| 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                     |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N                     |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |                       |       |
| ssl_cipher             | blob                              | NO   |     | NULL                  |       |
| x509_issuer            | blob                              | NO   |     | NULL                  |       |
| x509_subject           | blob                              | NO   |     | NULL                  |       |
| 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                     |       |
| plugin                 | char(64)                          | NO   |     | mysql_native_password |       |
| authentication_string  | text                              | YES  |     | NULL                  |       |
| password_expired       | enum('N','Y')                     | NO   |     | N                     |       |
| password_last_changed  | timestamp                         | YES  |     | NULL                  |       |
| password_lifetime      | smallint(5) unsigned              | YES  |     | NULL                  |       |
| account_locked         | enum('N','Y')                     | NO   |     | N                     |       |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)

13.2 账户管理
1.登录和退出MySQL服务器
例：使用root用户登录到本地MySQL服务器

C:\Users\lenovo>MySQL -uroot -p -hlocalhost test_db
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.7.14 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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.

例：使用root用户登录到本地MySQL服务器的test_db数据库中，同时执行一条查询语句。命令如下：

C:\Users\lenovo>MySQL -uroot -p -hlocalhost test_db -e "DESC  employee;"
Enter password: ******
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| e_no     | int(11)      | NO   | PRI | NULL    |       |
| e_name   | varchar(100) | NO   |     | NULL    |       |
| e_gender | char(2)      | NO   |     | NULL    |       |
| dept_no  | int(11)      | NO   | MUL | NULL    |       |
| e_job    | varchar(100) | NO   |     | NULL    |       |
| e_salary | smallint(6)  | NO   |     | NULL    |       |
| hireDate | date         | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+

13.2.2 新建普通用户

1. 使用CREATE USER语句创建新用户

mysql> CREATE USER 'feffrey'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.06 sec)

mysql> SELECT password('123456');
+-------------------------------------------+
| password('123456')                        |
+-------------------------------------------+
| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------------------------------------+
1 row in set, 1 warning (0.03 sec)

2. 使用GRANT语句创建新用户

mysql> GRANT SELECT,UPDATE ON *.*TO 'testUser'@'localhost'
    -> IDENTIFIED  BY 'testpwd';
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> SELECT Host,User,Select_priv FROM mysql.user WHERE user='testUser';
+-----------+----------+-------------+
| Host      | User     | Select_priv |
+-----------+----------+-------------+
| localhost | testUser | Y           |
+-----------+----------+-------------+
1 row in set (0.00 sec)

3. 直接操作MySQL用户表

使用INSERT语句创建新用户：

INSERT INTO mysql.user(Host,User,Passeord,[privilegelist])
VALUES('host','username',PASSWORD('password'),privilegevaluelist);

FLUSH PRIVILEGES：重新加载授权表。

13.2.3 删除普通用户

1. 使用DROP USER
   句法：DROP USER user[,user];
   DROP USER语句可用于删除一个或多个MySQL账户。
2. 使用DELETE语句删除用户
   句法：
   DELETE FROM MySQL.user WHERE host=‘hostname’ and user=‘username’；

13.2.4 root用户修改自己的密码

1. 使用mysqladmin命令在命令行指定新密码

C:\Users\lenovo>mysqladmin -u root -p password "654321"
Enter password: ******
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.

C:\Users\lenovo>mysql -u root -p
Enter password: ******
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

C:\Users\lenovo>mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 30
Server version: 5.7.14 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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.

2. 修改mysql数据库的user表

mysql> UPDATE mysql.user set Password=password("rootpw2")
    -> WHERE User="root" and Host="localhost";

3. 使用SET语句修改root用户的密码

mysql> SET PASSWORD = password("rootpwd3");
Query OK, 0 rows affected, 1 warning (0.10 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.18 sec)

13.2.5 root用户修改普通密码

1. 使用SET语句修改普通用户的密码

mysql> SET PASSWORD FOR 'testUser'@'localhost'=PASSWORD("newpwd");
Query OK, 0 rows affected, 1 warning (0.00 sec)

testUser用户的密码被成功设置为newpwd。

2. 使用UPDATE语句修改普通用户的密码

mysql> UPDATE MySQL.user SET Password = PASSWORD("pwd")
    -> WHERE USER="testUSER" AND host="localhost";

3. 使用GRANT语句修改普通用户密码

mysql> GRANT USAGE ON *.* TO 'testUser'@'localhost' IDENTIFIED BY 'newpwd3';
Query OK, 0 rows affected, 1 warning (0.00 sec)

13.2.6 普通用户修改密码

mysql> SET PASSWORD=PASSWORD("NEWPASSWORD");
Query OK, 0 rows affected, 1 warning (0.00 sec)

13.2.7 root用户密码丢失的解决办法

1. 使用–skip-grant-tables选项启动MySQL服务

mysqld --skip-grant-tables

mysqld-nt --skip-grant-tables

3. 加载权限表
   修改密码完成后，必须使用 FLUSH PRIVILEGES语句加载权限表。加载权限表后，新的密码才会生效，同时MySQL服务器开始验证。输入语句如下：

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.14 sec)

13.3 权限管理
13.3.2 授权

1. 全局层级
   全局权限适用于一个给定服务器中的所有数据库。这些权限在mysql.user表中。GRANT ALL ON * . *只授予和撤销全局权限。
2. 数据库层级
   数据库权限适用于一个给定数据库中所有目标。这些权限仅在mysql.db和mysql.host表中。GRANT ALL ON db_name.和REVOKE ALL ON db_name.* 只授予和撤销表权限。
3. 表层级
   表权限适用于一个给定表中的所有列。这些权限存储在mysql.tables_priv表中。GRANT ALL ON db_name.tb1_name和REVOKE ALL ON db_name.tb1_name只授予和撤销表权限。
4. 列层级
   列权限适用于一个给定表中的单一列。这些权限存储在mysql.columns_priv表中。当使用REVOKE时，必须指定与被授权列相同的列。
   13.3.3 收回权限
   例：使用REVOKE语句取消用户testUser的更新权限：

REVOKE UPDATE ON *.* FROM ‘testUser’@‘localhost’；

13.3.4 查看权限

mysql> SHOW GRANTS FOR 'testUser'@'localhost';
+-------------------------------------------------------+
| Grants for testUser@localhost                         |
+-------------------------------------------------------+
| GRANT SELECT, UPDATE ON *.* TO 'testUser'@'localhost' |
+-------------------------------------------------------+
1 row in set (0.05 sec)