How to grant permissions to users based on table information?

Question

Say I have a table:

+------------------------+
|       Employees        |
+------+--------+--------+
| Dept |  Name  | Salary |
+------+--------+--------+
|    1 | John   |  10000 |
|    1 | Mary   |  50000 |
|    2 | Alfred |  30000 |
+------+--------+--------+

Now I create two views:

create view dept1_memberview as select Dept, Name from Employees where Dept=1;
create view dept2_memberview as select Dept, Name from Employees where Dept=2;

What I want to do is give access to each employee in the table (their usernames match their entry under the 'Name' column) to the view that corresponds to the department they're in. I know I could just do:

grant select on db.dept1_memberview to John, Mary;
grant select on db.dept2_memberview to Alfred;

But mostly out of curious, and for larger databases with hundreds of employees, I would like to know how I'd do that.

Thanks in advance!

Answer 1

Something like that could be achieved through dynamic sql, or via using stored procedures instead of views (for selecting) or triggers (for controlling inserts/updates).alter

But you can also create the memberview to return values according to the currently logged on user, which is probably more practical:

create view memberview as 
  select Dept, Name 
  from Employees
  where Dept= (
    select Dept
    from Employees
    where Name = coalesce(nullif(mid(current_user, 1, locate('@', current_user)-1), ''), current_user)
    );

Answer 2

You are really creating your own authentication methods.

Plan A: Use the PAM stuff in newer versions of MySQL.

Plan B: Write a database layer where the user, via some UI, asks for something. Then the layer checks your tables to see whether he is allowed to see/change/etc the thing in question. Then it either denies access, or proceeds to generate and perform the queries.