在MySQL中经常需要导出一些表的数据，或者加载数据到某些表中，这时就涉及到一些权限的问题。今天就遇到一个开发加载数据的问题，我在测试环境进行了一些测试，并得出了一结论，过程和结果如下：
在测试环境中，创建一个测试用户，然后以root身份执行导入导出过程，导出导出过程正常：
$ mysqlplus.sh Login MySQL Srevice: lcoalhost_3306Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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 selectedmysql> show tables;ERROR 1046 (3D000): No database selectedmysql> 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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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 1mysql> 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: 0mysql> 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 existmysql> 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: 0mysql> mysql> select * from test02;ERROR 1146 (42S02): Table 'test.test02' doesn't existmysql> 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 3Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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 1mysql> use test;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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 1mysql> create table test02 select * from test01 where 1=0;Query OK, 0 rows affected (0.04 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> 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> exitBye
提示error 1045错误，一般是用户名和密码有问题的时候，会出现该错误，但此时已经建立正常连接，并且可以正常查询，肯定不是用户名和密码问题。
于是经过思考后，在root用户下，对新用户授予file权限，但授权方式和显示还是比较特殊的：
mysql> mysql> show grants for mis_lda@'192.168.226.121'; +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Grants for [email protected] |+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| 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 PRIVILEGESmysql> 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 [email protected] |+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| 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 4Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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: 0mysql> 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权限系统做到：
- 你不能明显地指定一个给定用户应该被拒绝存取。即，你不能明显地匹配一个用户并且然后拒绝连接。
- 你不能指定一个用户有权创建立或抛弃一个数据库中的表，也不能创建或抛弃数据库本身。
版权声明：本文为博主原创文章，未经博主允许不得转载。