Creating MySQL users and granting permissions

It is a good idea to have separate users for each database, and to limit the hostname.

 

The default syntax:

CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';

e.g.

CREATE USER 'test'@'localhost' IDENTIFIED BY 'test';

The above will create user test, allow it to connect from localhost (connecting to a DB that is on the same machine, and has password test).

 

Now let’s grant all privileges on some database to the above user:

GRANT ALL PRIVILEGES ON database.* TO 'username'@'hostname' WITH GRANT OPTION;

e.g.

GRANT ALL PRIVILEGES ON test.* TO 'test'@'localhost' WITH GRANT OPTION;

The above will grant all privileges on database test to user test when he connects from localhost. You can fine grain the permissions depending on username/hostname.

 

To check the permissions for some username/hostname:

SHOW GRANTS FOR 'username'@'hostname';

 

I recommend the official guide, if you need better understanding of the above.

Tagged , , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.