Search code examples
mysqldatabase-permissions

MySQL Grant permission based on column value?

I am trying to grant access permissions to a certain user who has 'manager' access. They are able to SELECT and UPDATE but only to the people in their own group. The DB is redflame and the table is payroll.

A portion of the table is this:

+------+---------+--------+-----------+--------+
| Dept | Manager | Name   | Birthdate | Salary |
+------+---------+--------+-----------+--------+
|    1 | Y       | BOB    | 1/1/1     |  50000 |
|    1 | N       | BILL   | 2/2/2     |  40000 |
|    1 | N       | BART   | 3/3/3     |  70000 |
|    2 | Y       | JIM    | 4/4/4     |  40000 |
|    2 | N       | JANET  | 5/5/5     |  50000 |
...

I am wanting to only allow SELECT and UPDATE privileges to the manager but only to his group. I tried,

GRANT SELECT (Dept, Manager, Name, Birthdate),
      UPDATE (Dept, Manager, Name, Birthdate)
   ON redflame.payroll WHERE Dept = '1'
   TO 'Bob'@'localhost';

I know that this won't work but how do you implement Bob's permission based on his Dept?

Any help would be greatly appreciated.

Solution

  • Create a View for every department and grant privileges on those.