MySQL: Create user with rights only for specific db

Question

I would need to create MySQL user programatically with the privileges only for specific db. Say there are databases a,b,c and I would need to create user who has rights only for B. I am sure its possible but my googling was not successful. Thank you!

score 23 · Accepted Answer · answered Nov 02 '09 at 07:28

23

grant all
    on B.*
to
    'user'@localhost
identified by
    'password'

The user 'user' with password 'password' now have access to all tables in the database 'B'. (Of course you need to run this query with a high priv user)

answered Nov 02 '09 at 07:28

Björn

29,019
9
65
81

You could also change "ALL" to a more reasonable set of privileges if it is going to be used by an application. Most web apps do not need to create,alter,drop tables. – MarkR Nov 02 '09 at 07:37
4

`grant all on B.* to 'user'@localhost identified by 'password';` to copy and paste faster ;) – Ricain Nov 04 '14 at 13:10

score 22 · Answer 2 · answered Nov 02 '09 at 07:30

22

you can try this

 CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'some_pass';
 grant all privileges on B.* to 'myuser'@'localhost' identified by 'some_pass';

answered Nov 02 '09 at 07:30

RRUZ

134,889
20
356
483

6

Just by granting privileges to an user if it doesn’t exist it is created. – MTVS Nov 12 '12 at 08:43

MySQL: Create user with rights only for specific db

2 Answers2