| Bug #73335 | Inconsistent error messages when mistyping database and table names | ||
|---|---|---|---|
| Submitted: | 20 Jul 2014 17:38 | Modified: | 22 Jul 2014 7:56 |
| Reporter: | Peter Laursen (Basic Quality Contributor) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Errors | Severity: | S3 (Non-critical) |
| Version: | any - tested on 5.6.12, 5.6.19, 5.6.21 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[20 Jul 2014 17:38]
Peter Laursen
[20 Jul 2014 17:42]
Peter Laursen
Synopsis spelling corrected.
[21 Jul 2014 8:07]
MySQL Verification Team
Thank you for the report. Imho this is an expected behavior. I observed that this "inconsistency' is observed when tried with valid and anonymous user. // With the valid user mysql> show variables like '%version%'; +-------------------------+---------------------------------------------------------+ | Variable_name | Value | +-------------------------+---------------------------------------------------------+ | innodb_version | 5.6.21 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.6.21-enterprise-commercial-advanced-log | | version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) | | version_compile_machine | x86_64 | | version_compile_os | Linux | +-------------------------+---------------------------------------------------------+ 7 rows in set (0.00 sec) mysql> set sql_mode=''; Query OK, 0 rows affected (0.00 sec) mysql> create database if not exists customer_portal; Query OK, 1 row affected (0.00 sec) mysql> use customer_portal; Database changed mysql> drop table if exists order_details; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> create table order_details(order_id int not null auto_increment primary key)engine=innodb; insert into order_details values(); insert into order_details values(); insert into order_details values(); insert into order_details values(); insert into order_details values(); Query OK, 0 rows affected (0.40 sec) mysql> insert into order_details values(); Query OK, 1 row affected (0.08 sec) mysql> insert into order_details values(); Query OK, 1 row affected (0.11 sec) mysql> insert into order_details values(); Query OK, 1 row affected (0.03 sec) mysql> insert into order_details values(); Query OK, 1 row affected (0.03 sec) mysql> insert into order_details values(); Query OK, 1 row affected (0.04 sec) mysql> select * from order_details; +----------+ | order_id | +----------+ | 1 | | 2 | | 3 | | 4 | | 5 | +----------+ 5 rows in set (0.00 sec) mysql> SELECT * FROM `customer_portal`.`or der_details` ORDER BY `order_id` DESC LIMIT 100; ERROR 1146 (42S02): Table 'customer_portal.or der_details' doesn't exist mysql> SELECT * FROM `customer_po rtal`.`order_details` ORDER BY `order_id` DESC LIMIT 100; ERROR 1146 (42S02): Table 'customer_po rtal.order_details' doesn't exist The only issue I see here is that - it complains about table name missing rather database missing specific errors. ## anonymous user mysql> show grants; +--------------------------------------+ | Grants for @localhost | +--------------------------------------+ | GRANT USAGE ON *.* TO ''@'localhost' | +--------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM `customer_portal`.`or der_details` ORDER BY `order_id` DESC LIMIT 100; ERROR 1142 (42000): SELECT command denied to user ''@'localhost' for table 'or der_details' mysql> SELECT * FROM `customer_po rtal`.`order_details` ORDER BY `order_id` DESC LIMIT 100; ERROR 1142 (42000): SELECT command denied to user ''@'localhost' for table 'order_details' ^^ This is expected behavior. For database-related requests (INSERT, UPDATE, and so on), the server first checks the user's global privileges by looking in the user table row. If the row permits the requested operation, access is granted. If the global privileges in the user table are insufficient, the server determines the user's database-specific privileges by checking the db table: The server looks in the db table for a match on the Host, Db, and User columns. The Host and User columns are matched to the connecting user's host name and MySQL user name. The Db column is matched to the database that the user wants to access. If there is no row for the Host and User, access is denied. Please see http://dev.mysql.com/doc/refman/5.6/en/request-access.html If you are seeing the inconsistency with valid user then could you please provide the complete test case to reproduce at our end? Thanks, Umesh
[21 Jul 2014 9:05]
Peter Laursen
Not quite what I get. But GRANTS may matter of course. But I did a mistake. I wrote "tested on 5.6.19" (now corrected). I actually tested on a 5.6.12 server (running on Amazon AWS - in CentOS, I believe). It is a live server hosting our company's CRM systems etc., so I have no immediate option to upgrade it. Relevant GRANTS are GRANT PROCESS ON *.* TO 'portal_readonly'@'%' IDENTIFIED BY PASSWORD ... GRANT SELECT ON `performance_schema`.* TO 'portal_readonly'@'%' -- << this a relic. P_S is OFF on the server nowadays. GRANT SELECT, CREATE TEMPORARY TABLES, SHOW VIEW ON `customer_portal`.* TO 'portal_readonly'@'%' (omitted grants specific for other databases/schemas an tables on the server). I notice that there is no SHOW DATABASES privilege granted to the user. Could that matter?
[21 Jul 2014 11:04]
MySQL Verification Team
Thanks for the feedback. mysql> show grants for 'portal_readonly'@'localhost'; +--------------------------------------------------------------------------------------------------------------------------+ | Grants for portal_readonly@localhost | +--------------------------------------------------------------------------------------------------------------------------+ | GRANT PROCESS ON *.* TO 'portal_readonly'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' | | GRANT SELECT ON `performance_schema`.* TO 'portal_readonly'@'localhost' | | GRANT SELECT, CREATE TEMPORARY TABLES, SHOW VIEW ON `customer_portal`.* TO 'portal_readonly'@'localhost' | +--------------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM `customer_portal`.`or der_details` ORDER BY `order_id` DESC LIMIT 100; ERROR 1146 (42S02): Table 'customer_portal.or der_details' doesn't exist mysql> SELECT * FROM `customer_po rtal`.`order_details` ORDER BY `order_id` DESC LIMIT 100; ERROR 1142 (42000): SELECT command denied to user 'portal_readonly'@'localhost' for table 'order_details' mysql> ^^ I'm able to repeat on what you are seeing. But again, I would say this is by design that you cant return "table not found" for a database you don't have select privs to because then you're leaking information. The server looks in the db table for a match on the Host, Db, and User columns. The Host and User columns are matched to the connecting user's host name and MySQL user name. The Db column is matched to the database that the user wants to access. If there is no row for the Host and User, access is denied. See here http://dev.mysql.com/doc/refman/5.6/en/request-access.html Not sure whether it is feasible and valid to display whether "xyz" db exists or not and access denied seems to be appropriate to avoid leaking any further info to the user who doesn't have access to "xyz" database.
[21 Jul 2014 11:37]
Peter Laursen
Hi Umesh .. You have set this to 'need feedback', but I really don't know what kind of feedback should rovide. My concern is an end_user concern. It is confusing to get an error listing lack of privileges to a database that does not exist at all. 'database does not exist' would be much more helpful. When I experienced this yesterday it actually took me 10-15 minutes to figure out the problem. Simply because the error message pointed my attention in the wrong direction. Now.. MySQL isn't - and never was - famous for quality and helpfull error messages. So this is just one more example IMO.
[22 Jul 2014 7:56]
MySQL Verification Team
Thank you for the feedback. Marking this as FR for the meaningful "error" other than just access denied which can confuse user.
[6 Aug 2014 11:32]
Hartmut Holzgraefe
I think "Access denied" is the correct error here, the user would not have access to the mistyped database either way, and "Database doesn't exist" would expose information about the mysql instance that this specific user is not supposed to have ...
[20 Jul 2018 7:50]
Hartmut Holzgraefe
And four years later I still think the same. But to make the point more clear: As root: CREATE USER foo@localhost; GRANT ALL ON no_such_db_1.* TO foo@localhost; GRANT ALL ON no_such_db_1.no_such_table_1 TO foo@localhost; Now as user foo: -- user has permissions on the whole database > select * from no_such_db_1.no_such_table_1; ERROR 1146 (42S02): Table 'no_such_db_1.no_such_table_1' doesn't exist > select * from no_such_db_1.no_such_table_2; ERROR 1146 (42S02): Table 'no_such_db_1.no_such_table_2' doesn't exist -- user has access to one table in this DB only > select * from no_such_db_2.no_such_table_1; ERROR 1146 (42S02): Table 'no_such_db_2.no_such_table_1' doesn't exist > select * from no_such_db_2.no_such_table_2; ERROR 1142 (42000): SELECT command denied to user 'foo'@'localhost' for table 'no_such_table_2' -- user has no access to this db at all > select * from no_such_db_3.no_such_table_1; ERROR 1142 (42000): SELECT command denied to user 'foo'@'localhost' for table 'no_such_table_1' > select * from no_such_db_3.no_such_table_2; ERROR 1142 (42000): SELECT command denied to user 'foo'@'localhost' for table 'no_such_table_2' So the behavior is correct: * first permissions are checked, if the user has no permissions on a table (neither via table nor db GRANT) a "command denied" error is raised * only if the user has sufficient privileges the actual table is checked, and "Table ... doesn't exist" is raised if there's no such table Also, as this example shows, the premise that this is different depending on whether a typo happens in the db or table name was wrong in the first place, it simply depends on whether there are privileges covering the mistyped name or not. So please close this as "Not a bug"
[20 Jul 2018 8:07]
Hartmut Holzgraefe
If the message criteria were in reverse you could brute force probe a database for the existence of specific tables, even without having any privileges on the database. E.g. INFORMATION_SCHEMA.TABLES will only show a user tables from those databases the user as privileges on. So via I_S.TABLES I could not find out of a specific table in a database I don't have access to exists. But if the "Table ... doesn't exist" check came before the privilege check I could find out if a specific table exists even without privileges on the tables database. So it is a "security wins over ease of use" thing, and there should be no feature requests that suggest to weaken security in favor of ease of use IMHO
