MySQL_LOAD_DATA权限设置

MySQL：LOAD_DAT A权限设置

LOAD DATA 默认读的是服务器上的文件，但是加上LOCAL参数后，就可以将本地具有访问权限的文件加载到数据库中。这在带来方便的同时。也带来了以下安全问题：

1、可以任意加载本地文件到数据库。

2、在WEB环境中，客户从WEB服务器连接，用户可以使用LOAD DATA LOCAL语句来读取WEB服务器进程有读访问权限的任何文件(假定用户可以运行SQL服务器的任何命令)。在这种环境中，MySQL服务器的客户实际上是WEB服务器，而不是连接WEB服务器的用户运行的程序。

解决方法：

1、可以用--local-infile=0选项启动mysqld从服务器端禁用所有LOAD DATA LOCAL命令。

即是在/etc/https://www.360docs.net/doc/4113935852.html,f的[mysqld]下面添加local-infile=0选项。

2、对于mysql命令行的客户端，可以通指定--local-infile[=1]选项启用LOAD DATA LOCAL命令，

或通过--local-infile=0选项禁用。

类似地，对于mysqlimport,--local or -L选项启用本地数据库文件装载。在任何情况下，成功进行本地装载需要服务器启有相关选项。

如下为实操例子：

1、/etc/https://www.360docs.net/doc/4113935852.html,f

# The MySQL server

[mysqld]

port = 3306

socket = /tmp/mysql.sock

skip-locking

key_buffer = 16M

max_allowed_packet = 1M

table_cache = 64

sort_buffer_size = 512K

net_buffer_length = 8K

read_buffer_size = 256K

read_rnd_buffer_size = 512K

myisam_sort_buffer_size = 8M

local-infile=0 (增加此选项)

重启mysql

/etc/init.d/mysql restart

尝试用—user=root 连接

[root@localhost ~]# /home/mysql/bin/mysql --user=root --password --host=localhost

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.0.45-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| confluence |

| mysql |

| test |

| test01 |

+--------------------+

5 rows in set (0.00 sec)

mysql> use test01;

Database changed

mysql> select user();

+----------------+

| user() |

+----------------+

| root@localhost |

+----------------+

1 row in set (0.00 sec)

mysql> select * from role;

+-----+--------------------+

| rid | name |

+-----+--------------------+

| 1 | anonymous user |

| 2 | authenticated user |

+-----+--------------------+

2 rows in set (0.01 sec)

mysql> load data local infile 'test_01.sql' into table role fields terminated by ','; ERROR 1148 (42000): The used command is not allowed with this MySQL version

2、/etc/https://www.360docs.net/doc/4113935852.html,f

# The MySQL server

[mysqld]

port = 3306

socket = /tmp/mysql.sock

skip-locking

key_buffer = 16M

max_allowed_packet = 1M

table_cache = 64

sort_buffer_size = 512K

net_buffer_length = 8K

read_buffer_size = 256K

read_rnd_buffer_size = 512K

myisam_sort_buffer_size = 8M

local-infile=1 (将此选项改为1)

重启mysql，再用—user=root连接

[root@localhost ~]# /home/mysql/bin/mysql --user=root --password --host=localhost Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.0.45-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test01;

Database changed

mysql> select * from role;

+-----+--------------------+

| rid | name |

+-----+--------------------+

| 1 | anonymous user |

| 2 | authenticated user |

+-----+--------------------+

2 rows in set (0.00 sec)

mysql> load data local infile 'test_01.sql' into table role fields terminated by ','; Query OK, 1 row affected (0.00 sec)

Records: 1 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from role;

+-----+--------------------+

| rid | name |

+-----+--------------------+

| 1 | anonymous user |

| 2 | authenticated user |

| 3 | zhongyh user |

+-----+--------------------+

3 rows in set (0.00 sec)

另一种方法是在客户端连接取用--local-infile[=0/1]

/etc/https://www.360docs.net/doc/4113935852.html,f采用

# The MySQL server

[mysqld]

port = 3306

socket = /tmp/mysql.sock

skip-locking

key_buffer = 16M

max_allowed_packet = 1M

table_cache = 64

sort_buffer_size = 512K

net_buffer_length = 8K

read_buffer_size = 256K

read_rnd_buffer_size = 512K

myisam_sort_buffer_size = 8M

local-infile=1 (将此选项改为1)

[root@localhost ~]# /home/mysql/bin/mysql --user=root --password --host=localhost --local-infile=0 Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.0.45-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test01;

Database changed

mysql> select * from role;

+-----+--------------------+

| rid | name |

+-----+--------------------+

| 1 | anonymous user |

| 2 | authenticated user |

| 3 | zhongyh user |

+-----+--------------------+

3 rows in set (0.00 sec)

mysql> load data local infile 'test_02.sql' into table role fields terminated by ',';

ERROR 1148 (42000): The used command is not allowed with this MySQL version