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

$ mysqlplus.sh 
Login MySQL Srevice: lcoalhost_3306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.19-log MySQL Community Server (GPL)

Copyright (c) 2000, 2011, 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> select user,host from mysql.user;
+----------+-----------+
| user     | host      |
+----------+-----------+
| fileuser | %         |
| mycat    | %         |
| zhao     | %         |
| root     | 127.0.0.1 |
| root     | ::1       |
|          | centos01  |
| root     | centos01  |
|          | localhost |
| root     | localhost |
+----------+-----------+
9 rows in set (0.01 sec)

mysql> 
mysql> show tables;
ERROR 1046 (3D000): No database selected
mysql> show tables;
ERROR 1046 (3D000): No database selected
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| DataPlatform       |
| auction            |
| db1                |
| db2                |
| db3                |
| db_bcty365         |
| db_pursey          |
| discuzX3           |
| hibernate_20140223 |
| hivemeta           |
| jjbbs              |
| jjwiki             |
| jjwikidb           |
| liferay            |
| lportal            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
19 rows in set (0.10 sec)

mysql> 
mysql> 
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> show tabels;
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 'tabels' at line 1
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                    |
| user                              |
| user_role                         |
+-----------------------------------+
20 rows in set (0.00 sec)

mysql> 
mysql> 
mysql> select user,host from mysql.user;
+----------+-----------+
| user     | host      |
+----------+-----------+
| fileuser | %         |
| mycat    | %         |
| zhao     | %         |
| root     | 127.0.0.1 |
| root     | ::1       |
|          | centos01  |
| root     | centos01  |
|          | localhost |
| root     | localhost |
+----------+-----------+
9 rows in set (0.00 sec)

mysql> 
mysql> 
mysql> 
mysql> 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' IDENTIFIED BY 'fPUw2TgI';
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> select user,host from mysql.user;
+----------+-----------------+
| 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> 
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.01 sec)

mysql> 
mysql> 
mysql> create table test01 as select user,host from mysql.user where 1=0;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> select user,host from mysql.user into outfile '/tmp/test01.txt';
Query OK, 10 rows affected (0.01 sec)

mysql> 
mysql> select * from test02;
ERROR 1146 (42S02): Table 'test.test02' doesn't exist
mysql> 
mysql> select * from test01;
Empty set (0.00 sec)

mysql> 
mysql> load data infile '/tmp/test01.txt' into table test01;
Query OK, 10 rows affected (0.04 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 0

mysql> 
mysql> select * from test02;
ERROR 1146 (42S02): Table 'test.test02' doesn't exist
mysql> 
mysql> select * from test01;
+----------+-----------------+
| 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> 

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

$ 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 权限修改何时生效所说的情况时，服务器读入这些数据库表内容。

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

权限	列	上下文
select	Select_priv	表
insert	Insert_priv	表
update	Update_priv	表
delete	Delete_priv	表
index	Index_priv	表
alter	Alter_priv	表
create	Create_priv	数据库、表或索引
drop	Drop_priv	数据库或表
grant	Grant_priv	数据库或表
references	References_priv	数据库或表
reload	Reload_priv	服务器管理
shutdown	Shutdown_priv	服务器管理
process	Process_priv	服务器管理
file	File_priv	在服务器上的文件存取

        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	reload, refresh, flush-privileges, flush-hosts, flush-logs, flush-tables
shutdown	shutdown
precess	processlist, kill

        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权限系统做到：

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

版权声明：本文为博主原创文章，未经博主允许不得转载。

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

标签：mysql