MySQL加载数据所需要的file权限实验

- 日理万妓 2022-08-03 11:53 148阅读 0赞

在MySQL中经常需要导出一些表的数据，或者加载数据到某些表中，这时就涉及到一些权限的问题。今天就遇到一个开发加载数据的问题，我在测试环境进行了一些测试，并得出了一结论，过程和结果如下：

在测试环境中，创建一个测试用户，然后以root身份 执行导入导出过程，导出导出过程正常：

按照上面的方式授权后，用新授权的用户进行数据导入和导出会出现错误：

$ mysql -umis_lda -pfPUw2TgI  -h 192.168.226.121 -P 3306 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 3
    Server version: 5.5.19-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2013, 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> 
    mysql> 
    mysql> 
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | test               |
    +--------------------+
    2 rows in set (0.00 sec)
    
    mysql> 
    mysql> 
    mysql> test;
    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 'test' at line 1
    mysql> use test;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    mysql> 
    mysql> show tables;
    +-----------------------------------+
    | Tables_in_test                    |
    +-----------------------------------+
    | department                        |
    | networkspeed_day_isp_r            |
    | networkspeed_day_province_isp_r   |
    | networkspeed_day_province_r       |
    | networkspeed_day_system_ip        |
    | networkspeed_month_isp_r          |
    | networkspeed_month_province_isp_r |
    | networkspeed_month_province_r     |
    | networkspeed_month_system_ip      |
    | networkspeed_week_isp_r           |
    | networkspeed_week_province_isp_r  |
    | networkspeed_week_province_r      |
    | networkspeed_week_system_ip       |
    | privilege                         |
    | province_r                        |
    | province_test                     |
    | role                              |
    | role_privilege                    |
    | user                              |
    | user_role                         |
    +-----------------------------------+
    20 rows in set (0.00 sec)
    
    mysql> 
    mysql> 
    mysql> 
    mysql> 
    mysql> show tables;
    +-----------------------------------+
    | Tables_in_test                    |
    +-----------------------------------+
    | department                        |
    | networkspeed_day_isp_r            |
    | networkspeed_day_province_isp_r   |
    | networkspeed_day_province_r       |
    | networkspeed_day_system_ip        |
    | networkspeed_month_isp_r          |
    | networkspeed_month_province_isp_r |
    | networkspeed_month_province_r     |
    | networkspeed_month_system_ip      |
    | networkspeed_week_isp_r           |
    | networkspeed_week_province_isp_r  |
    | networkspeed_week_province_r      |
    | networkspeed_week_system_ip       |
    | privilege                         |
    | province_r                        |
    | province_test                     |
    | role                              |
    | role_privilege                    |
    | test01                            |
    | user                              |
    | user_role                         |
    +-----------------------------------+
    21 rows in set (0.00 sec)
    
    mysql> 
    mysql> 
    mysql> 
    mysql> create test02 as select * from test01 where 1=0;
    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 'test02 as select * from test01 where 1=0' at line 1
    mysql> create table test02 select * from test01 where 1=0;
    Query OK, 0 rows affected (0.04 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> 
    mysql> 
    mysql> select * from test02;
    Empty set (0.00 sec)
    
    mysql> 
    mysql> 
    mysql> load data infile '/tmp/test01.txt' into table test02;
    ERROR 1045 (28000): Access denied for user 'mis_lda'@'192.168.226.121' (using password: YES)
    mysql> 
    mysql> select * from test02;
    Empty set (0.00 sec)
    
    mysql> 
    mysql> 
    mysql> 
    mysql> load data infile '/tmp/test01.txt' into table test02;
    ERROR 1045 (28000): Access denied for user 'mis_lda'@'192.168.226.121' (using password: YES)
    mysql> 
    mysql> exit
    Bye

提示error 1045错误，一般是用户名和密码有问题的时候，会出现该错误，但此时已经建立正常连接，并且可以正常查询，肯定不是用户名和密码问题。

于是经过思考后，在root用户下，对新用户授予file权限，但授权方式和显示还是比较特殊的：

mysql> 
    mysql> show grants for mis_lda@'192.168.226.121';   
    +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Grants for mis_lda@192.168.226.121                                                                                                                                          |
    +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'mis_lda'@'192.168.226.121' IDENTIFIED BY PASSWORD '*24B612AA4FC098916383320D28EF01789ADEE21F'                                                        |
    | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON `test`.* TO 'mis_lda'@'192.168.226.121' |
    +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    2 rows in set (0.01 sec)
    
    mysql> 
    mysql> grant file ON `test`.* TO 'mis_lda'@'192.168.226.121';
    ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
    mysql> 
    mysql> 
    mysql> grant file on *.* to 'mis_lda'@'192.168.226.121';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> 
    mysql> 
    mysql> 
    mysql> flush privileges;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> 
    mysql> 
    mysql> show grants for mis_lda@'192.168.226.121';            
    +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Grants for mis_lda@192.168.226.121                                                                                                                                          |
    +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | GRANT FILE ON *.* TO 'mis_lda'@'192.168.226.121' IDENTIFIED BY PASSWORD '*24B612AA4FC098916383320D28EF01789ADEE21F'                                                         |
    | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON `test`.* TO 'mis_lda'@'192.168.226.121' |
    +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    2 rows in set (0.00 sec)

此时授权完毕后，再次用新用户进行数据导入导出，就可以正常执行了：

$ mysql -umis_lda -pfPUw2TgI  -h 192.168.226.121 -P 3306 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 4
    Server version: 5.5.19-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2013, 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> 
    mysql> use test;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    mysql> 
    mysql> select * from test02;
    Empty set (0.00 sec)
    
    mysql> load data infile '/tmp/test01.txt' into table test02;
    Query OK, 10 rows affected (0.01 sec)
    Records: 10  Deleted: 0  Skipped: 0  Warnings: 0
    
    mysql> 
    mysql> 
    mysql> select * from test02;
    +----------+-----------------+
    | user     | host            |
    +----------+-----------------+
    | fileuser | %               |
    | mycat    | %               |
    | zhao     | %               |
    | root     | 127.0.0.1       |
    | mis_lda  | 192.168.226.121 |
    | root     | ::1             |
    |          | centos01        |
    | root     | centos01        |
    |          | localhost       |
    | root     | localhost       |
    +----------+-----------------+
    10 rows in set (0.00 sec)
    
    mysql>

通过这个实验过程，可以得出以下几个结论：

1. 对于mysql中出现的下面错误，大部分可能是用户名和密码有问题，但也可能是权限有问题：

ERROR 1045 (28000): Access denied for user 'mis\_lda'@'192.168.226.121' (using password: YES)

2. mysql如果想要有into oufile 或者 load data infile 操作，必须授予file权限，而这个权限不能针对某个表，或库授予，是针对\*.\*的用户授予的；

3. 一般在mysql对已有用户添加新的权限，使用show grants for 命令查看权限时，会在下面一行增加显示；但如果是针对用户授予的权限，会上面用户名和口令语句中显示；

对于file权限的问题，在mysql官方文档中的说明如下：

权限信息用`user`、`db`、`host`、`tables_priv`和`columns_priv`表被存储在`mysql`数据库中(即在名为`mysql`的数据库中)。在**MySQL**启动时和在[6.9 权限修改何时生效][6.9]所说的情况时，服务器读入这些数据库表内容。

本手册所用的涉及由**MySQL**提供的权限名称显示在下表，还有在授权表中每个权限的表列名称和每个权限有关的上下文：

<table style="font-size:undefined; margin-top:10px; margin-bottom:10px; display:table; border-collapse:collapse; background-color:inherit"> 
 <tbody style="background-color:inherit"> 
 <tr style="background-color:inherit"> 
 <td style="">权限</td> 
 <td style="">列</td> 
 <td style="">上下文</td> 
 </tr> 
 <tr style="background-color:inherit"> 
 <td style="">select</td> 
 <td style=""><code style="font-family:Monaco,Consolas,Courier,'Lucida Console',monospace; font-style:normal; background-color:inherit">Select_priv</code></td> 
 <td style="">表</td> 
 </tr> 
 <tr style="background-color:inherit"> 
 <td style="">insert</td> 
 <td style=""><code style="font-family:Monaco,Consolas,Courier,'Lucida Console',monospace; font-style:normal; background-color:inherit">Insert_priv</code></td> 
 <td style="">表</td> 
 </tr> 
 <tr style="background-color:inherit"> 
 <td style="">update</td> 
 <td style=""><code style="font-family:Monaco,Consolas,Courier,'Lucida Console',monospace; font-style:normal; background-color:inherit">Update_priv</code></td> 
 <td style="">表</td> 
 </tr> 
 <tr style="background-color:inherit"> 
 <td style="">delete</td> 
 <td style=""><code style="font-family:Monaco,Consolas,Courier,'Lucida Console',monospace; font-style:normal; background-color:inherit">Delete_priv</code></td> 
 <td style="">表</td> 
 </tr> 
 <tr style="background-color:inherit"> 
 <td style="">index</td> 
 <td style=""><code style="font-family:Monaco,Consolas,Courier,'Lucida Console',monospace; font-style:normal; background-color:inherit">Index_priv</code></td> 
 <td style="">表</td> 
 </tr> 
 <tr style="background-color:inherit"> 
 <td style="">alter</td> 
 <td style=""><code style="font-family:Monaco,Consolas,Courier,'Lucida Console',monospace; font-style:normal; background-color:inherit">Alter_priv</code></td> 
 <td style="">表</td> 
 </tr> 
 <tr style="background-color:inherit"> 
 <td style="">create</td> 
 <td style=""><code style="font-family:Monaco,Consolas,Courier,'Lucida Console',monospace; font-style:normal; background-color:inherit">Create_priv</code></td> 
 <td style="">数据库、表或索引</td> 
 </tr> 
 <tr style="background-color:inherit"> 
 <td style="">drop</td> 
 <td style=""><code style="font-family:Monaco,Consolas,Courier,'Lucida Console',monospace; font-style:normal; background-color:inherit">Drop_priv</code></td> 
 <td style="">数据库或表</td> 
 </tr> 
 <tr style="background-color:inherit"> 
 <td style="">grant</td> 
 <td style=""><code style="font-family:Monaco,Consolas,Courier,'Lucida Console',monospace; font-style:normal; background-color:inherit">Grant_priv</code></td> 
 <td style="">数据库或表</td> 
 </tr> 
 <tr style="background-color:inherit"> 
 <td style="">references</td> 
 <td style=""><code style="font-family:Monaco,Consolas,Courier,'Lucida Console',monospace; font-style:normal; background-color:inherit">References_priv</code></td> 
 <td style="">数据库或表</td> 
 </tr> 
 <tr style="background-color:inherit"> 
 <td style="">reload</td> 
 <td style=""><code style="font-family:Monaco,Consolas,Courier,'Lucida Console',monospace; font-style:normal; background-color:inherit">Reload_priv</code></td> 
 <td style="">服务器管理</td> 
 </tr> 
 <tr style="background-color:inherit"> 
 <td style="">shutdown</td> 
 <td style=""><code style="font-family:Monaco,Consolas,Courier,'Lucida Console',monospace; font-style:normal; background-color:inherit">Shutdown_priv</code></td> 
 <td style="">服务器管理</td> 
 </tr> 
 <tr style="background-color:inherit"> 
 <td style="">process</td> 
 <td style=""><code style="font-family:Monaco,Consolas,Courier,'Lucida Console',monospace; font-style:normal; background-color:inherit">Process_priv</code></td> 
 <td style="">服务器管理</td> 
 </tr> 
 <tr style="background-color:inherit"> 
 <td style="">file</td> 
 <td style=""><code style="font-family:Monaco,Consolas,Courier,'Lucida Console',monospace; font-style:normal; background-color:inherit">File_priv</code></td> 
 <td style="">在服务器上的文件存取</td> 
 </tr> 
 </tbody> 
</table>

**        select**、**insert**、**update**和**delete**权限允许你在一个数据库现有的表上实施操作。

`SELECT`语句只有在他们真正从一个表中检索行是才需要**select**权限，你可以执行某个`SELECT`语句，甚至没有任何到服务器上的数据库里的存取任何东西的许可。例如，你可使用`mysql`客户作为一个简单的计算器：

mysql> SELECT 1+1;
    mysql> SELECT PI()*2;

**        index**权限允许你创建或抛弃(删除)索引。

**        alter**权限允许你使用`ALTER TABLE`。

**        create**和**drop**权限允许你创建新的数据库和表，或抛弃(删除)现存的数据库和表。

注意：如果你将`mysql`数据库的**drop**权限授予一个用户，该用户能抛弃存储了**MySQL**存取权限的数据库！

**        grant**权限允许你把你自己拥有的那些权限授给其他的用户。

**        file**权限给予你用`LOAD DATA INFILE`和`SELECT ... INTO OUTFILE`语句读和写服务器上的文件，任何被授予这个权限的用户都能读或写**MySQL**服务器能读或写的任何文件。

其余的权限用于管理性操作，它使用`mysqladmin`程序实施。下表显示`mysqladmin`支配每个管理性权限允许你执行的命令：

`        reload`命令告诉服务器再读入授权表，`refresh`命令清洗所有表并打开和关闭记录文件，`flush-privileges`是`reload`的一个同义词，其它`flush-*`命令执行类似`refresh`的功能，但是范围更有限，并且在某些情况下可能更好用。例如，如果你只是想清洗记录文件，`flush-logs`比`refresh`是更好的选择。

`        shutdown`命令关掉服务器。

`        processlist`命令显示在服务器内执行的线程的信息。`kill`命令杀死服务器线程。你总是能显示或杀死你自己的线程，但是你需要**process**权限来显示或杀死其他用户启动的线程。

总的说来，只授予权限给需要他们的那些用户是一个好主意，但是你应该在授予某个权限时试验特定的警告：

*  **grant**权限允许用户放弃他们的权限给其他用户。2个有不同的权限并有**grant**权限的用户可以合并权限。
 *  **alter**权限可以用于通过重新命名表来推翻权限系统。
 *  **file**权限可以被滥用在服务器上读取任何世界可读(world-readable，即任何人可读)的文件到一张数据库表，然后其内容能用`SELECT`被存取。
 *  **shutdown**权限通过终止服务器可以被滥用完全拒绝为其他用户服务, 。
 *  **precess**权限能被用来察看当前执行的查询的普通文本，包括设定或改变口令查询。
 *  在`mysql`数据库上的权限能被用来改变口令和其他存取权限信息。（口令被加密存储，所以一个恶意的用户不能简单地读取他们。然而，有足够的权限，同一个用户能用不同的一个代替一个口令。）

有一些事情你不能用**MySQL**权限系统做到：

*  你不能明显地指定一个给定用户应该被拒绝存取。即，你不能明显地匹配一个用户并且然后拒绝连接。
 *  你不能指定一个用户有权创建立或抛弃一个数据库中的表，也不能创建或抛弃数据库本身。

[6.9]: http://www.cbi.pku.edu.cn/chinese/documents/csdoc/mysql/manual_Privilege_system.html#Privilege_changes