Description:
Creating a table like an existing table fails if the user does not have 'select' privs on the old table with error:
ERROR 1142 (42000): SELECT command denied to user 'omer'@'localhost' for table 't1'
Note: this is observed in 5.0.44 but worked fine in 5.0.42.
Details:
In 5.0.42 - OK
=========
omer@linux:~/ent-5.0.42-gpl/mysql-test> ../bin/mysql --user=root --port=9306 --protocol=tcp
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.42-enterprise-gpl-log MySQL Enterprise Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create database omer_db;
Query OK, 1 row affected (0.00 sec)
mysql> use omer_db;
Database changed
mysql> create table t1 (i int);
Query OK, 0 rows affected (0.00 sec)
mysql> create user 'omer'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> grant create on omer_db.* to 'omer'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
omer@linux:~/ent-5.0.42-gpl/mysql-test> ../bin/mysql --user=omer --port=9306 --protocol=tcp
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.42-enterprise-gpl-log MySQL Enterprise Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show grants;
+---------------------------------------------------+
| Grants for omer@localhost |
+---------------------------------------------------+
| GRANT USAGE ON *.* TO 'omer'@'localhost' |
| GRANT CREATE ON `omer_db`.* TO 'omer'@'localhost' |
+---------------------------------------------------+
2 rows in set (0.01 sec)
mysql> use omer_db;
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 tables;
+-------------------+
| Tables_in_omer_db |
+-------------------+
| t1 |
+-------------------+
1 row in set (0.00 sec)
mysql> create table zoo like t1; <-- This line will fail in 5.0.44
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+-------------------+
| Tables_in_omer_db |
+-------------------+
| t1 |
| zoo |
+-------------------+
2 rows in set (0.00 sec)
mysql> quit
Bye
omer@linux:~/ent-5.0.42-gpl/mysql-test>
omer@linux:~/public_html>
----------------------------------------------------------------------------
In 5.0.44 - Not OK
=========
omer@linux:~/ent-5.0.44-com/mysql-test> ../bin/mysql --user=root --port=9306 --protocol=tcp
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.44-enterprise-log MySQL Enterprise Server (Commercial)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create database omer_db;
Query OK, 1 row affected (0.00 sec)
mysql> use omer_db;
Database changed
mysql> create table t1 (i int);
Query OK, 0 rows affected (0.00 sec)
mysql> create user 'omer'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> grant create on omer_db.* to 'omer'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
omer@linux:~/ent-5.0.44-com/mysql-test> ../bin/mysql --user=omer --port=9306 --protocol=tcp
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.44-enterprise-log MySQL Enterprise Server (Commercial)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show grants;
+---------------------------------------------------+
| Grants for omer@localhost |
+---------------------------------------------------+
| GRANT USAGE ON *.* TO 'omer'@'localhost' |
| GRANT CREATE ON `omer_db`.* TO 'omer'@'localhost' |
+---------------------------------------------------+
2 rows in set (0.00 sec)
mysql> use omer_db;
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 tables;
+-------------------+
| Tables_in_omer_db |
+-------------------+
| t1 |
+-------------------+
1 row in set (0.00 sec)
mysql> create table zoo like t1; <-- This line works in 5.0.42
ERROR 1142 (42000): SELECT command denied to user 'omer'@'localhost' for table 't1'
mysql> show tables;
+-------------------+
| Tables_in_omer_db |
+-------------------+
| t1 |
+-------------------+
1 row in set (0.00 sec)
mysql> quit
Bye
omer@linux:~/ent-5.0.44-com/mysql-test>
How to repeat:
1) Start the server using mysql-test-run.pl --start-and-exit
2) Log in as root
mysql --user=root --port=9306 --protocol=tcp
3) Run the following commands:
create database omer_db;
use omer_db;
create table t1 (i int);
create user 'omer'@'localhost';
grant create on omer_db.* to 'omer'@'localhost';
quit
4) Log in as omer
bin/mysql --user=omer --port=9306 --protocol=tcp
5) Run the follwoing commands:
show grants;
use omer_db;
show tables;
create table zoo like t1;
show tables;
quit
