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: