MySQL file权限

千家信息网最后更新 2025年01月22日MySQL file权限

file权限指的是是否能够对系统的文件读取和写操作.

                           拥有file权限才可以执行 select ..into   outfile和load data infile…操作，但是不要把file, process, super权限授予管理员以外的账号，这样存在严重的安全隐患。  下面简单做个试验：        

1、创建环境 mysql> CREATE USER 'filetest'@'localhost' IDENTIFIED BY 'mypass'; Query OK, 0 rows affected (0.01 sec)
mysql> GRANT select ON test.* TO 'filetest'@'localhost'; ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
mysql> GRANT select ON test.* TO 'filetest'@'localhost'; Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE tab1( -> aa varchar(50), -> bb varchar(50) -> ); Query OK, 0 rows affected (0.02 sec)
mysql> mysql> insert into tab1 values('aaa','bbb'); Query OK, 1 row affected (0.01 sec)
mysql> insert into tab1 values('ccc','ddd'); Query OK, 1 row affected (0.01 sec)
mysql>
2、切换到filetest用户： [root@master ~]# mysql -ufiletest -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.7.13-log Source distribution
Copyright (c) 2000, 2015, 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; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A
Database changed mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | test | +--------------------+ 2 rows in set (0.00 sec)
mysql> select * from tab1 into outfile '/mysql/mysql57/st_file1'; ERROR 1045 (28000): Access denied for user 'filetest'@'localhost' (using password: YES) 没有file权限，倒出报错！
3、root登陆授权： mysql> grant file on test.* to filetest@localhost; ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES mysql> grant file on *.* to filetest@localhost; Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
4、filetest用户登陆
mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A
Database changed mysql> select * from tab1 into outfile '/mysql/mysql57/st_file'; Query OK, 2 rows affected (0.01 sec)
mysql> [root@master mysql57]# cat st_file aaa bbb ccc ddd

5、导入 mysql> create table tab2 as select * from tab1; Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0
mysql> desc tab2 -> ; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | aa | varchar(50) | YES | | NULL | | | bb | varchar(50) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.01 sec)
mysql> select * from tab2; +------+------+ | aa | bb | +------+------+ | aaa | bbb | | ccc | ddd | +------+------+ 2 rows in set (0.00 sec)
mysql> truncate table tab2; Query OK, 0 rows affected (0.02 sec)
mysql> select * from tab2; Empty set (0.00 sec)
mysql> load data infile '/mysql/mysql57/st_file1' into table tab2; ERROR 1142 (42000): INSERT command denied to user 'filetest'@'localhost' for table 'tab2'
##root登陆授权： mysql> grant insert on test.* to filetest@localhost; Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)

##重新登陆： mysql> load data infile '/mysql/mysql57/st_file1' into table tab2; Query OK, 2 rows affected (0.01 sec) Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from tab2; +------+------+ | aa | bb | +------+------+ | aaa | bbb | | ccc | ddd | +------+------+ 2 rows in set (0.00 sec)
小实验完成。