MySQL权限篇之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 help. Type '\c' to clear the current input statement.


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cms                |
+--------------------+
2 rows in set (0.00 sec)


mysql> use cms
Database changed
mysql> show tables;
+---------------+
| Tables_in_cms |
+---------------+
| t_area        |
+---------------+
1 row in set (0.00 sec)


mysql> 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 ;
Query OK, 228 rows affected (0.06 sec)


mysql>

成功导出。

来看看LOAD DATA INFILE操作，该子句指定将操作系统上的某个文件，导入到某个表中：

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


mysql> grant file on *.* to 'ut01'@'%';
Query OK, 0 rows affected (0.05 sec)


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


mysql>

还需要授予该用户在某个schema上的某个table的update权限：

mysql> use test
Database changed
mysql> CREATE TABLE `t_area` (
    ->   `id` varchar(255) NOT NULL,
    ->   `address` varchar(255) DEFAULT NULL,
    ->   `level` int(11) NOT NULL,
    ->   `name` varchar(255) DEFAULT NULL,
    ->   `sort` int(11) NOT NULL,
    ->   `telephone` varchar(255) DEFAULT NULL,
    ->   `visible` bit(1) NOT NULL,
    ->   `parentId` varchar(255) DEFAULT NULL,
    ->   `number` varchar(255) DEFAULT NULL,
    ->   `theCode` varchar(255) DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.38 sec)


mysql> grant update on test.t_area to 'ut01'@'%';
Query OK, 0 rows affected (0.09 sec)


mysql> show grants for 'ut01'@'%';
+-----------------------------------------------+
| Grants for ut01@%                             |
+-----------------------------------------------+
| GRANT FILE ON *.* TO 'ut01'@'%'               |
| GRANT UPDATE ON `test`.`t_area` TO 'ut01'@'%' |
+-----------------------------------------------+
2 rows in set (0.00 sec)


mysql>

看看ut01@%的操作：

C:\Users\Administrator>mysql -uut01
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
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 help. Type '\c' to clear the current input statement.


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)


mysql> use test
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t_area         |
+----------------+
1 row in set (0.00 sec)


mysql> LOAD DATA INFILE 'D:/Program Files/mysql-5.7.11-winx64/temp/t_area.txt'
    -> INTO TABLE test.`t_area`
    -> FIELDS TERMINATED BY ','
    -> OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n';
ERROR 1142 (42000): INSERT command denied to user 'ut01'@'localhost' for table 't_area'
mysql>

要insert权限，不是update权限：

mysql> grant insert on test.t_area to 'ut01'@'%';
Query OK, 0 rows affected (0.07 sec)


mysql> revoke update on test.t_area from 'ut01'@'%';
Query OK, 0 rows affected (0.04 sec)


mysql> show grants for 'ut01'@'%';
+-----------------------------------------------+
| Grants for ut01@%                             |
+-----------------------------------------------+
| GRANT FILE ON *.* TO 'ut01'@'%'               |
| GRANT INSERT ON `test`.`t_area` TO 'ut01'@'%' |
+-----------------------------------------------+
2 rows in set (0.00 sec)


mysql>

该用户继续：

C:\Users\Administrator>mysql -uut01
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
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 help. Type '\c' to clear the current input statement.


mysql> use test;
Database changed
mysql> LOAD DATA INFILE 'D:/Program Files/mysql-5.7.11-winx64/temp/t_area.txt'
    -> INTO TABLE test.`t_area`
    -> FIELDS TERMINATED BY ','
    -> OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n';
Query OK, 228 rows affected (0.52 sec)
Records: 228  Deleted: 0  Skipped: 0  Warnings: 0  


mysql> select * from t_area;
ERROR 1142 (42000): SELECT command denied to user 'ut01'@'localhost' for table 't_area'
mysql>

成功导入。

来看看LOAD_FILE()函数，该函数读取操作系统上的文件（该文件路径为secure-file-priv指定路径），然后以字符串格式返回文件内容。

如果没有该文件则返回为NULL，或者该文件路径和secure-file-priv参数指定不同，也返回NULL。

文件的大小必须小于参数max_allowed_packet指定的值：

mysql> select load_file('D:/Program Files/mysql-5.7.11-winx64/temp/t.txt');
+--------------------------------------------------------------+
| load_file('D:/Program Files/mysql-5.7.11-winx64/temp/t.txt') |
+--------------------------------------------------------------+
| NULL                                                         |
+--------------------------------------------------------------+
1 row in set (0.05 sec)


mysql> select 'aabb' into outfile 'D:/Program Files/mysql-5.7.11-winx64/temp/t.txt';
Query OK, 1 row affected (0.00 sec)


mysql> select load_file('D:/Program Files/mysql-5.7.11-winx64/temp/t.txt');
+--------------------------------------------------------------+
| load_file('D:/Program Files/mysql-5.7.11-winx64/temp/t.txt') |
+--------------------------------------------------------------+
| aabb
                                                        |
+--------------------------------------------------------------+
1 row in set (0.02 sec)


mysql> select load_file('D:/temp/t.txt');  #该文件存在，并且非空
+----------------------------+
| load_file('D:/temp/t.txt') |
+----------------------------+
| NULL                       |
+----------------------------+
1 row in set (0.00 sec)


mysql>