MySQL 账号权限

客官°小女子只卖身不卖艺 2024-03-23 22:53 76阅读 0赞

mysql 在安装好后，默认是没有远端管理账号。

#### 一、账号管理 ####

#### 1. 查看账号列表 ####

MySQL用户账号和信息存储在名为 `mysql` 的数据库中。一般不需要直接访问 `mysql` 数据库和表，但有时需要直接访问。例如，查看数据库所有用户账号列表时。

USE mysql;
    SELECT DISTINCT(`user`) FROM user;

*  数据库 `mysql` 有一个名为 `user` 的表，它包含所有用户账号。 `user` 表有一个名为 `user` 的字段，它存储账号名。
 *  进入数据库 `mysql`，查看 `user` 表中的 `user` 列，由于有些账号会分多行记录，`DISTINCT` 用于去重。

*  user字段 表示账号名，表示当前数据库有 `root` 和 `mysql.sys` 两个账号。

#### 2. 创建账号 ####

可以使用 `CREATE USER` 语句创建一个新用户账号。

CREATE USER account_name IDENTIFIED BY 'password';

*  `IDENTIFIED BY` 用于设定密码，MySQL 会先将密码进行加密，在将其保存到 user 表。

> 使用 `GRANT` 或 `INSERT GRANT` 语句也可以创建用户账号，但一般来说 `CREATE USER` 是最清楚和最简单的句子。  
> 使用 `CREATE USER` 创建用户账号，必须接着分配访问权限。新创建的用户账号没有访问权限。它们能登录MySQL，但不能看到数据，不能执行任何数据库操作。  
> 可以使用 `GRANT` 语句创建用户账号并授权，该语句会在文章授权部分讲解。用于账号都存储在数据库 `mysql` 的 `user` 表中，理论上也可以通过直接插入行到 user 表来增加用户，不过为安全起见，一般不建议这样做。MySQL用来存储用户账号信息的表（以及表模式等）极为重要，对它们的任何毁坏都可能严重地伤害到MySQL服务器。因此，最好不要直接修改数据库 `mysql` 中表的数据。

*  为数据库添加账号 `zhangsan` 密码为 `123456`

*  使用 zhangsan 的账号和密码登录，成功登录 MySQL。

[vagrant~] ]$mysql -uzhangsan -p
    Enter password:
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    ......
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql>

> 注意，不要直接在命令行中输入密码，因为命令行的输入历史都会被记录下来，很同意导致密码泄露。

#### 3. 账号重命名 ####

可以使用 `RENAME USER` 语句为账号重命名。

RENAME USER old_name TO new_name;

> 仅 MySQL 5及之后的版本支持 `RENAME USER`。  
> MySQL 5以前的版本，要重命名一个用户，可使用 UPDATE 直接更新 user 表（谨慎操作）。

*  将数据库账号 `zhangsan` 重命名为 `lisi`

#### 4. 重置账号密码 ####

可以使用 `SET PASSWORD` 语句重置账号密码。

SET PASSWORD FOR account_name = Password('password');

> 使用 `SET PASSWORD` 重置账号密码。新密码必须通过 Password() 函数进行加密。

当不指定用户名时， `SET PASSWORD` 会重置当前登录用户的密码。

SET PASSWORD = Password('password');

*  将账号 `lisi` 的密码改为 `abcdef`

mysql> SET PASSWORD FOR lisi = Password('abcdef');
    Query OK, 0 rows affected (0.03 sec)

*  使用 lisi 的账号和新密码登录，成功登录 MySQL。

*  将当前登录账号的密码重置为 10086

mysql> SET PASSWORD = Password('10086');
    Query OK, 0 rows affected (0.00 sec)

#### 5. 删除账号 ####

可以使用 `DROP USER` 语句删除账号（以及相关的权限）。

DROP USER account_name;

> MySQL 5及之后的版本， `DROP USER` 删除用户账号时会自动删除所有相关的账号权限。  
> 在MySQL 5以前， `DROP USER` 只能用来删除用户账号，不能删除相关的权限。因此，如果使用旧版本的MySQL，需要先用 `REVOKE` 删除与账号相关的权限，然后再用 `DROP USER` 删除账号。

*  删除账号 `lisi`

#### 二、权限管理 ####

#### 访问控制 ####

MySQL服务器的安全基础是：用户应该对他们需要的数据具有适当的访问权，既不能多也不能少。

考虑以下情况：

*  多数用户只需要对表进行读和写，但少数用户甚至需要能创建和删除表；
 *  某些用户需要读表，但可能不需要更新表；
 *  你可能想允许用户添加数据，但不允许他们删除数据；
 *  某些用户（管理员）可能需要处理用户账号的权限，但多数用户不需要；
 *  你可能想让用户通过存储过程访问数据，但不允许他们直接访问数据；
 *  你可能想根据用户登录的地点限制对某些功能的访问。

这些都只是例子，但有助于说明一个重要的事实，即你需要给用户提供他们所需的访问权，且仅提供他们所需的访问权。这就是所谓的访问控制，管理访问控制需要创建和管理用户账号。

**严肃对待 root 账号的使用**

MySQL 会默认创建一个名为 `root` 的用户账号，它对整个 MySQL 服务器具有完全的控制。不过在日常的 MySQL 操作中（特别是生产环境），决不能使用 root 账号登录。应该创建一系列的账号，有的用于管理，有的供用户使用，有的供开发人员使用，等等。应该严肃对待 root 账号的使用，仅在绝对需要时使用它。

**访问控制的目的**

*  防止用户的恶意企图。
 *  防止用户无意识错误造成数据错乱。这是更为常见的情况。如错打 SQL 语句，在不合适的数据库中操作或其他一些用户错误。

通过保证用户不能执行他们不应该执行的语句，访问控制有助于避免这些情况的发生。

#### 查看账号权限 ####

SHOW GRANTS[ FOR account_name][@host];

*  当不使用 `FOR` 指定用户时，默认是查看自己的账号权限。
 *  当使用 `SHOW GRANTS FOR account_name@host` 时，可查看指定账号在指定主机下的权限。可以在数据库 `mysql` 中使用 `SELECT user,host FROM user;` 查看账号的主机列表。

host字段： 表示账号可以在哪些主机或IP地址登录。`%` 代表任何IP地址都可以登录（生产环境中这样做是非常危险的），`localhost` 表示只允许本机登录。

> 将 `host` 设为 `%`，就代表任何IP地址都可以访问该数据库，在生产环境中这样做是非常危险的。  
> 也可以使用其他手段来提高数据库安全性：比如设置防火墙、iptable；如果是云平台的数据库还可以通过安全组等方式提高安全性。

*  查看自己的账号（root）权限。

mysql> SHOW GRANTS;
    +-------------------------------------------------------------+
    | Grants for root@%                                           |
    +-------------------------------------------------------------+
    | GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
    +-------------------------------------------------------------+
    1 row in set (0.00 sec)

*  查看账号 root 在 localhost 下的权限。

mysql> SHOW GRANTS FOR root@localhost;
    +---------------------------------------------------------------------+
    | Grants for root@localhost                                           |
    +---------------------------------------------------------------------+
    | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
    +---------------------------------------------------------------------+
    1 row in set (0.00 sec)

> 输出结果显示账号 `root` 有一个权限 `ALL PRIVILEGES ON *.*`，表示 `root` 账号可以操作所有数据库和所有表。

*  使用 `CREATE USER` 创建一个账号 `zhangsan`，并查看 `zhangsan` 的账号权限。

mysql> CREATE USER zhangsan IDENTIFIED BY '123456';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SHOW GRANTS FOR zhangsan;
    +---------------------------------------------------------------------------------------------------------+
    | Grants for zhangsan@%                                                                                   |
    +---------------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'zhangsan'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
    +---------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

> 输出结果显示账号 `zhangsan` 有一个权限 `USAGE ON *.*` 。 USAGE 表示根本没有权限，所以，此结果表示 `zhangsan` 对任意数据库和任意表上对任何东西都没有操作权限。

*  登录账号 `zhangsan`，并尝试进入 `test` 数据库，被拒绝。

> 使用 `CREATE USER` 创建的用户账号默认没有访问权限。它们能登录MySQL，但不能看到数据，不能执行任何数据库操作。

#### 为已存在的账号设置权限 ####

可以使用 `GRANT` 语句为账号设置权限。至少给出以下信息：

*  账号名。
 *  被授予访问权限的数据库或表。
 *  要授予的权限。

GRANT <权限> ON <数据库名>.<表名> TO <账户名>;

*  给账号 `zhangsan` 赋予在 `test` 数据库内的任意表查找和添加数据的权限。

mysql> GRANT SELECT, INSERT ON test.* TO 'zhangsan';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SHOW GRANTS FOR zhangsan;
    +---------------------------------------------------------------------------------------------------------+
    | Grants for zhangsan@%                                                                                   |
    +---------------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'zhangsan'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
    | GRANT SELECT, INSERT ON `test`.* TO 'zhangsan'@'%'                                                      |
    +---------------------------------------------------------------------------------------------------------+
    2 rows in set (0.00 sec)

> 授权后必须FLUSH PRIVILEGES，否则无法立即生效。

*  登录账号 `zhangsan`，可以成功进入到数据库 `test`，在 `user` 表中插入一条数据，并从 `user` 表查找数据。

*  当 `zhangsan` 想要使用 `UPDATE` 和 `DELETE` 命令修改和删除这条数据时，被提示没有权限。

mysql> UPDATE user SET email='zhangsanfeng@gmail.com' WHERE username='zhangsan';
    ERROR 1142 (42000): UPDATE command denied to user 'zhangsan'@'localhost' for table 'user'
    
    mysql> DELETE FROM user WHERE username='zhangsan';
    ERROR 1142 (42000): DELETE command denied to user 'zhangsan'@'localhost' for table 'user'

#### 创建账号并设置权限 ####

GRANT <权限> ON <数据库名>.<表名> TO <账户名>@<主机名/IP> IDENTIFIED BY '<密码>'[ WITH GRANT OPTION];

> `WITH GRANT OPTION` 用于赋予账号使用 `GRANT` 和 `REVOKE` 命令的权限，用于给账号授权和取消授权。此权限级别极高，一般只会将此权限授予数据库管理员账号。

*  创建账号 `lisi` 密码为 `abcdef`，在任何IP地址都可以登录，同时赋予 `lisi` 在 `test` 数据库内的任意表数据的增删改查权限。

mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON test.* TO 'lisi'@'%' IDENTIFIED BY 'abcdef';
    Query OK, 0 rows affected (0.00 sec)

*  登录账号 `lisi`，可以成功进入到数据库 `test`，并对 `user` 表数据进行增删改查。

#### 撤销账号指定权限 ####

可以使用 `REVOKE` 语句撤销账号指定权限。`REVOKE` 是 `GRANT` 的反操作。

REVOKE <权限> ON <数据库名>.<表名> FROM <账户名>;

*  删除账号 `lisi` 的 DELETE 权限。

mysql> REVOKE DELETE ON test.* FROM lisi;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SHOW GRANTS FOR lisi;
    +-----------------------------------------------------------------------------------------------------+
    | Grants for lisi@%                                                                                   |
    +-----------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'lisi'@'%' IDENTIFIED BY PASSWORD '*C2D24DCA38E9E862098B85BF0AB35CAA52803797' |
    | GRANT SELECT, INSERT, UPDATE ON `test`.* TO 'lisi'@'%'                                              |
    +-----------------------------------------------------------------------------------------------------+
    2 rows in set (0.04 sec)

*  当 `lisi` 想要使用 `DELETE` 命令删除数据时，被提示没有权限。

mysql> DELETE FROM user WHERE username='lisi';
    ERROR 1142 (42000): DELETE command denied to user 'lisi'@'localhost' for table 'user'

#### 权限说明 ####

`GRANT` 和 `REVOKE` 可在几个层次上控制访问权限：

*  整个服务器，使用 `GRANT ALL` 和 `REVOKE ALL`；
 *  整个数据库，使用 `ON database.*`；
 *  特定的表，使用 `ON database.table`；
 *  特定的列；
 *  特定的存储过程。

<table> 
 <tbody> 
  <tr> 
   <th>权 限</th> 
   <th>说 明</th> 
  </tr> 
  <tr> 
   <td>ALL</td> 
   <td>除 GRANT OPTION 外的所有权限</td> 
  </tr> 
  <tr> 
   <td>ALTER</td> 
   <td>使用 ALTER TABLE</td> 
  </tr> 
  <tr> 
   <td>ALTER ROUTINE</td> 
   <td>使用 ALTER PROCEDURE 和 DROP PROCEDURE</td> 
  </tr> 
  <tr> 
   <td>CREATE</td> 
   <td>使用 CREATE TABLE</td> 
  </tr> 
  <tr> 
   <td>CREATE ROUTINE</td> 
   <td>使用 CREATE PROCEDURE</td> 
  </tr> 
  <tr> 
   <td>CREATE TEMPORARY TABLES</td> 
   <td>使用 CREATE TEMPORARY TABLE</td> 
  </tr> 
  <tr> 
   <td>CREATE USER</td> 
   <td>使用 CREATE USER、DROP USER、RENAME USER 和 REVOKE ALL PRIVILEGES</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>使用 CREATE VIEW</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>使用 DELETE</td> 
  </tr> 
  <tr> 
   <td>CREATE VIEW</td> 
   <td>使用 DROP TABLE</td> 
  </tr> 
  <tr> 
   <td>DELETE</td> 
   <td>使用 CALL 和存储过程</td> 
  </tr> 
  <tr> 
   <td>DROP</td> 
   <td>使用 SELECT INTO OUTFILE 和 LOAD DATA INFILE</td> 
  </tr> 
  <tr> 
   <td>EXECUTE</td> 
   <td>使用 GRANT 和 REVOKE</td> 
  </tr> 
  <tr> 
   <td>FILE</td> 
   <td>使用 CREATE INDEX 和 DROP INDEX</td> 
  </tr> 
  <tr> 
   <td>GRANT OPTION</td> 
   <td>使用 INSERT</td> 
  </tr> 
  <tr> 
   <td>INDEX</td> 
   <td>使用 LOCK TABLES</td> 
  </tr> 
  <tr> 
   <td>INSERT</td> 
   <td>使用 SHOW FULL PROCESSLIST</td> 
  </tr> 
  <tr> 
   <td>LOCK TABLES</td> 
   <td>使用 FLUSH</td> 
  </tr> 
  <tr> 
   <td>PROCESS</td> 
   <td>服务器位置的访问</td> 
  </tr> 
  <tr> 
   <td>RELOAD</td> 
   <td>由复制从属使用</td> 
  </tr> 
  <tr> 
   <td>REPLICATION CLIENT</td> 
   <td>使用 SELECT</td> 
  </tr> 
  <tr> 
   <td>REPLICATION SLAVE</td> 
   <td>使用 SHOW DATABASES</td> 
  </tr> 
  <tr> 
   <td>SELECT</td> 
   <td>使用 SHOW CREATE VIEW</td> 
  </tr> 
  <tr> 
   <td>SHUTDOWN</td> 
   <td>使用 mysqladmin shutdown（用来关闭MySQL）</td> 
  </tr> 
  <tr> 
   <td>SUPER</td> 
   <td>使用 CHANGE MASTER、KILL、LOGS、PURGE、MASTER 和 SET GLOBAL。还允许 mysqladmin 调试登录</td> 
  </tr> 
  <tr> 
   <td>UPDATE</td> 
   <td>使用 UPDATE</td> 
  </tr> 
  <tr> 
   <td>USAGE</td> 
   <td>无访问权限</td> 
  </tr> 
 </tbody> 
</table>

#### 权限控制流程 ####

最后附一张《MySQL性能调优与架构设计》中的权限控制流程图。

以 `SELECT id,name FROM test.t4 where status = 'deleted';` 为例。

![d3fa5e4e9d56473d8077126de2161874.png][]

[d3fa5e4e9d56473d8077126de2161874.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/133ddf45692c43a9b4aaee0e68de7203.png