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.

CREATE USER [IF NOT EXISTS] account_name IDENTIFIED BY 'password';  

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.

username@hostname  

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:

username@%  

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.

Enter Password: ********  

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

mysql> select user from mysql.user;  We will get the output as below:

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

mysql> create user peter@localhost identified by 'jtp12345';  

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.

mysql> CREATE USER IF NOT EXISTS adam@localhost IDENTIFIED BY 'jtp123456';  

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.

mysql> GRANT ALL PRIVILEGES ON * . * TO peter@localhost;  

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

mysql> GRANT CREATE, SELECT, INSERT ON * . * TO peter@localhost;  

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

FLUSH PRIVILEGES;  

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

mysql> SHOW GRANTS for username;