1

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
1

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 !!!

| improve this answer | |
  • I thought SELECT... INTO OUTFILE won't overwrite an existing file, regardless of secure_file_priv. It gives error ERROR 1086 (HY000): File 'outfile.txt' already exists – Bill Karwin Jul 8 '13 at 23:47
2

I think you are confused about what's happening here. You ran the client on the server machine as root. You probably weren't running the client as root on the "remote" machine. Even if your query had succeeded in appending to /etc/password, you would have only modified your local password file, not the one on the server.

The pager is always local to where the mysql client is running. It has nothing to do with the MySQL server.

If the user on the local machine (where the client is running) has write access to a local file, then they can write to the file via the pager option, just as they could write to the file with anything else. There's nothing here that has anything to do with the MySQL server or permissions.

mysql> \P cat >> foo.bar
PAGER set to 'cat >> foo.bar'
mysql> select NOW();
1 row in set (0.00 sec)

mysql> quit
Bye

Now, by making the file so it can't be written...

sqlbot@dev:~$ chmod 000 foo.bar
sqlbot@dev:~$ mysql

mysql> \P cat >> foo.bar
PAGER set to 'cat >> foo.bar'
mysql> select NOW();
sh: cannot create foo.bar: Permission denied
1 row in set (0.00 sec)

mysql> quit
Bye

Run the client as root on the remote machine and you'll see that you won't get permission denied, but you also will not have modified anything on the server -- just your local password file.

Also, yeah, don't create a 'root'@'%' account in MySQL.

| improve this answer | |
  • Thanks for your reply - so another words MySQL is secure enough and there is no way to modify system files with a remote MySQL access (only) to the server? – HTF Jul 8 '13 at 19:46
  • 1
    You're correct, if permissions on the system files are sane. If you GRANT the FILE privilege to a user, the user will have some level of access to files on the server, with the same unix credentials mysqld itself is running under (typically this is the mysql account) regardless of their MySQL username ("root" is only a privileged MySQL user by convention; it has no relation to unix "root"). Setting secure_file_priv further restricts file access to a specific path. – Michael - sqlbot Jul 8 '13 at 19:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.