MySQL CREATE USER Statement with Examples


How to Create User Accounts Using MySQL CREATE USER Statement

In this tutorial, we'll learn how to use the CREATE USER statement to create user accounts in MySQL.

We can create user accounts that can connect to the MySQL database server from localhost, the specified host or any host. For example, we can create a new account, named user_a, and then allow this user account to connect from the somedomain.com domain.

CREATE USER Syntax

The syntax of the CREATE USER statement is as follows:

CREATE USER 'user_account'

IDENTIFIED BY 'password';

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

Examples

Create a User Account that Can Connect from localhost

The following statement creates a new user account, named admin with the password pass:

CREATE USER 'admin'@'localhost'

IDENTIFIED BY 'pass';

When the user account admin first created, this user account has no privileges and a default role of NONE. As we can see, we specified localhost in the above statement, so this means that this user account admin can connect to the MySQL database server from the localhost only.

However, if we omit the hostname part of the user account, MySQL will allow the 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';

Create a User Account that Can Connect from any Host

To allow a new user account that we create, to connect from any host, we can write the MySQL statement as follows:

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.

Create a User Account that Can Connect from any Sub Domain

Let's say we have a domain mysqltutorial.com, and we want to create a new user account james that can connect to the MySQL database server from any sub domain of this domain, then we can write the MySQL statement as follows:

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

IDENTIFIED BY 'pass';

When the user james created, james can connect from any sub domain of the mysqltutorial.com domain, for example, main.mysqltutorial.com.

In this tutorial, we've learned how to use the CREATE USER statement to create user accounts in MySQL. We can create user accounts that can connect to localhost, the specified host or any host. An user account, when first created, has no privileges and a default role of NONE.

Leave a Reply

Your email address will not be published. Required fields are marked *