| Bug #77509 | Using an alias to perform DELETE statement in information_schema | ||
|---|---|---|---|
| Submitted: | 26 Jun 2015 14:22 | Modified: | 27 Jun 2015 5:52 |
| Reporter: | Hotpu Technology | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Errors | Severity: | S1 (Critical) |
| Version: | MySQL5.6.21, 5.6.27, 5.7.8, 5.5.46 | OS: | CentOS |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Access denied information_schema | ||
[26 Jun 2015 14:22]
Hotpu Technology
[26 Jun 2015 15:30]
Hotpu Technology
Exactly speaking,Using an alias of table_name to perform DELETE statement in the information_schema database ，You will get an error like this ： ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'
[27 Jun 2015 5:52]
MySQL Verification Team
Hello Pu Hot, Thank you for the report and test case. Thanks, Umesh
[27 Jun 2015 5:52]
MySQL Verification Team
// 5.6.27
mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name | Value |
+-------------------------+---------------------------------------------------------+
| innodb_version | 5.6.27 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.27-enterprise-commercial-advanced |
| version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64 |
| version_compile_os | linux-glibc2.5 |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)
mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> use test
Database changed
mysql> CREATE TABLE `t` (
-> `id` int(11) DEFAULT NULL,
-> `name` varchar(16) DEFAULT NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> insert into t values(1,'mysql');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t;
+------+-------+
| id | name |
+------+-------+
| 1 | mysql |
+------+-------+
1 row in set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> delete t from test.t t where t.id=1;
Query OK, 1 row affected (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
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> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> delete t from test.t t where t.id=1;
Query OK, 1 row affected (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> use information_schema
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> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> delete t from test.t t where t.id=1;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> delete test.t from test.t where test.t.id=1;
Query OK, 1 row affected (0.00 sec)
mysql>
[27 Jun 2015 5:53]
MySQL Verification Team
// 5.7.8
mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name | Value |
+-------------------------+---------------------------------------------------------+
| innodb_version | 5.7.8 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.7.8-rc-enterprise-commercial-advanced |
| version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+---------------------------------------------------------+
7 rows in set, 1 warning (0.00 sec)
mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test
Database changed
mysql> CREATE TABLE `t` (
-> `id` int(11) DEFAULT NULL,
-> `name` varchar(16) DEFAULT NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t;
Empty set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> delete t from test.t t where t.id=1;
Query OK, 0 rows affected (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
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> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> delete t from test.t t where t.id=1;
Query OK, 0 rows affected (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> use information_schema
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> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> delete t from test.t t where t.id=1;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'
mysql> delete test.t from test.t where test.t.id=1;
Query OK, 0 rows affected (0.00 sec)
[27 Jun 2015 5:53]
MySQL Verification Team
// 5.5.46
mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.5.46 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.5.46 |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | linux2.6 |
+-------------------------+------------------------------+
7 rows in set (0.01 sec)
mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> use test
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> CREATE TABLE `t` (
-> `id` int(11) DEFAULT NULL,
-> `name` varchar(16) DEFAULT NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ERROR 1050 (42S01): Table 't' already exists
mysql> drop table if exists t;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE `t` (
-> `id` int(11) DEFAULT NULL,
-> `name` varchar(16) DEFAULT NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t values(1,'mysql');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t;
+------+-------+
| id | name |
+------+-------+
| 1 | mysql |
+------+-------+
1 row in set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> delete t from test.t t where t.id=1;
Query OK, 1 row affected (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
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> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> delete t from test.t t where t.id=1;
Query OK, 1 row affected (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> use information_schema
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> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> delete t from test.t t where t.id=1;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'
mysql>
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> delete test.t from test.t where test.t.id=1;
Query OK, 1 row affected (0.00 sec)
mysql>
