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?

Answer 1

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';