In MySQL permissions can be granted to users just for few specific columns in a table, instead of creating a view and then granting access to views
Column level permissions can be granted using below syntax
GRANT PRIVILEGE(col1,col2,..) ON dname.tablename TO 'username'@'location';
Here is an example of granting access only to first_name column of user table for user reporter.
GRANT SELECT(first_name) ON mydb.user TO 'reporter'@'%';
Now when the user lists the columns in a table he can see only the columns he has access to. When select * is issued on the table user will get an access denied message on the column they dont have access to.
mysql> SHOW COLUMNS FROM mydb.user;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| first_name | varchar(40) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
1 row in set (0.01 sec)
mysql> SELECT * FROM mydb.user;
ERROR 1143 (42000): SELECT command denied to user 'reporter'@'localhost' for column 'id' in table 'user'
Columns level privileges granted are stored in MySQL table mysql.columns_priv.
mysql>SELECT * FROM mysql.columns_priv WHERE User ='reporter'\G
*************************** 1. row ***************************
Host: %
Db: mydb
User: reporter
Table_name: user
Column_name: first_name
Timestamp: 0000-00-00 00:00:00
Column_priv: Select
1 row in set (0.00 sec)
However only few specific privileges can be granted on a column level like INSERT, REFERENCES, SELECT and UPDATE as of MySQL v8
References: