Bug #76777 mysqlfrm user permissions issue
Submitted: 21 Apr 2015 15:07 Modified: 16 Jun 2015 22:52
Reporter: Shahriyar Rzayev (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Utilities Severity:S1 (Critical)
Version:1.5.4 OS:Any (CentOS 7)
Assigned to: CPU Architecture:Any

[21 Apr 2015 15:07] Shahriyar Rzayev
Description:
As Documentation for mysqlfrm states:
"
PERMISSIONS REQUIRED

The permissions for using mysqlfrm will vary and depend entirely on how you use it. If you use the utility to read .frm files in a protected folder like the example above (in either mode), you must have the ability to run the server as root.

If you use the utility with a server connection, the user you use to connect must have the ability to read system variables at a minimum (read access to the mysql database). "

From documentation we understand that we can run this command as regular linux user, but with MySQL's root user.

1. If we specify .frm file as "{database:table.frm}":

[sh@centos7 ~]$ mysqlfrm --server=root:12345@localhost:3306 employees:salaries.frm --port=3333 -vvv
WARNING: Using a password on the command line interface can be insecure.
# Source on localhost: ... connected.
# Checking read access to .frm files 
ERROR: Cannot read .frm file from salaries.frm.

2. If we specify .frm file as full path it will spawn server, although it rises permission ERROR:

[sh@centos7 ~]$ mysqlfrm --server=root:12345@localhost:3306 /var/lib/mysql/employees/salaries.frm --port=3333 -vvv
WARNING: Using a password on the command line interface can be insecure.
# Source on localhost: ... connected.
ERROR: Cannot read /var/lib/mysql/employees/salaries.frm. You must have read privileges to the file or path and it must exist. Skipping this argument.
.
.
# Checking read access to .frm files 
# Creating a temporary datadir = /tmp/tmphgvLjo
# Starting the spawned server on port 3333 ...
# Cloning the MySQL server located at /usr.
# Configuring new instance...
.
.
# Starting new instance of the server...
# Startup command for new server:
/usr/sbin/mysqld --no-defaults --basedir=/usr --datadir=/tmp/tmphgvLjo --pid-file=/tmp/tmphgvLjo/clone.pid --port=3333 --server-id=101 --socket=/tmp/tmphgvLjo/mysql.sock --tmpdir=/tmp/tmphgvLjo
.
.
# Success!
# Setting the root password...
Warning: Using a password on the command line interface can be insecure.
# Connection Information:
#  -uroot -proot --socket=/tmp/tmphgvLjo/mysql.sock
#...done.
# Connecting to spawned server
done.
# Reading .frm files
# Shutting down spawned server
# Removing the temporary datadir
#...done.

So we use server connection with root user = and root user can read system variables. (ability to read system variables). Documentation should be much more clear.

Now if we decide to run mysqlfrm with Linux root user:

[root@centos7 new_datadir]# mysqlfrm --server=root:12345@localhost:3306 /var/lib/mysql/employees/salaries.frm --port=3333 -vvv
WARNING: Using a password on the command line interface can be insecure.
# Source on localhost: ... connected.
Usage: mysqlfrm --server=[user[:<pass>]@host[:<port>][:<socket>]|<login-path>[:<port>][:<socket>]] [path\tbl1.frm|db:tbl.frm]

mysqlfrm: error: Running a spawned server as root is not advised. If you want to run the utility as root, please provide the --user option to specify a user to use to launch the server. Example: --user=mysql.

But if we use --user=root it will work as needed:

[root@centos7 ~]# mysqlfrm --server=root:12345@localhost:3306 /var/lib/mysql/employees/salaries.frm --port=3333 -vvv --user=root
WARNING: Using a password on the command line interface can be insecure.
# Source on localhost: ... connected.
# Checking read access to .frm files 
# Creating a temporary datadir = /tmp/tmpahExn_
# Spawning server with --user=root.
# Starting the spawned server on port 3333 ...
# Cloning the MySQL server located at /usr.
# Configuring new instance...
.
.
/usr/sbin/mysqld --no-defaults --basedir=/usr --datadir=/tmp/tmpahExn_ --pid-file=/tmp/tmpahExn_/clone.pid --port=3333 --server-id=101 --socket=/tmp/tmpahExn_/mysql.sock --tmpdir=/tmp/tmpahExn_ --user=root
.
.
# Engine string: InnoDB
# Server version in file: 5.6.23
#
# CREATE statement for /var/lib/mysql/employees/salaries.frm:
#

CREATE TABLE `employees`.`salaries` (
  `emp_no` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`from_date`),
  KEY `emp_no` (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

But if we specify --user=mysql (as documentation states) it will not work:

[root@centos7 ~]# mysqlfrm --server=root:12345@localhost:3306 /var/lib/mysql/employees/salaries.frm --port=3333 -vvv --user=mysql
WARNING: Using a password on the command line interface can be insecure.
# Source on localhost: ... connected.
# Checking read access to .frm files 
# Creating a temporary datadir = /root/e51075ef-8532-4e3c-ac33-4c76c74e1de2
# Spawning server with --user=mysql.
# Starting the spawned server on port 3333 ...
# Cloning the MySQL server located at /usr.

# Startup command for new server:
/usr/sbin/mysqld --no-defaults --basedir=/usr --datadir=/root/e51075ef-8532-4e3c-ac33-4c76c74e1de2 --pid-file=/root/e51075ef-8532-4e3c-ac33-4c76c74e1de2/clone.pid --port=3333 --server-id=101 --socket=/root/e51075ef-8532-4e3c-ac33-4c76c74e1de2/mysql.sock --tmpdir=/root/e51075ef-8532-4e3c-ac33-4c76c74e1de2 --user=mysql
.
.
/usr/sbin/mysqld: Can't create/write to file '/root/e51075ef-8532-4e3c-ac33-4c76c74e1de2/ibjFAInl' (Errcode: 13 - Permission denied)
2015-04-21 19:39:57 7fc447425740  InnoDB: Error: unable to create temporary file; errno: 13
2015-04-21 19:39:57 4664 [ERROR] Plugin 'InnoDB' init function returned error.
2015-04-21 19:39:57 4664 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2015-04-21 19:39:57 4664 [ERROR] Unknown/unsupported storage engine: InnoDB
2015-04-21 19:39:57 4664 [ERROR] Aborting

Now let's try with mysql user without --user option:

1. It will work correctly:

[root@centos7 ~]# su - mysql
Last login: Tue Apr 21 16:24:10 AZST 2015 on pts/2
-bash-4.2$  mysqlfrm --server=root:12345@localhost:3306 /var/lib/mysql/employees/salaries.frm --port=3333 -vvv
WARNING: Using a password on the command line interface can be insecure.
# Source on localhost: ... connected.
# Checking read access to .frm files 
# Creating a temporary datadir = /tmp/tmpYzMNhg
# Starting the spawned server on port 3333 ...
# Cloning the MySQL server located at /usr.

2. now with --user=sh (previously failed) will work correctly too:

-bash-4.2$  mysqlfrm --server=root:12345@localhost:3306 /var/lib/mysql/employees/salaries.frm --port=3333 -vvv --user=sh
WARNING: Using a password on the command line interface can be insecure.
# Source on localhost: ... connected.
# Checking read access to .frm files 
# Creating a temporary datadir = /tmp/tmpOj8Ot0
# Spawning server with --user=sh.

1. So in conclusion {database:table.frm} like syntax does not work.
2. With a regular Linux user it will rise an ERROR about read access, instead of terminating the process it will spawn server. --user=mysql and --user=root will not work.
3. With Linux root user without --user option it will throw an error about using --user=mysql even if you specify --user=mysql it will not work but if you specify --user=root it will by-pass this limitation.
4. With Linux mysql user --user=mysql, --user=root, --user=sh will work.

How to repeat:
All steps are provided in description

Suggested fix:
1. Maybe improvement in documentation about mysqlfrm
2. Regular user permission check before starting to spawn server
3. Disallow running with root, even specifying --user=root
[28 May 2015 19:46] Chuck Bell
Fixed in release 1.6.2.
[16 Jun 2015 22:52] Philip Olson
Thank you for the detailed bug report, the documentation was updated and now reads as follows:

PERMISSIONS REQUIRED
The permissions for using mysqlfrm will vary and depend entirely on how you use it. If you use the utility to read .frm files in a protected folder like the example above (in either mode), you must have the ability to run the spawned server with privileges that allow you to read the protected files. For example, you could use a user account that has root-level privileges.

If you use the utility with a server connection, the user you use to connect must have the ability to read system variables at a minimum including read access to the mysql database.

You should never use the root user to spawn the server nor should you use the mysql user when spawning the server or running the utility.