mysql GRANT + WHERE

Question

I want to give permissions only to specificated rows in mysql. table: messages cols: from, to, message

GRANT ALL ON db.messages TO 'jeffrey'@'localhost' WHERE messages.from = 'jeffrey' OR messages.to = 'jeffrey' ;

With a thing like this the user only can access only his own messages.

Do you know how to solve the problem?

score 4 · Accepted Answer · answered Jul 27 '12 at 18:39

4

Per the GRANT command, there is no ability to set permission-levels on a per-row basis (table/columns, yes - but not the individual rows).

You could setup a View to handle this though and grant the user permission to access the view instead.

A view such as the following should give you the messages based on the current user:

CREATE VIEW user_messages AS
    SELECT *
    FROM messages
    WHERE
        messages.from = user() OR messages.to = user();

And the grant-statement should be similar:

GRANT ALL ON db.user_messages TO 'jeffrey'@'localhost';

answered Jul 27 '12 at 18:39

newfurniturey

37,556
9
94
102

It's worked correctly, i have only added `SUBSTRING_INDEX(USER(),'@',1)` so i have `root` only, not `root@localhost` – androbin Jul 27 '12 at 19:19
Awesome! I've never done it before, but I've always wanted to set something up like this myself =P. I'm glad it worked out for you =] – newfurniturey Jul 27 '12 at 19:30
Thank you very much! I've connected it with jquery and php and everything is fine. :) – androbin Jul 27 '12 at 20:43

score 1 · Answer 2 · answered Jul 27 '12 at 18:38

1

MySQL doesn't have row-level permissions. You have database, table, and column. not rows. For row-level, use a view and/or approriate where clauses.

answered Jul 27 '12 at 18:38

Marc B

356,200
43
426
500

Tony Hopkinson · Answer 3 · 2012-07-27T19:04:25.947

If you want row level permissions, you'll have to add your own mechanism. Usually it's an integer, used as a bit Pattern

E.g. 0x0002 is a Supervisor Level access

The user Fred is a supervisor so their Permission is 0x0002 and they have a UserId of 23

Then something like

Select * From SomeTable
inner Join myUsers On MyUsers.UserID = 23 and (SomeTable.PermissionID & MyUsers.PermissionID) > 0

So Fred can only access rows with a permissionId where bit 1 (Big endian !) is set.

Have a deep think though it's major PIA to maintain.

mysql GRANT + WHERE

3 Answers3

Linked

Related