MySQL: Create User

Profile picture for user arilio666

MySQL determines which database a user can connect to and contains two vital pieces of information the username and the host's name to which it is trying to connect.

  • So the format for that can be put like this username@hostname.
  • Let it be the admin user who is trying to connect here. They all go through the same format.
  • These user accounts are stored up in the user grant table in the MySQL database.
  • To connect to a database using a user credential based upon the privileges granted to them, CREATE USER is used in MySQL.


CREATE USER username IDENTIFIED BY password;

Creating A New User

To create a new user, we need to use the CREATE USER statement in MySQL followed by the username of your choice and the local instance to connect to your database. Then followed by after that, will be your password.

CREATE USER 'naruto'@'LocalInstance' IDENTIFIED BY 'abcd';

This only created a new user and does not grant any permissions to it.

Creating More Than One User

Creating more than one user can also be done with the help of the same query which we used for making the single user. It goes like this.

'sai'@'LocalInstance' IDENTIFIED BY 'vgma';
'sasuke'@'LocalInstance' IDENTIFIED BY 'dcba';

User Connection From Any Host Account

% wildcard is used to create a user who can connect via any host in the MySQL database.

'sasuke'@'%' IDENTIFIED BY 'dcba';

In the place of the hostname, add the % wildcard to allow users to connect through any hosts.

Using Grants To View Permission

This one is used to view the permissions granted to the user.

SHOW GRANTS FOR naruto@LocalInstance;