CODEWITHSUNDEEP
mysqldatabase-permissions
jarsever
jarsever

Reputation: 780

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.

Upvotes: 1

Views: 2086

Answers (2)

user4035
user4035

Reputation: 23729

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.

Upvotes: 0

fancyPants
fancyPants

Reputation: 51868

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

Upvotes: 1

Related Questions