Selecting data by user in views

I have found an interesting request in the MySQL newsgroup asking, how data can be selected as of a user column in a table. Only the user who is stored in a record should be able to access and alter this row.

So this is a perfect example to demonstrate, how this can be accomplished by a view.

First I created a table called tt in the database test and filled it with sample data:

create table tt (
id int unsigned not null auto_increment primary key,
charvalue char(1) not null,
intvalue int unsigned not null,
allowed_user char(6) not null);

insert into tt (charvalue, intvalue, allowed_user) 
values ('a', 560, 'user_g');
insert into tt (charvalue, intvalue, allowed_user) 
values ('b', 931, 'user_g');
insert into tt (charvalue, intvalue, allowed_user) 
values ('c', 661, 'user_c');
insert into tt (charvalue, intvalue, allowed_user) 
values ('d', 246, 'user_f');
insert into tt (charvalue, intvalue, allowed_user) 
values ('e', 452, 'user_g');
insert into tt (charvalue, intvalue, allowed_user) 
values ('f', 130, 'user_c');
insert into tt (charvalue, intvalue, allowed_user) 
values ('g', 341, 'user_f');
insert into tt (charvalue, intvalue, allowed_user) 
values ('h', 847, 'user_a');
insert into tt (charvalue, intvalue, allowed_user) 
values ('i', 417, 'user_b');
insert into tt (charvalue, intvalue, allowed_user) 
values ('j', 151, 'user_f');
insert into tt (charvalue, intvalue, allowed_user) 
values ('k', 11, 'user_e');
insert into tt (charvalue, intvalue, allowed_user) 
values ('l', 66, 'user_a');
insert into tt (charvalue, intvalue, allowed_user) 
values ('m', 375, 'user_g');
insert into tt (charvalue, intvalue, allowed_user) 
values ('n', 412, 'user_g');
insert into tt (charvalue, intvalue, allowed_user) 
values ('o', 358, 'user_c');
insert into tt (charvalue, intvalue, allowed_user) 
values ('p', 78, 'user_c');
insert into tt (charvalue, intvalue, allowed_user) 
values ('q', 463, 'user_e');
insert into tt (charvalue, intvalue, allowed_user) 
values ('r', 773, 'user_e');
insert into tt (charvalue, intvalue, allowed_user) 
values ('s', 165, 'user_b');
insert into tt (charvalue, intvalue, allowed_user) 
values ('t', 740, 'user_e');
insert into tt (charvalue, intvalue, allowed_user) 
values ('u', 875, 'user_c');
insert into tt (charvalue, intvalue, allowed_user) 
values ('v', 793, 'user_a');
insert into tt (charvalue, intvalue, allowed_user) 
values ('w', 677, 'user_a');
insert into tt (charvalue, intvalue, allowed_user) 
values ('x', 133, 'user_b');
insert into tt (charvalue, intvalue, allowed_user) 
values ('y', 994, 'user_d');
insert into tt (charvalue, intvalue, allowed_user) 
values ('z', 479, 'user_e');

The column allowed_user specifies, which user should be allowed to access this row.

The next step is to wrap this table into a view (called tt_select) and limit it to be accessible for only the user who is logged in:

create view tt_select as
select * from tt
where allowed_user = substring(user(), 1, locate('@', user()) - 1);

The substring and locate functions cut off the host from the user name, so e.g. user@localhost becomes user (if the limit should apply to both user and host, you can also store the complete user@host information in the table and query 'where allowed_user = user()').

Finally I created the user accounts and granted the privileges exclusively for the view (not for the table):

GRANT SELECT , INSERT , UPDATE , DELETE ON `test`.`tt_select` 
TO 'user_a'@'%' IDENTIFIED BY 'abc123';
GRANT SELECT , INSERT , UPDATE , DELETE ON `test`.`tt_select` 
TO 'user_b'@'%' IDENTIFIED BY 'abc123';
GRANT SELECT , INSERT , UPDATE , DELETE ON `test`.`tt_select` 
TO 'user_c'@'%' IDENTIFIED BY 'abc123';
GRANT SELECT , INSERT , UPDATE , DELETE ON `test`.`tt_select` 
TO 'user_d'@'%' IDENTIFIED BY 'abc123';
GRANT SELECT , INSERT , UPDATE , DELETE ON `test`.`tt_select` 
TO 'user_e'@'%' IDENTIFIED BY 'abc123';
GRANT SELECT , INSERT , UPDATE , DELETE ON `test`.`tt_select` 
TO 'user_f'@'%' IDENTIFIED BY 'abc123';
GRANT SELECT , INSERT , UPDATE , DELETE ON `test`.`tt_select` 
TO 'user_g'@'%' IDENTIFIED BY 'abc123';

That's it :-). Lets test it using the MySQL console client, assuming that the user 'user_g' logs in:

E:\>mysql -u user_g -pabc123 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 181 to server version: 5.0.15-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| tt_select      |
+----------------+
1 row in set (0.00 sec)

mysql> select * from tt_select;
+----+-----------+----------+--------------+
| id | charvalue | intvalue | allowed_user |
+----+-----------+----------+--------------+
|  1 | a         |      560 | user_g       |
|  2 | b         |      931 | user_g       |
|  5 | e         |      452 | user_g       |
| 13 | m         |      375 | user_g       |
| 14 | n         |      412 | user_g       |
+----+-----------+----------+--------------+
5 rows in set (0.00 sec)

mysql> update tt_select set intvalue=584 where id=13;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tt_select;
+----+-----------+----------+--------------+
| id | charvalue | intvalue | allowed_user |
+----+-----------+----------+--------------+
|  1 | a         |      560 | user_g       |
|  2 | b         |      931 | user_g       |
|  5 | e         |      452 | user_g       |
| 13 | m         |      584 | user_g       |
| 14 | n         |      412 | user_g       |
+----+-----------+----------+--------------+
5 rows in set (0.00 sec)

mysql> delete from tt_select where id=13;
Query OK, 1 row affected (0.00 sec)

mysql> select * from tt_select;
+----+-----------+----------+--------------+
| id | charvalue | intvalue | allowed_user |
+----+-----------+----------+--------------+
|  1 | a         |      560 | user_g       |
|  2 | b         |      931 | user_g       |
|  5 | e         |      452 | user_g       |
| 14 | n         |      412 | user_g       |
+----+-----------+----------+--------------+
4 rows in set (0.02 sec)

mysql> insert into tt_select values (13, 'm', 375, 'user_g');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tt_select;
+----+-----------+----------+--------------+
| id | charvalue | intvalue | allowed_user |
+----+-----------+----------+--------------+
|  1 | a         |      560 | user_g       |
|  2 | b         |      931 | user_g       |
|  5 | e         |      452 | user_g       |
| 13 | m         |      375 | user_g       |
| 14 | n         |      412 | user_g       |
+----+-----------+----------+--------------+
5 rows in set (0.00 sec)

mysql> select * from tt;
ERROR 1142 (42000): SELECT command denied to user 
'user_g'@'localhost' for table 'tt'
mysql>

As we can see - it works fine!