Grant Privileges on selected columns
Hi everybody,
I have tried to search on the web to get answers, but haven't found anything to solve my issue.
I'm trying to give a user some privileges on selected columns of a table. But when I'm starting a SELECT command, I've got a denied access message.
The user, let's say 'testuser' has been created with no right at the beginning.
I've got a table named 'orderlines' with 42 columns, and I want this testuser to be able to view only a few of them, and update only one.
Here my GRANTs input:
>GRANT SELECT (oli_id,ohd_id,art_id,oli_quantity,oli_status),UPDATE (oli_status) ON TABLE cvbms.orderlines TO 'testuser'@'%';
Result is:
>SHOW GRANTS FOR 'testuser'@'%';
GRANT USAGE ON *.* TO 'testuser'@'%' IDENTIFIED BY PASSWORD '*89F1BC03BC6692B6E08D4BDE239DA642CDD794D6'
GRANT SELECT (oli_quantity, art_id, oli_status, ohd_id, oli_id), UPDATE (oli_status) ON `cvbms`.`orderlines` TO 'testuser'@'%'
When I check the privileges in the schema_information, the privileges are correctly defined.
>SELECT * FROM mysql.columns_priv;
% cvbms testuser orderlines oli_status 0000-00-00 00:00:00 Select,Update
% cvbms testuser orderlines oli_id 0000-00-00 00:00:00 Select
% cvbms testuser orderlines oli_quantity 0000-00-00 00:00:00 Select
% cvbms testuser orderlines ohd_id 0000-00-00 00:00:00 Select
% cvbms testuser orderlines art_id 0000-00-00 00:00:00 Select
But when I connect (on the MySQL Workbench) under this account, I've got a denied access on a SELECT command. Actually, the Workbench shows me the message "Fetching..." and then "Error Code: 1142 SELECT command denied to user 'testuser'@'localhost' for table 'orderlines'"
>SELECT oli_id,ohd_id,art_id,oli_quantity,oli_status FROM orderlines LIMIT 10;
SELECT oli_id,ohd_id,art_id,oli_quantity,oli_status FROM orderlines LIMIT 10 Fetching... 0.000 sec / ?
SELECT oli_id,ohd_id,art_id,oli_quantity,oli_status FROM orderlines LIMIT 10 Error Code: 1142 SELECT command denied to user 'testuser'@'localhost' for table 'orderlines'
Here, I don't know what to do! I may miss something, but what?
I tried to GRANT SELECT on the table (without columns specification), and it worked. But only with columns specifications, I've got an access denial.
A help will be deeply appreciated.
Thank you very much, also on all the answers I found preivously on other topics.
Sylvain