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!
