MySQL权限篇之FILE

来源：互联网发布：java使用sdk 编辑：程序博客网时间：2024/06/05 17:43

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>

0 0