MySQL Create User

The MySQL user is a record in the USER table of the MySQL server that contains the login information, account privileges, and the host information for MySQL account. It is essential to create a user in MySQL for accessing and managing the databases.

The MySQL Create User statement allows us to create a new user account in the database server. It provides authentication, SSL/TLS, resource-limit, role, and password management properties for the new accounts. It also enables us to control the accounts that should be initially locked or unlocked.

If you want to use the Create User, it is required to have a global privilege of Create User statement or the INSERT privilege for the MySQL system schema. When you create a user that already exists, it gives an error. But if you use, IF NOT EXISTS clause, the statement gives a warning for each named user that already exists instead of an error message.

Why Did Users require in MySQL server?

When the MySQL server installation completes, it has a ROOT user account only to access and manage the databases. But, sometimes, you want to give the database access to others without granting them full control. In that case, you will create a non-root user and grant them specific privileges to access and modify the database.

Syntax

The following syntax is used to create a user in the database server.

In the above syntax, the account_name has two parts one is the username, and another is the hostname, which is separated by @ symbol. Here, the username is the name of the user, and the hostname is the name of the host from which the user can connect with the database server.

The hostname is optional. If you have not given the hostname, the user can connect from any host on the server. The user account name without hostname can be written as:

Note: The Create User creates a new user with full access. So, if you want to give privileges to the user, it is required to use the GRANT statement.

MySQL CREATE USER Example

The following are the step required to create a new user in the MySQL server database.

Step 1: Open the MySQL server by using the mysql client tool.

Step 2: Enter the password for the account and press Enter.

Step 3: Execute the following command to show all users in the current MySQL server.

We will get the output as below:

Step 4: Create a new user with the following command.

Now, run the command to show all users again.

In the above output, we can see that the user peter has been created successfully.

Step 5: Now, we will use the IF NOT EXISTS clause with the CREATE USER statement.

Grant Privileges to the MySQL New User

MySQL server provides multiple types of privileges to a new user account. Some of the most commonly used privileges are given below:

1. ALL PRIVILEGES: It permits all privileges to a new user account.
2. CREATE: It enables the user account to create databases and tables.
3. DROP: It enables the user account to drop databases and tables.
4. DELETE: It enables the user account to delete rows from a specific table.
5. INSERT: It enables the user account to insert rows into a specific table.
6. SELECT: It enables the user account to read a database.
7. UPDATE: It enables the user account to update table rows.

If you want to give all privileges to a newly created user, execute the following command.

If you want to give specific privileges to a newly created user, execute the following command.

Sometimes, you want to flush all the privileges of a user account for changes occurs immediately, type the following command.

If you want to see the existing privileges for the user, execute the following command.