MySQL CREATE USER – Add Users in MySQL

How to create a user account using the CREATE USER statement in MySQL with examples

This tutorial explains how to create a user account in MySQL using the CREATE USER statement.

It is possible to create a user account that can connect to the MySQL database server from localhost, a specified host, or any host. For example, you can create a new user account named user_a and allow this user account to connect from a domain named somedomain.com.

Related tutorial: How to delete a user account in MySQL

CREATE USER Syntax

The syntax of the MySQL CREATE USER statement is as follows:

CREATE USER 'user_account'

IDENTIFIED BY 'password';

Note that this statement will create a new user account with no privileges and a default role of NONE.

Examples

Creating a user account that can connect from localhost

The following statement creates a new user account named admin and sets the password pass:

CREATE USER 'admin'@'localhost'

IDENTIFIED BY 'pass';

When the user account admin is first created, this user account has no privileges, and the default role is NONE. In the above SQL statement, localhost is specified, which means that this user account admin can only connect to the MySQL database server from localhost.

If you omit the hostname part of the user account, MySQL will allow that user account to connect from any host. For example, the following MySQL statement creates a new user account named remote_user that can connect from any host:

CREATE USER 'remote_user'

IDENTIFIED BY 'pass';

Creating a user account that can connect from any host

To make sure that the new user account you created can connect from any host, write the MySQL statement like this:

CREATE USER 'superadmin'@'%'

IDENTIFIED BY 'pass';

This statement creates a new user account named "superadmin" that can connect to the MySQL database server from any host.

Creating a user account that can connect from any subdomain

For example, suppose you have the domain mysqltutorial.com, and you want to create a new user account named "james" that can connect to the MySQL database server from any subdomain of this domain. You can write a MySQL statement as follows:

CREATE USER 'james'@'%.mysqltutorial.com'

IDENTIFIED BY 'pass';

Once the user james has been created, "james" can connect from any subdomain of mysqltutorial.com.

Summary

In this tutorial, you have learned how to create a user account in MySQL using the CREATE USER statement. You can create a user account that can connect to localhost, a specified host, or any host. User accounts have no privileges when they are first created, and the default role is NONE. If you want to drop a user account from MySQL, use MySQL DROP USER Statement.


See also:
MySQL LIKE Operator Pattern Matching and Examples
MySQL SUBSTRING_INDEX Function with Examples
MySQL EXISTS Operator with Examples
MySQL ROW_NUMBER Function with Examples
MySQL CONCAT() Function | Concatenate Strings in MySQL

Leave a Comment