MySQL file权限

2025-04-09 技术教程

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

拥有file权限才可以执行select..intooutfile和loaddatainfile…操作，但是不要把file,process,super权限授予管理员以外的账号，这样存在严重的安全隐患。下面简单做个试验：
1、创建环境 mysql>CREATEUSER'filetest'@'localhost'IDENTIFIEDBY'mypass';QueryOK,0rowsaffected(0.01sec)
mysql>GRANTselectONtest.*TO'filetest'@'localhost';ERROR1290(HY000):TheMySQLserverisrunningwiththe--skip-grant-tablesoptionsoitcannotexecutethisstatement
mysql>flushprivileges;QueryOK,0rowsaffected(0.00sec)
mysql>GRANTselectONtest.*TO'filetest'@'localhost';QueryOK,0rowsaffected(0.00sec)
mysql>CREATETABLEtab1(->aavarchar(50),->bbvarchar(50)->);QueryOK,0rowsaffected(0.02sec)
mysql>mysql>insertintotab1values('aaa','bbb');QueryOK,1rowaffected(0.01sec)
mysql>insertintotab1values('ccc','ddd');QueryOK,1rowaffected(0.01sec)
mysql>
2、切换到filetest用户： [root@master~]#mysql-ufiletest-pEnterpassword:WelcometotheMySQLmonitor.Commandsendwith;or\g.YourMySQLconnectionidis7Serverversion:5.7.13-logSourcedistribution
Copyright(c)2000,2015,Oracleand/oritsaffiliates.Allrightsreserved.
OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.
Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.
mysql>usetest;ReadingtableinformationforcompletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-A
Databasechangedmysql>showdatabases;+--------------------+|Database|+--------------------+|information_schema||test|+--------------------+2rowsinset(0.00sec)
mysql>select*fromtab1intooutfile'/mysql/mysql57/st_file1';ERROR1045(28000):Accessdeniedforuser'filetest'@'localhost'(usingpassword:YES) 没有file权限，倒出报错！
3、root登陆授权： mysql>grantfileontest.*tofiletest@localhost;ERROR1221(HY000):IncorrectusageofDBGRANTandGLOBALPRIVILEGES mysql>grantfileon*.*tofiletest@localhost; QueryOK,0rowsaffected(0.01sec)
mysql>flushprivileges;QueryOK,0rowsaffected(0.00sec)
4、filetest用户登陆
mysql>usetest;ReadingtableinformationforcompletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-A
Databasechangedmysql>select*fromtab1intooutfile'/mysql/mysql57/st_file'; QueryOK,2rowsaffected(0.01sec)
mysql>[root@mastermysql57]#catst_fileaaabbbcccddd

5、导入 mysql>createtabletab2asselect*fromtab1;QueryOK,2rowsaffected(0.02sec)Records:2Duplicates:0Warnings:0
mysql>desctab2->;+-------+-------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-------+-------------+------+-----+---------+-------+|aa|varchar(50)|YES||NULL|||bb|varchar(50)|YES||NULL||+-------+-------------+------+-----+---------+-------+2rowsinset(0.01sec)
mysql>select*fromtab2;+------+------+|aa|bb|+------+------+|aaa|bbb||ccc|ddd|+------+------+2rowsinset(0.00sec)
mysql>truncatetabletab2;QueryOK,0rowsaffected(0.02sec)
mysql>select*fromtab2;Emptyset(0.00sec)
mysql>loaddatainfile'/mysql/mysql57/st_file1'intotabletab2;ERROR1142(42000):INSERTcommanddeniedtouser'filetest'@'localhost'fortable'tab2'
##root登陆授权：mysql>grantinsertontest.*tofiletest@localhost;QueryOK,0rowsaffected(0.01sec)
mysql>flushprivileges;QueryOK,0rowsaffected(0.00sec)

##重新登陆：mysql>loaddatainfile'/mysql/mysql57/st_file1'intotabletab2; QueryOK,2rowsaffected(0.01sec)Records:2Deleted:0Skipped:0Warnings:0
mysql>select*fromtab2;+------+------+|aa|bb|+------+------+|aaa|bbb||ccc|ddd|+------+------+2rowsinset(0.00sec)
小实验完成。