How to create a user and grant permissions using MySQL

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.

Share this post

Comments

Espen T

04. may 2018 13:18

Would like to see more guides like this! Keep posting.

Add comment

Sign in to add a comment