How to grant specific privileges to user in MySQL?

Question

I'd like to allow user to SELECT from specific tables and views, and EXECUTE specific procedures. I've written such a script:

CREATE USER 'user'@'%' IDENTIFIED BY 'user';
GRANT SELECT ON 
database.table1,
database.view1,
database.view2
'user'@'%' IDENTIFIED BY 'user'
WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
GRANT EXECUTE ON PROCEDURE
database.procedure1,
database.procedure2
'user'@'%' IDENTIFIED BY 'user'
WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;

But it doesn't work. What am I doing wrong?

Well, it looks like your missing a `TO` in there. Please be more specific about "doesn't work". Do you get an error message, etc.? — derobert, Nov 28 '11 at 14:14

score 3 · Answer 1 · answered Nov 28 '11 at 14:19

Try

Grant Select On database.table1 To 'user'@'%'

(I don't beleive you need the IDENTIFIED BY portion after you create the user). Grant Doc.

This should be the correct syntax. You can add multiple tables/views in one statement once you verify this works for you.

How to grant specific privileges to user in MySQL?

1 Answers1