Bug #62908 | User may create tables with names reserved for log tables | ||
---|---|---|---|
Submitted: | 24 Oct 2011 11:47 | Modified: | 24 Oct 2011 20:02 |
Reporter: | Peter Laursen (Basic Quality Contributor) | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Logging | Severity: | S3 (Non-critical) |
Version: | 5.1+ | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | qc |
[24 Oct 2011 11:47]
Peter Laursen
[24 Oct 2011 11:48]
Peter Laursen
I forgot version. Now added.
[24 Oct 2011 11:51]
Peter Laursen
Or maybe rather: The table names used by log tables should be reserved .. *in the `mysql` database*. (I am perfectly aware that user should not create such table in `mysql` database . but people do weird things sometimes.)
[24 Oct 2011 12:55]
Valeriy Kravchuk
While I agree with your concerns, I doubt we can (and should) prevent root from doing anything is mysql database... Now, my experience is also a bit different: macbook-pro:5.1 openxs$ bin/mysql -uroot test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 42 Server version: 5.1.60 Source distribution Copyright (c) 2000, 2011, 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> SET GLOBAL general_log = 0; Query OK, 0 rows affected (0.02 sec) mysql> SET GLOBAL log_output = 'file'; Query OK, 0 rows affected (0.00 sec) mysql> mysql> USE mysql; 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> DROP TABLE IF EXISTS general_log; Query OK, 0 rows affected (0.18 sec) mysql> CREATE TABLE general_log(id INT); Query OK, 0 rows affected (0.08 sec) mysql> SET GLOBAL log_output = 'TABLE'; Query OK, 0 rows affected (0.00 sec) mysql> SET GLOBAL general_log = 1; Query OK, 0 rows affected (0.02 sec) mysql> SHOW COLUMNS FROM general_log; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.02 sec) mysql> select 1; +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.00 sec) mysql> select * from mysql.general_log; Empty set (0.00 sec) mysql> desc mysql.general_log; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.02 sec) So, it is not the user table that is dropped silently, it is there, just logging to table does not happen it seems.
[24 Oct 2011 13:56]
Peter Laursen
It seems that you are right. I tried again with 5.1.59: SET GLOBAL general_log = 0; SET GLOBAL log_output = 'file'; USE mysql; DROP TABLE IF EXISTS general_log; CREATE TABLE general_log(id INT); INSERT INTO general_log VALUES (1); -- Error Code: 1556 -- You can't use locks with log tables. -- -- (so here the server *knows* that this is a reserved table name of some kind) SELECT 1; SELECT * FROM general_log; -- empty set So 1) User may specify and create a `general_log` table. 2) But she will not be able to INSERT INTO it (and the error message is also not good IMHO - but not the first time we have discussed error messages here.) 3) Starting logging to table fails with no warning or error sent to the client. 4) I also now notice that the error log contains message "Failed to write to mysql.general_log" for every statement after logging was (attempted) started. I still think the best solution would be to reserve the table name for the server process. What is the idea of letting user create it when it cannot even be used/INSERTED to? But I know of course that system tables have no special protection in MySQL. You can manipulate `mysql`.`user` and more as well if you only have privileges.. No. 2 solution is performing a check on the statement "SET GLOBAL log_output = 'table';", return an error to the client if a table non-fit for logging exists, and not just let logging fail (with a message in error log that may only be seen too late). Anyway I just came across this and though I would report it. It is not important for me personally.
[24 Oct 2011 20:02]
Valeriy Kravchuk
Verified with 5.1.60. I think your suggested solution #2 (check if table with improper structure exists at the moment of enabling logging to table) makes perfect sense.