MySQL Bugs: #61853: Weird interaction of traditional privileges and SQL SECURITY

Bug #61853	Weird interaction of traditional privileges and SQL SECURITY
Submitted:	13 Jul 2011 13:24	Modified:	4 Dec 2014 21:20
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.1.58	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
I qw actually looking into this issue
http://stackoverflow.com/questions/6527599/mysql-forgets-who-is-logged-in-command-denied-t...
http://forums.mysql.com/read.php?10,423668,423668#msg-423668

.. but came across another.  But I think they both show something weird for implementation of SQL SECURITY for VIEWS (at least) interacting with traditional privileges a way that they should not.

I cannot reproduce the problem linke to above. But here comes my own.

How to repeat:
-- as root
DROP DATABASE IF EXISTS sqlsectest;
CREATE DATABASE sqlsectest;
USE sqlsectest;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (id INT, txt VARCHAR(20));
GRANT ALL ON sqlsectest.* TO user1;
GRANT ALL ON sqlsectest.* TO user2;
REVOKE SELECT ON sqlsectest.* FROM user2;

-- as user1 
USE sqlsectest;
DROP VIEW IF EXISTS v;
CREATE SQL SECURITY DEFINER VIEW v AS SELECT * FROM t1;

-- as user2
USE sqlsectest; 
SHOW CREATE VIEW v;
-- Error Code : 1142
-- SELECT command denied to user 'user2'@'localhost' for table 'v'

-- as root
GRANT ALL ON sqlsectest.* TO user2;

-- *new connection* as user2
USE sqlsectest; 
SHOW CREATE VIEW v;
-- success

Suggested fix:
I cannot understand why SELECT privilege is required to execute SHOW statements. This page http://dev.mysql.com/doc/refman/5.0/en/stored-programs-security.html tells nothing about such restriction as far as I can see. I am not trying to read any DATA.

I should have linked to the 5.1 manual:
http://dev.mysql.com/doc/refman/5.1/en/stored-programs-security.html

Fixed typo in synopsis.

Thank you for the report.

According to http://dev.mysql.com/doc/refman/5.1/en/show-create-view.html this is not a bug:

Use of SHOW CREATE VIEW requires the SHOW VIEW privilege and the SELECT privilege for the view in question.

@Sveta .. I wonder if you could give any *reasonable* reason for this. Is there any security consideration/concern for instance or is it just an imperfection with current MySQL code?

In my understanding your reply can be translated to "yes we have a bug and we have documented it".

Peter,

there was rationale, but it is described in private worklog, so I can not share it.

OK! We all have our secrets! :-)

I am opening this report again.

We had one user experiencing this yesterday. He was connecting as a non-anonymous user GRANTS "GRANT ALL on *.* .." and on SHOW TABLE STATUS the server returned "access denied to user ''@'%'" (ie to an anonymous user).

Can we have some information if this is fixed and from what server versions? And what the conditions are/were for this to happen?

I don't find the secrecy here acceptable.

The problem reported by ouruser is exactly like described in the Stackoverflow report:

Running show grants; indicates that I am logged in as a user with all privileges on a database.

Running show table status; results in an error. And the error does not show the username I am logged in as!

It's as if, for this command, mysql forgets who I am. Other select statements work fine. Can anyone explain this? How to fix? Thanks.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.13-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show grants;
+---------------------------------------------------------------------------------------------------------------------+
| Grants for php@localhost                                                                                            |
+---------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'php'@'localhost' IDENTIFIED BY PASSWORD '*8F5FF90079BC601F8EA7C148475658E65A0C029D' |
| GRANT ALL PRIVILEGES ON `sunflower_work`.* TO 'php'@'localhost'                                                     |
| GRANT ALL PRIVILEGES ON `news_demo`.* TO 'php'@'localhost'                                                          |
| GRANT ALL PRIVILEGES ON `news_base`.* TO 'php'@'localhost'                                                          |
+---------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> show table status from sunflower_work;
ERROR 1143 (42000): SELECT command denied to user ''@'%' for column 'uid' in table 'users'
mysql>

Thank you for the feedback.

Please provide output of SELECT user, host FROM mysql.user

@Sveta .. you must definitely have lost your mind, asking me this now.

This was originally reported in 2011 (and reopened 3 months ago). You told years ago that there was some rationale that Oracle would not expose details of this bug to the outside world. Why should I provide more information, as this is the case? This is complete nonsense IMO.

Besides so much time has passed that I really don't recall any details and don't have any test case now. A more speedy response from Oracle support would be in place. And also a clear statement if and when the bug I reported back in 2011 was fixed (if it was and if you want to *expose* it now).

In short: I am neither your servant nor babysiter!

Peter,

such error when MySQL treats one user as another one usually happens when something wrong happened with privilege tables. This is why I asked you about their content.

I also cannot repeat behavior, described in your last comments, with different views and users:

mysql> show table status from  sqlsectest;
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| t1   | MyISAM |      10 | Dynamic    |    0 |              0 |           0 | 281474976710655 |         1024 |         0 |           NULL | 2014-12-05 00:02:41 | 2014-12-05 00:02:41 | NULL       | latin1_swedish_ci |     NULL |                |         |
| v    | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL                | NULL                | NULL       | NULL              |     NULL | NULL           | VIEW    |
| v1   | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL                | NULL                | NULL       | NULL              |     NULL | NULL           | VIEW    |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
3 rows in set (0.00 sec)

mysql> use sqlsectest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show create view v;
+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View                                                                                                                                                      | character_set_client | collation_connection |
+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v    | CREATE ALGORITHM=UNDEFINED DEFINER=`user1`@`%` SQL SECURITY DEFINER VIEW `v` AS select `sqlsectest`.`t1`.`id` AS `id`,`sqlsectest`.`t1`.`txt` AS `txt` from `t1` | utf8                 | utf8_general_ci      |
+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

mysql> show create view v1;
+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View                                                                                                                                                  | character_set_client | collation_connection |
+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v1   | CREATE ALGORITHM=UNDEFINED DEFINER=``@`%` SQL SECURITY DEFINER VIEW `v1` AS select `sqlsectest`.`t1`.`id` AS `id`,`sqlsectest`.`t1`.`txt` AS `txt` from `t1` | utf8                 | utf8_general_ci      |
+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

mysql> show grants;
+----------------------------------------------------------------------------------------------------------------+
| Grants for peter1@%                                                                                            |
+----------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'peter1'@'%' IDENTIFIED BY PASSWORD '*F3A2A51A9B0F2BE2468926B4132313728C250DBF' |
| GRANT ALL PRIVILEGES ON `sqlsectest`.* TO 'peter1'@'%'                                                         |
+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select user(), current_user();
+------------------+----------------+
| user()           | current_user() |
+------------------+----------------+
| peter1@localhost | peter1@%       |
+------------------+----------------+
1 row in set (0.00 sec)

So I tend to think that something wrong happened with your privilege tables (corruption?) and this is not a bug.