Hack 61. Set Security Based on Rows

You can use a VIEW to set user-based access rights on a column-by-column basis. You can also set access rights on a per-row basis.

The standard SQL permissions system lets you control access on a per-user, per-table basis. If you want scott to be able to see the t table, for instance, you issue this command:

GRANT SELECT ON t TO scott

You can get even finer control by setting up a view that shows only part of the table, and granting access to that instead. The following creates a view, v, which excludes rows with the words Top Secret in the f column. You then revoke Scott's access to t and grant it on v:

CREATE VIEW v AS SELECT * FROM t WHERE f != 'Top Secret'; REVOKE SELECT ON t FROM scott; GRANT SELECT ON v TO scott;

You can do the same kind of thing with INSERT, UPDATE, and DELETE, in place of the SELECT command. You can also refer to the PUBLIC role to mean any named user.

8.4.1. Make Use of Usernames

Suppose you've got an employee table where every staff member has a department, a boss, and a salary (see Table 8-3). You can set up the system so that each user has permission to see only the details of their minions.

In the employee table it is important that the actual SQL username is included in the table details because this hack uses the CURRENT_USER [Hack #59] to control permissions.

You can create a single VIEW, minions, that looks different to each user:

CREATE VIEW minions AS SELECT * FROM employee WHERE boss=CURRENT_USER

Now, when user bilko logs into the system and queries this view, he sees the details for Doberman and Barbella. If user hall tries the same query he will see only Bilko's record. You can allow users doberman and barbella full access to the view, but when they run the query it will return zero rows.

To enforce permissions you need to revoke access to the underlying table from everyone (or simply not give any access in the first place), and allow everyone SELECT and, possibly, UPDATE and DELETE permission to the VIEW:

GRANT SELECT, UPDATE, DELETE TO PUBLIC ON minions

Now Bilko can see the salary for his employees:

mysql> SELECT * FROM minions; +--------------------+---------------+------------+--------+---------------+ | userName | name | department | salary | boss | +--------------------+---------------+------------+--------+---------------+ | barbella@localhost | Cpl. Barbella | Motor pool | 4000 |bilko@localhost| | doberman@localhost | Pvt. Doberman | Motor pool | 3000 |bilko@localhost| +--------------------+---------------+------------+--------+---------------+ 2 rows in set (0.00 sec)

He can change any of their details. Here, Bilko gives Doberman a pay raise:

mysql> UPDATE minions -> SET salary = 3100 -> WHERE name='Pvt. Doberman'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

He has permission to try the same thing on Col. Hall (or indeed himself), and no error is generated when he tries it. However, when he attempts the change, zero rows match, and Col. Hall's salary remains unchanged:

mysql> UPDATE minions -> SET salary = 3100 -> WHERE name='Col. Hall'; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0

8.4.2. Hacking the Hack

You have considerable flexibility with a schema such as this, and you will need it to make it work because there are some potential problems.

8.4.2.1. One-way trap

For instance, there is the potential for users to make mistakes that cannot be recovered. Bilko can set the boss value for one of his men, but after he has done that he cannot change it back again. If he sets the boss to an employee who does not exist or who cannot be contacted, there is no way to fix the error without the DBA intervening.

8.4.2.2. Supervisor mode

It is useful to have a "supervisor" user who is trusted and who can see and change anything. The supervisor can fix problems such as the one-way trap and set up and maintain the hierarchy. You still have the underlying employee table and you could give appropriate permissions on that, but you can do better.

You could change the minions view so that anyone in the personnel office can see all rows, for example. The code to do that would be:

CREATE VIEW minions AS SELECT * FROM employee WHERE boss=CURRENT_USER OR CURRENT_USER IN (SELECT userName FROM employee WHERE dept='Personnel')

Of course, if you do something like that you are going to have to be a little less generous with your permissions. Even if you can't see how this scheme could be circumvented you can be sure that Sgt. Bilko and Cpl. Barbella will figure out how to do it. The scam goes like this: Bilko sets Barbella's department to Personnel, Barbella can now run any trick the two of them cook up, and Barbella then sets his own department back to Motor pool and covers his tracks.