Column-level privileges are fairly straightforward, but given how infrequently they are used there are a few areas worth discussing.
Here are a few high-level observations:
Read on for more details on each type of column-level privilege, along with example queries.
Users may only reference columns that they have explicit privileges on. This applies to the entire SELECT statement, not just the SELECT clause. If you try to reference a column that you do not have privileges on in the WHERE, GROUP BY, HAVING, or ORDER BY clause then you will get an error.
To illustrate this I created a table with two rows of sample data for testing:
mysql> create table good_stuff ( -> id int unsigned not null auto_increment primary key, -> ctime timestamp default current_timestamp, -> mtime timestamp default current_timestamp on update current_timestamp, -> is_deleted tinyint not null default 0, -> public varchar(255) null, -> protected varchar(255) null, -> private varchar(255) null -> ) engine = innodb; Query OK, 0 rows affected (0.03 sec) mysql> insert into good_stuff (id,public,protected,private) -> values (DEFAULT,'Hello world!','Red','Secret'); Query OK, 1 row affected (0.00 sec) mysql> insert into good_stuff (id,public,protected,private) -> values (DEFAULT,'Hi Scott','Blue','Boo Scott'); Query OK, 1 row affected (0.01 sec)
If I grant SELECT privileges on two columns in that table to a user named “scott”, then scott may select those two columns:
mysql> -- as root, grant SELECT privileges to scott mysql> grant select (public,protected) on good_stuff to scott@'%'; Query OK, 0 rows affected (0.00 sec)
mysql> -- as scott, test SELECT privileges mysql> select public,protected from good_stuff; +--------------+-----------+ | public | protected | +--------------+-----------+ | Hello world! | Red | | Hi Scott | Blue | +--------------+-----------+ 2 rows in set (0.00 sec)
But scott may not reference another column in the ORDER BY clause:
mysql> -- as scott, test SELECT privileges mysql> select public from good_stuff order by id; ERROR 1143 (42000): SELECT command denied to user 'scott'@'localhost' for column 'id' in table 'good_stuff'
Table-level privileges take precedence, so granting table-level SELECT privileges to a user overrides any column-level SELECT privileges they may have.
If you happen to grant SELECT privileges on all columns in a table to a user, then the user is allowed to run SELECT * queries.
Users may only explicitly insert data into columns for which they have the INSERT privilege. INSERT privileges do not rely on SELECT privileges, so it is possible to configure a user who may write data but not read it. Default values may be used, but only implicitly. If you try to explicitly reference a default value you will get an error.
If I give scott INSERT privileges on only the public column in my table, he can still insert a row as long as he only references that one column, and default values will be used for other columns (id, ctime, mtime, is_deleted).
mysql> -- as root, grant INSERT privileges to scott mysql> grant insert (public) on good_stuff to scott@'%'; Query OK, 0 rows affected (0.00 sec)
For example this works:
mysql> -- as scott, test INSERT privileges mysql> insert into good_stuff (public) values ('Hi everybody'); Query OK, 1 row affected (0.01 sec)
And results in a row like this:
mysql> -- as root, select full row that scott inserted mysql> select * from good_stuff where public = 'Hi everybody'; +----+---------------------+---------------------+------------+--------------+-----------+---------+ | id | ctime | mtime | is_deleted | public | protected | private | +----+---------------------+---------------------+------------+--------------+-----------+---------+ | 3 | 2016-06-30 20:37:39 | 2016-06-30 20:37:39 | 0 | Hi everybody | NULL | NULL | +----+---------------------+---------------------+------------+--------------+-----------+---------+
These statements all fail even though they have the same intent, because they are explicitly referencing columns that scott does not have privileges to INSERT:
mysql> -- as scott, test INSERT privileges mysql> insert into good_stuff (id,public) values (null,'Is it okay if I do this?'); ERROR 1143 (42000): INSERT command denied to user 'scott'@'localhost' for column 'id' in table 'good_stuff' mysql> insert into good_stuff (id,public) values (DEFAULT,'Is it okay if I do this?'); ERROR 1143 (42000): INSERT command denied to user 'scott'@'localhost' for column 'id' in table 'good_stuff'
I would get the same results if I tried to use null or DEFAULT with the other columns that have defaults values (ctime, mtime, is_deleted).
UPDATE statements support column-level privileges much the same way as SELECT and INSERT. In order to explicitly update a column a user needs UPDATE privileges on that column, but columns can be set to default values implicitly. If you reference a column in the WHERE clause of an UPDATE, then you need SELECT privileges on that column.
First I grant UPDATE privileges on the public column to scott:
mysql> -- as root, grant UPDATE privileges to scott mysql> grant update (public) on good_stuff to scott@'%'; Query OK, 0 rows affected (0.01 sec)
This is allowed:
mysql> -- as scott, test UPDATE privileges mysql> update good_stuff set public = lower(public); Query OK, 1 row affected (0.00 sec) Rows matched: 4 Changed: 1 Warnings: 0
This is also allowed:
mysql> -- as scott, test UPDATE privileges mysql> update good_stuff set public = upper(public) where protected = 'Blue'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0
Please note that the previous two statements will only work if scott has both SELECT and UPDATE privileges on the public column, since the existing value is being used in the UPDATE, so that requires both a read and a write.
These are not allowed because the WHERE clause references a column for which scott does not have SELECT privileges:
mysql> -- as scott, test UPDATE privileges mysql> update good_stuff set public = upper(public) where id = 2; ERROR 1143 (42000): SELECT command denied to user 'scott'@'localhost' for column 'id' in table 'good_stuff' mysql> update good_stuff set is_deleted = DEFAULT; ERROR 1143 (42000): UPDATE command denied to user 'scott'@'localhost' for column 'is_deleted' in table 'good_stuff'
Even though scott does not have privileges to update the mtime column, the on update current_timestamp is still invoked implicitly. To verify this I can select back the “Hi everybody” row I selected earlier to confirm that the mtime value changed:
mysql> -- as root, select full row that scott inserted mysql> select * from good_stuff where id = 3; +----+---------------------+---------------------+------------+--------------+-----------+---------+ | id | ctime | mtime | is_deleted | public | protected | private | +----+---------------------+---------------------+------------+--------------+-----------+---------+ | 3 | 2016-06-30 20:37:39 | 2016-06-30 21:15:03 | 0 | hi everybody | NULL | NULL | +----+---------------------+---------------------+------------+--------------+-----------+---------+
As mentioned above, column-level DELETE privileges are not supported in MySQL.