How to create a MySQL user and grant privileges

/ #Mysql


Creating and granting a user permissions is something you might not do too often using MySQL, so it's easy to forget the syntax. Here is a short guide on how you first create the user and how you grant it the permissions it need.

Create the user

mysql> CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

This will create a user with username as it's username and the password is password. "localhost" means that you will only be able to connect to MySQL when you are logged on to the server where MySQL is running.

If you change "localhost" to "%", you will be able to log on to the server from anywhere and if you just want a certain IP address to get access to can write that there instead.

Grant the permissions

mysql> GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost';

When the user is created we need to grant it privileges. "*.*" means that we grant the privileges on all databases and on all tables.

If you just want to user to access one single database, you can use this syntax instead.

GRANT ALL PRIVILEGES ON databasename.* TO 'username'@'localhost';

Flush the privileges

mysql> FLUSH PRIVILEGES;

When you have done changes to the permissions like adding a user or just changing what access he or she has, you need to flush the MySQL cache. The only command you need to run is FLUSH PRIVILEGES and the cache will be flushed.


Comments

No comments yet...

Add comment

Newsletter

Subscribe to my weekly newsletter. One time per week I will send you a short summary of the tutorials I have posted in the past week.