MySQL_LOAD_DATA权限设置

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

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

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

解决方法：

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

即是在/etc/my.cnf的[mysqld]下面添加local-infile=0选项。

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

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

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

 

如下为实操例子：

1、/etc/my.cnf

# 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-logMySQL Community Server (GPL)

 

Type 'help;' or '\h' for help. Type '\c' toclear 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 tablerole fields terminated by ',';

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

 

2、/etc/my.cnf

# 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-logMySQL Community Server (GPL)

 

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

 

mysql>use test01;

Databasechanged

mysql>select * from role;

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

|rid | name               |

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

|   1 | anonymous user     |

|   2 | authenticated user |

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

2rows in set (0.00 sec)

 

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

QueryOK, 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       |

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

3rows in set (0.00 sec)

 

 

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

/etc/my.cnf采用

# 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-logMySQL Community Server (GPL)

 

Type 'help;' or '\h' for help. Type '\c' toclear 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 notallowed with this MySQL version