MySQL权限篇之FILE_mysql grant file

FILE权限，global privilege，服务器上的文件访问权限，是指在mysql服务器上有通过mysql实例读取或者写入操作系统目录文件的权限。

该权限影响如下三个操作：

LOAD DATA INFILE，将文件内容导入表中；
INTO OUTFILE ，将表中记录导出到文件中；
LOAD_FILE()，读取文件中内容。

先看看INTO OUTFILE 子句，该子句指定了将结果集直接导出到某个操作系统的文件中。如：

SELECT 
  * INTO OUTFILE 'D:/Program Files/mysql-5.7.11-winx64/temp/t_area.txt' 
  FIELDS TERMINATED BY ',' 
  OPTIONALLY ENCLOSED BY '"' 
  LINES TERMINATED BY '\n' 
FROM
  cms.t_area ;

注意：在5.7中，导出文件的路径必须是secure-file-priv参数指定的目录。并且mysql对该目录具有读写权限。Windows上目录路径必须是正斜杠（/）。

mysql> grant file on test.* to 'ut01'@'%';
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
mysql> grant file on *.* to 'ut01'@'%';
Query OK, 0 rows affected (0.04 sec)


mysql> show grants for 'ut01'@'%';
+---------------------------------+
| Grants for ut01@%               |
+---------------------------------+
| GRANT FILE ON *.* TO 'ut01'@'%' |
+---------------------------------+
1 row in set (0.00 sec)


mysql>

我们授予了该用户file权限，但是要使用 INTO OUTFILE 导出数据，必须还需要被导出表上的select权限：
mysql> grant select on cms.t_area to 'ut01'@'%';
Query OK, 0 rows affected (0.03 sec)


mysql>

接下来看看，ut01@%用户的操作：

C:\Users\Administrator>mysql -uut01
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.11-log 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