Description:
With the privileges given below, if I prepare the given query, I get:
Query: insert into ctstable2 values(?,?,?,?)
Error: insert command denied to user: 'cts@66.93.114.100' for column 'unknown' in table 'ctstable2'
If I fully-qualify the column names, the query works. Also, if I just issue the query by hand
(i.e. 'insert into ctstable2 values(1,2,3,4)') it works.
mysql> describe ctstable2;
+----------+-------------+-----------+------+-----+---------+-------+
| Field | Type | Collation | Null | Key | Default | Extra |
+----------+-------------+-----------+------+-----+---------+-------+
| KEY_ID | int(11) | binary | | PRI | 0 | |
| COF_NAME | varchar(32) | latin1 | YES | | NULL | |
| PRICE | float | binary | YES | | NULL | |
| TYPE_ID | int(11) | binary | YES | MUL | NULL | |
+----------+-------------+-----------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> select * from mysql.user where user='cts' \G
*************************** 1. row ***************************
Host: %
User: cts
Password: *ec2471db56cfa93f40d63746fa6336f5c32ed9a0c8a5
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
*************************** 2. row ***************************
Host: 66.93.114.100
User: cts
Password: *c8b1281d70fc1490a716360336119ce9f40f36d318a1
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
2 rows in set (0.01 sec)
mysql> select * from mysql.tables_priv where user='cts' \G
*************************** 1. row ***************************
Host: %
Db: cts
User: cts
Table_name: cts
Grantor: root@localhost
Timestamp: 2003-02-21 20:08:38
Table_priv: Select,Insert,Update,Delete,Create,Drop,References,Index,Alter
Column_priv:
1 row in set (0.00 sec)
mysql>
mysql> select * from mysql.columns_priv where user='cts' \G
Empty set (0.00 sec)
mysql> select * from mysql.db where user='cts' \G
*************************** 1. row ***************************
Host: %
Db: cts
User: cts
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
*************************** 2. row ***************************
Host: 66.93.114.100
Db: cts
User: cts
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
2 rows in set (0.00 sec)
How to repeat:
Description: With the privileges given below, if I prepare the given query, I get: Query: insert into ctstable2 values(?,?,?,?) Error: insert command denied to user: 'cts@66.93.114.100' for column 'unknown' in table 'ctstable2' If I fully-qualify the column names, the query works. Also, if I just issue the query by hand (i.e. 'insert into ctstable2 values(1,2,3,4)') it works. mysql> describe ctstable2; +----------+-------------+-----------+------+-----+---------+-------+ | Field | Type | Collation | Null | Key | Default | Extra | +----------+-------------+-----------+------+-----+---------+-------+ | KEY_ID | int(11) | binary | | PRI | 0 | | | COF_NAME | varchar(32) | latin1 | YES | | NULL | | | PRICE | float | binary | YES | | NULL | | | TYPE_ID | int(11) | binary | YES | MUL | NULL | | +----------+-------------+-----------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> select * from mysql.user where user='cts' \G *************************** 1. row *************************** Host: % User: cts Password: *ec2471db56cfa93f40d63746fa6336f5c32ed9a0c8a5 Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N Shutdown_priv: N Process_priv: N File_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Show_db_priv: N Super_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Execute_priv: N Repl_slave_priv: N Repl_client_priv: N ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 *************************** 2. row *************************** Host: 66.93.114.100 User: cts Password: *c8b1281d70fc1490a716360336119ce9f40f36d318a1 Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N Shutdown_priv: N Process_priv: N File_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Show_db_priv: N Super_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Execute_priv: N Repl_slave_priv: N Repl_client_priv: N ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 2 rows in set (0.01 sec) mysql> select * from mysql.tables_priv where user='cts' \G *************************** 1. row *************************** Host: % Db: cts User: cts Table_name: cts Grantor: root@localhost Timestamp: 2003-02-21 20:08:38 Table_priv: Select,Insert,Update,Delete,Create,Drop,References,Index,Alter Column_priv: 1 row in set (0.00 sec) mysql> mysql> select * from mysql.columns_priv where user='cts' \G Empty set (0.00 sec) mysql> select * from mysql.db where user='cts' \G *************************** 1. row *************************** Host: % Db: cts User: cts Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Grant_priv: N References_priv: Y Index_priv: Y Alter_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y *************************** 2. row *************************** Host: 66.93.114.100 Db: cts User: cts Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Grant_priv: N References_priv: Y Index_priv: Y Alter_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y 2 rows in set (0.00 sec) How to repeat: