Mysql – Security: writing to a file from MySQL CLI

MySQL

I was testing writes to a file from MySQL CLI (locally & remotely) and I wonder what settings are preventing writes for remote users, basically I would like to make sure that it's set on all servers in case someone (devs) will allow remote access to MySQL:

Local (Writes allowed):

mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> \P cat >> /etc/passwd
PAGER set to 'cat >> /etc/passwd'
mysql> SELECT CONCAT('test:x:5000:5000::/home/test:/bin/bash');

Remote (writes denied):

mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| root@%         |
+----------------+
1 row in set (0.01 sec)

mysql> \P cat >> /etc/passwd
PAGER set to 'cat >> /etc/passwd'
mysql> SELECT CONCAT('test:x:5000:5000::/home/test:/bin/bash');
sh: /etc/passwd: Permission denied

Best Answer

I was looking through the MySQL Documentation. The FILE privilege can restrict

but may not have an effect on the MySQL client pager.

Since root@'%' fails and you want root@localhost to fail, here are your options

OPTION #1

Try disabling the FILE privilege anyway on root@localhost by doing this

UPDATE mysql.user SET file_priv = 'N' WHERE user='root' AND host='localhost';
FLUSH PRIVILEGES;

You will have to put it back if you want to use LOAD DATA INFILE, SELECT ... INTO OUTFILE, or LOAD_FILE().

OPTION #2

Try setting this: secure_file_priv

OPTION #3

Give root@'%' a different password. When Developers login as

mysql -uroot -p127.0.0.1 --protocol=tcp -p

Using the different password, this may provide the restriction.

Make sure you have both root@localhost and root@'%' are defined in mysql.user. Check by doing:

SELECT user,host,password FROM mysql.user WHERE user='root';

As an alternative, change the root@localhost password. Don't give the developers the new password.

OPTION #4

TELL THE DEVELOPERS DON'T USE THE PAGER !!! Human nature is the hardest to program, eh ???

Give it A Try !!!

Related Question