How to create a user and grant permissions using MySQL
Published 03. may 2018 13:30 by Stein Ove Helset
Creating and granting a user permissions is a task 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.
Flush the privileges
mysql> FLUSH PRIVILEGES;
Whenever you do changes to the permissions you have to flush the cache. The only command you need to run is FLUSH PRIVILEGES and the cache will be flushed.