MySQL Grant permission based on column value?

Question

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.

score 1 · Accepted Answer · answered Apr 08 '13 at 12:01

1

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

answered Apr 08 '13 at 12:01

fancyPants

50,732
33
89
96

Thank you for the suggestion. I got this code to work: `CREATE VIEW Dept1 as SELECT * FROM redflame.payroll WHERE Dept = '1';` and then was able to implement the normal `GRANT` privileges. I also found a suggestion in another article [HERE](http://stackoverflow.com/questions/1840494/does-deleting-row-from-view-delete-row-from-base-table-mysql) where it states `Some views are updatable. That is, you can use them in statements such as UPDATE, DELETE, or INSERT to update the contents of the underlying table.` – jarsever Apr 08 '13 at 12:35

score 0 · Answer 2 · answered Apr 08 '13 at 12:20

According to MySQL manual:

http://dev.mysql.com/doc/refman/5.1/en/grant.html

Normally, a database administrator first uses CREATE USER to create an account, then GRANT to define its privileges and characteristics.

In you php code when you are adding a new record to payroll table, you must do the following (I am writing in pseudo-code):

if((new->Dept == 1) and (new->Manager == 'Y'))
{
      if (user with name == new->Name doesn't exist)
      {
            Create a new user with name = new->Name
            Grant SELECT and UPDATE privileges to the newly created user ON redflame.payroll table
      }
      else
      {
            error: user already exists!
      }
}

When you are updating a record in payroll table:

if(the record was a Manager and he is updated to non Manager)
{
      DROP USER with name = old->Name
}

if(the record was not a Manager and he is updated to Manager)
{
      if (user with name == new->Name doesn't exist)
      {
            Create a new user with name = new->Name
            Grant SELECT and UPDATE privileges to the newly created user ON redflame.payroll table
      }
      else
      {
            error: user already exists!
      }
}

When you are deleting a record from payroll table:

if((old->Dept == 1) and (old->Manager == 'Y'))
{
      if (user with name == old->Name exists)
      {
            DROP USER with name = old->Name
      }
}

If you have root access to the database, you can create ON INSERT, ON UPDATE and ON DELETE triggers. Then use this logics, writing SQL code. Otherwise, implement it in php.

MySQL Grant permission based on column value?

2 Answers2