How to hide column from user in MySQL table

Question

I figured that this would be easy, but apparently (and to my frustration) it is not.

I have a user. We will say the user's name is 'user'. I simply want this user to NOT be able to see a column in my MySQL database.

I am using HeidiSQL. There seems to be no way to use the GUI to disallow users to see a column in a table. So I assumed that the following would work;

GRANT SELECT ON database_name.user TO 'user'@'%';
GRANT SELECT (column_name) ON database_name.table_name TO 'user'@'%';
REVOKE SELECT (column_name) ON database_name.table_name FROM 'user'@'%';

But it doesn't. Whenever I flush privileges and log in through the user, I still see the column that I do not want the user to see.

What is the algorithm for this, exactly? I'd like to assume this is possible.

Thanks in advance,

-Anthony

Answer 1

We can grant/revoke privileges at the column level as MySQL stores column privileges in the mysql.columns_priv table, and should be applied for single column in a table.

GRANT SELECT (col1), INSERT (col1,col2) ON dbname.tblname TO 'user'@'hostname';