MySQL Bugs: #15013: show innodb status privileged

Bug #15013	show innodb status privileged
Submitted:	17 Nov 2005 12:06	Modified:	17 Nov 2005 18:00
Reporter:	Kristian Koehntopp	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.10-standard-log/5.0.17 BK	OS:	Linux (RHEL 3 Linux)
Assigned to:		CPU Architecture:	Any

Description:
"show variables" and "show variables" are not privileged commands, "show inndb status" requires SUPER priviliege. This is inconsistent and should be changed.

How to repeat:
mysql> select version();
+---------------------+
| version()           |
+---------------------+
| 4.1.10-standard-log |
+---------------------+
1 row in set (0.00 sec)

mysql> show status;
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| Aborted_clients          | 2741       |
| Aborted_connects         | 240001     |
...
| Uptime                   | 18405546   |
+--------------------------+------------+
156 rows in set (0.00 sec)

mysql> show innodb status;
ERROR 1227 (HY000): Access denied; you need the SUPER privilege for this operation

Suggested fix:
Make "SHOW INNODB STATUS" behave like "SHOW STATUS" regarding permissions required.

miguel@hegel:~/dbs/5.0> bin/mysql -uuser3 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.17-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show innodb status;
ERROR 1227 (42000): Access denied; you need the SUPER privilege for this operation

mysql> show variables;
+---------------------------------+---------
| Variable_name                   | Value   
+---------------------------------+---------
| auto_increment_increment        | 1       
| auto_increment_offset           | 1       

<cut>

222 rows in set (0.02 sec)

mysql> show status;
+-----------------------------------+-----------+
| Variable_name                     | Value     |
+-----------------------------------+-----------+
| Aborted_clients                   | 0         |
| Aborted_connects                  | 0         |
<cut>
| Threads_running                   | 1         |
| Uptime                            | 163       |
+-----------------------------------+-----------+
249 rows in set (0.01 sec)

mysql>

Hi!

With SHOW INNODB STATUS you can see queries run by other database users. A SUPER privilege is warranted.

Regards,

Heikki

Heikki says:
"With SHOW INNODB STATUS you can see queries run by other database users. A SUPER privilege is warranted."

That is SHOW PROCESSLIST information, and does not really belong into SHOW INNODB STATUS, then. Or we get SHOW INNODB STATUS without that information unprivileged, and SHOW FULL INNODB STATUS, with that information included and privilege.

Currently there are MySQL consultants on sites where they have only limited access unless the customer is running commands with privilege with them. Having as much status information as possible available without privilege is useful in these scenarios.

Kris,

SHOW INNODB STATUS and SHOW PROCESSLIST are analogous commands. There is little use for SHOW INNODB STATUS if you cannot see the queries that took part in a deadlock, for example.

Regards,

Heikki

I agree, but there is use for INNODB data such as cache efficiency, hash table sizes, operations per second, hit rates and other statistical data that can be useful in determining tuning parameters. And all of this is not SUPER in any way.